On Mar 1, 3:39 pm, Erland Sommarskog <esq...@sommarskog.se> wrote:
> Vic (vikra...@gmail.com) writes:
> > CREATE PROCEDURE [dbo].[GetColumnName]
> > -- Add the parameters for the stored procedure here
> > @sTableName varchar(50),@sObjectClass varchar(50),@sFieldNumber
> > varchar(50), @sColumnName varchar(50) OUTPUT
> > AS
> > BEGIN
> > SET NOCOUNT ON;
> > set @sColumnName = ('select column_name FROM '+ @sTableName +
> > '.Information_Schema.columns '+'where Table_Name = '+''''+@sObjectClass
> > +''''+ ' and Column_Name like '+''''+@sFieldNumber+'-%''')
> > END
>
> > I call the Stored Procedure as
> > Declare @sColumnName varchar(50)
> > exec dbo.GetColumnName 'TestData6061','accounting','11',@sColumnName
> > OUTPUT
> > select @sColumnName
> > go
>
> > If I run
> > SELECT column_name
> > FROM TestData6061.Information_Schema.columns
> > where Table_Name = 'accounting' and Column_Name like '11-%' which is
> > what the stored procedure is doing, I get '11-AccountSpecifictype'.
>
> Which is what the procedure is doing? Eh? The procedure assigns the variable
> string expression, which will be truncated after 50 characters, as
> that is how long you have declared the parameter. That string starts
> with 'select column_name FROM ', and there is no way it can become
> '11-AccountSpecifictype'.
>
> It appears that you have invented your own syntax, but that is not going
> to take you anywhere.
>
> Maybe this is what you mean:
>
> DECLARE @sql nvarchar(MAX),
> @params nvarchar(MAX)
> SELECT @sql = select @columnname = COLUMN_NAME FROM '+ @db +
> '.INFORMATION_SCHEMA.COLUMS ' +
> 'WHERE TABLE_NAME = @objectclass ' +
> ' AND COLUMN_NAME LIKE @fieldnumber',
> @params = ' @columnname sysname, @objectclass sysname, ' +
> ' @fieldnumber sysname'
> EXEC sp_executesql @sql, @params, @columnname OUTPUT,
> @objectclass, @fieldnumber
>
> If you have not read my article on dynamic SQL, I recommend that you do,
> because you seem to need it. At least the parts that describe sp_executesql.http://www.sommarskog.se/dynamic_sql.html
>
> --
> Erland Sommarskog, SQL Server MVP, esq...@sommarskog.se
>
> Books Online for SQL Server 2005 athttp://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books...
> Books Online for SQL Server 2000 athttp://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

Hi Erland, thanks for the reply I was checking out your sp_executesql
related page and it was pretty helpful. So I modified my code to
something like this but its giving me some problem during execution.
Also the print @sql isn't showing values of @db. So I tried to modify
the select statement by modifying the the single quotes (') in the
line then I see the error as @db not declared or something. Also Exec
sp_executesql @sql, @params, 'TestData', 'accounting', '11' is that
how I call sp_executesql?

DECLARE @sql nvarchar(MAX),@params nvarchar(MAX),@valueofrow
varchar(50)

SELECT @sql =
N' SELECT COLUMN_NAME '+
N' FROM @db.INFORMATION_SCHEMA.COLUMNS '+
N' WHERE TABLE_NAME = @objectclass AND '+
N' COLUMN_NAME LIKE @fieldnumber-% '

print @sql

SELECT @params =
N' @db sysname, '+
N' @objectclass sysname, '+
N' @fieldnumber sysname, '+
N' @columnname varchar(50) OUTPUT '

exec sp_executesql @sql,
@params,'TestData6061','accounting','11',@columnam e = @valueofrow
OUTPUT