On Mar 2, 6:03 am, Erland Sommarskog <esq...@sommarskog.se> wrote:
> Vic (vikra...@gmail.com) writes:
> > 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.
>
> You have:
>
> > N' FROM @db.INFORMATION_SCHEMA.COLUMNS '+
>
> I don't think this can ever be legal T-SQL. In any case it can never mean
> what you intended. You cannot specify the database name through a
> variable, but you have to inline it:
>
> N' FROM ' + quotename(@db) + '.INFORMATION_SCHEMA.COLUMNS '+
>
> > Also Exec sp_executesql @sql, @params, 'TestData', 'accounting', '11'
> > is that how I call sp_executesql?
>
> The first parameter is the SQL statement. The second is the parameter
> list. The remaining are the parameters as specified in your parameter
> list.
>
> > N' COLUMN_NAME LIKE @fieldnumber-% '
>
> This is not going to end happily. - is a numeric operator, but % will not
> convert to an integer. You need:
>
> > N' COLUMN_NAME LIKE @fieldnumber + ''-%'' '
>
> --
> 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

Thanks for the reply. So I modified my select line as below (as per
your instruction)
SELECT @sql =
N' SELECT COLUMN_NAME '+
N' FROM'+ quotename(@db)+'.INFORMATION_SCHEMA.COLUMNS '+
N' WHERE TABLE_NAME = @objectclass AND '+
N' COLUMN_NAME LIKE @fieldnumber+"-%" '

n now I a call sp_executesql as
exec sp_executesql @sql,
@params,@db='TestData6061',@objectclass='accountin g',@fieldnumber='11',@columname
= @valueofrow OUTPUT
Is that how am I supposed to call sp_executesql? I get an error for
'must declare the scalar variable @db' in the select line when I run