On Mar 2, 9:16 am, Erland Sommarskog <esq...@sommarskog.se> wrote:
> Vic (vikra...@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, 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

I finally got it working. Thanks a million. Btw as you mentioned 'I
strongly recommend that you set your newsreader to show posts with a
fixed font'. I just read my group mails by loggin onto google groups.
I'll take a look to change the settings. Thanks