Vic (vikrantp@gmail.com) writes:
> 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+"-%" '

The last line should be

N' COLUMN_NAME LIKE @fieldnumber+''-%'' '

Double single quotes, not single double quotes. (I strongly recommend
that you set your newsreader to show posts with a fixed font.)

> 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

So why just declare it then?

But @db is not a parameter to the SQL command, only a parameter to
the surrounding procedure, so it should not appear in the call to
sp_executesql.



--
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx