Problem with Setting a variable in SQL String

[Aamer Nazir]

Hi,

I am having problems setting the value of a variable in a SQL String
that I have to create dynamically in my procedure. The code that I
currently have is as follows:


set @sqlStatement=' Set @compare_string =' + '(Select ' +
@group_column_l ist_mod + ' from ' + @Tbl_Name + '_Sorted' + ' where
Identity_Column =' + ltrim(rtrim(str (@loop_counter) )) + ')'

exec(@sqlStatem ent)

The error message that I get is as follows:

Must declare the variable '@compare_strin g'.

Here @compare_string has already been declared in the procedure and I
don't have a problem using the variable anywhere else but this SQL
Statement (when called using the EXEC function).

I am not sure why SQL Server can't see the variable declared when used
in a string in conjunction with EXEC. Is this a syntax issue? Any help
on this issue would be greatly appreciated!

Thanks in advance.

[Robin Tucker]

Re: Problem with Setting a variable in SQL String

You need a parms string and an exec string, like this:

SET @Parms = `@compare_strin g`

set @sqlStatement=' Set @compare_string =' + '(Select ' +
@group_column_l ist_mod + ' from ' + @Tbl_Name + '_Sorted' + ' where
Identity_Column =' + ltrim(rtrim(str (@loop_counter) )) + ')'

EXECUTE sp_executesql @sqlStatement, @Parms, @compare_string

SET @Error = COALESCE ( NULLIF ( @Error, 0 ), @@ERROR )
[color=blue]
>
> exec(@sqlStatem ent)
>
> The error message that I get is as follows:
>
> Must declare the variable '@compare_strin g'.
>
> Here @compare_string has already been declared in the procedure and I
> don't have a problem using the variable anywhere else but this SQL
> Statement (when called using the EXEC function).
>
> I am not sure why SQL Server can't see the variable declared when used
> in a string in conjunction with EXEC. Is this a syntax issue? Any help
> on this issue would be greatly appreciated!
>
> Thanks in advance.[/color]

[Aamer Nazir]

Re: Problem with Setting a variable in SQL String

Thanks for your reply. The sp_executesql procedure still doesn't give
the desired results. I am posting the updated piece of code and sample
output from the Query Analyzer.


------------------
set @parameter_Stri ng=N'@compare_s tring nvarchar(4000)'

set @sqlStatement=' Set @compare_string =(Select ' +
@group_column_l ist_mod + ' from ' + @Tbl_Name + '_Sorted' + ' where
Identity_Column =' + ltrim(rtrim(str (@loop_counter) )) + ')'

Print @sqlStatement
EXECUTE sp_executesql @sqlStatement,@ parameter_Strin g,@compare_stri ng

Print @compare_String
------------------

When I print the value of @compare_String in the end its a NULL.
However, if I run the same query without the set @compare_string
clause, it does work perfectly and returns the values of two columns
concatenated together. Any clues as to where I might be going wrong?

Thanks,


"Robin Tucker" <idontwanttobes pammedanymore@r eallyidont.com> wrote in message news:<bs9mbk$jt 0$1$8300dec7@ne ws.demon.co.uk> ...[color=blue]
> You need a parms string and an exec string, like this:
>
> SET @Parms = `@compare_strin g`
>
> set @sqlStatement=' Set @compare_string =' + '(Select ' +
> @group_column_l ist_mod + ' from ' + @Tbl_Name + '_Sorted' + ' where
> Identity_Column =' + ltrim(rtrim(str (@loop_counter) )) + ')'
>
> EXECUTE sp_executesql @sqlStatement, @Parms, @compare_string
>
> SET @Error = COALESCE ( NULLIF ( @Error, 0 ), @@ERROR )
>[color=green]
> >
> > exec(@sqlStatem ent)
> >
> > The error message that I get is as follows:
> >
> > Must declare the variable '@compare_strin g'.
> >
> > Here @compare_string has already been declared in the procedure and I
> > don't have a problem using the variable anywhere else but this SQL
> > Statement (when called using the EXEC function).
> >
> > I am not sure why SQL Server can't see the variable declared when used
> > in a string in conjunction with EXEC. Is this a syntax issue? Any help
> > on this issue would be greatly appreciated!
> >
> > Thanks in advance.[/color][/color]

[Erland Sommarskog]

Re: Problem with Setting a variable in SQL String

[posted and mailed, please reply in news]

Aamer Nazir (aamernazir_01@ hotmail.com) writes:[color=blue]
> I am having problems setting the value of a variable in a SQL String
> that I have to create dynamically in my procedure. The code that I
> currently have is as follows:
>
>
> set @sqlStatement=' Set @compare_string =' + '(Select ' +
> @group_column_l ist_mod + ' from ' + @Tbl_Name + '_Sorted' + ' where
> Identity_Column =' + ltrim(rtrim(str (@loop_counter) )) + ')'
>
> exec(@sqlStatem ent)
>
> The error message that I get is as follows:
>
> Must declare the variable '@compare_strin g'.
>
> Here @compare_string has already been declared in the procedure and I
> don't have a problem using the variable anywhere else but this SQL
> Statement (when called using the EXEC function).[/color]

The EXEC() statement is another scope which is not part of your procedure.
Thus, @compare_string is not defined in that example.

For better examples than the one posted, see
http://support.microsoft.com/?id=262499 and
http://www.sommarskog.se/dynamic_sql.html#sp_executesql.


--
Erland Sommarskog, SQL Server MVP, sommar@algonet. se

Books Online for SQL Server SP3 at

http://www.microsoft.com/sql/techinfo/productdoc/2000/books.asp

[Robin Tucker]

Re: Problem with Setting a variable in SQL String

Yes, that won't work. Sorry, I just focused on the parameter part.

You can just do this:

select @compare_string = mytable.myfield FROM mytable where Identity_Column
= myvalue

or, in your specific case:

'Select @compare_string =' + @group_column_l ist_mod + ' from ' + @Tbl_Name +
'_Sorted' + ' where Identity_Column =' + ltrim(rtrim(str @loop_counter)) '

At least this is the syntax you should use in this case. Otherwise, you are
effectively trying to bind @compare_string to a recordset result, which
doesn't work.

Make sure you add in the error checking afterwards!! :)

"Aamer Nazir" <aamernazir_01@ hotmail.com> wrote in message
news:60b6d0a1.0 312231058.14540 a2c@posting.goo gle.com...[color=blue]
> Thanks for your reply. The sp_executesql procedure still doesn't give
> the desired results. I am posting the updated piece of code and sample
> output from the Query Analyzer.
>
>
> ------------------
> set @parameter_Stri ng=N'@compare_s tring nvarchar(4000)'
>
> set @sqlStatement=' Set @compare_string =(Select ' +
> @group_column_l ist_mod + ' from ' + @Tbl_Name + '_Sorted' + ' where
> Identity_Column =' + ltrim(rtrim(str (@loop_counter) )) + ')'
>
> Print @sqlStatement
> EXECUTE sp_executesql @sqlStatement,@ parameter_Strin g,@compare_stri ng
>
> Print @compare_String
> ------------------
>
> When I print the value of @compare_String in the end its a NULL.
> However, if I run the same query without the set @compare_string
> clause, it does work perfectly and returns the values of two columns
> concatenated together. Any clues as to where I might be going wrong?
>
> Thanks,
>
>
> "Robin Tucker" <idontwanttobes pammedanymore@r eallyidont.com> wrote in[/color]
message news:<bs9mbk$jt 0$1$8300dec7@ne ws.demon.co.uk> ...[color=blue][color=green]
> > You need a parms string and an exec string, like this:
> >
> > SET @Parms = `@compare_strin g`
> >
> > set @sqlStatement=' Set @compare_string =' + '(Select ' +
> > @group_column_l ist_mod + ' from ' + @Tbl_Name + '_Sorted' + ' where
> > Identity_Column =' + ltrim(rtrim(str (@loop_counter) )) + ')'
> >
> > EXECUTE sp_executesql @sqlStatement, @Parms, @compare_string
> >
> > SET @Error = COALESCE ( NULLIF ( @Error, 0 ), @@ERROR )
> >[color=darkred]
> > >
> > > exec(@sqlStatem ent)
> > >
> > > The error message that I get is as follows:
> > >
> > > Must declare the variable '@compare_strin g'.
> > >
> > > Here @compare_string has already been declared in the procedure and I
> > > don't have a problem using the variable anywhere else but this SQL
> > > Statement (when called using the EXEC function).
> > >
> > > I am not sure why SQL Server can't see the variable declared when used
> > > in a string in conjunction with EXEC. Is this a syntax issue? Any help
> > > on this issue would be greatly appreciated!
> > >
> > > Thanks in advance.[/color][/color][/color]

[Aamer Nazir]

Re: Problem with Setting a variable in SQL String

Thanks for pointing me to the right direction. The code works
perfectly fine now. The problem was with the syntax that Erland
Sommarskog mentioned in his posting. You have to specify the parameter
type (input or output) in the parameter specification string (the
second argument to sp_executesql).

Best Regards,


"Robin Tucker" <idontwanttobes pammedanymore@r eallyidont.com> wrote in message news:<bsboku$o7 p$1$8300dec7@ne ws.demon.co.uk> ...[color=blue]
> Yes, that won't work. Sorry, I just focused on the parameter part.
>
> You can just do this:
>
> select @compare_string = mytable.myfield FROM mytable where Identity_Column
> = myvalue
>
> or, in your specific case:
>
> 'Select @compare_string =' + @group_column_l ist_mod + ' from ' + @Tbl_Name +
> '_Sorted' + ' where Identity_Column =' + ltrim(rtrim(str @loop_counter)) '
>
> At least this is the syntax you should use in this case. Otherwise, you are
> effectively trying to bind @compare_string to a recordset result, which
> doesn't work.
>
> Make sure you add in the error checking afterwards!! :)
>
> "Aamer Nazir" <aamernazir_01@ hotmail.com> wrote in message
> news:60b6d0a1.0 312231058.14540 a2c@posting.goo gle.com...[color=green]
> > Thanks for your reply. The sp_executesql procedure still doesn't give
> > the desired results. I am posting the updated piece of code and sample
> > output from the Query Analyzer.
> >
> >
> > ------------------
> > set @parameter_Stri ng=N'@compare_s tring nvarchar(4000)'
> >
> > set @sqlStatement=' Set @compare_string =(Select ' +
> > @group_column_l ist_mod + ' from ' + @Tbl_Name + '_Sorted' + ' where
> > Identity_Column =' + ltrim(rtrim(str (@loop_counter) )) + ')'
> >
> > Print @sqlStatement
> > EXECUTE sp_executesql @sqlStatement,@ parameter_Strin g,@compare_stri ng
> >
> > Print @compare_String
> > ------------------
> >
> > When I print the value of @compare_String in the end its a NULL.
> > However, if I run the same query without the set @compare_string
> > clause, it does work perfectly and returns the values of two columns
> > concatenated together. Any clues as to where I might be going wrong?
> >
> > Thanks,
> >
> >
> > "Robin Tucker" <idontwanttobes pammedanymore@r eallyidont.com> wrote in[/color]
> message news:<bs9mbk$jt 0$1$8300dec7@ne ws.demon.co.uk> ...[color=green][color=darkred]
> > > You need a parms string and an exec string, like this:
> > >
> > > SET @Parms = `@compare_strin g`
> > >
> > > set @sqlStatement=' Set @compare_string =' + '(Select ' +
> > > @group_column_l ist_mod + ' from ' + @Tbl_Name + '_Sorted' + ' where
> > > Identity_Column =' + ltrim(rtrim(str (@loop_counter) )) + ')'
> > >
> > > EXECUTE sp_executesql @sqlStatement, @Parms, @compare_string
> > >
> > > SET @Error = COALESCE ( NULLIF ( @Error, 0 ), @@ERROR )
> > >
> > > >
> > > > exec(@sqlStatem ent)
> > > >
> > > > The error message that I get is as follows:
> > > >
> > > > Must declare the variable '@compare_strin g'.
> > > >
> > > > Here @compare_string has already been declared in the procedure and I
> > > > don't have a problem using the variable anywhere else but this SQL
> > > > Statement (when called using the EXEC function).
> > > >
> > > > I am not sure why SQL Server can't see the variable declared when used
> > > > in a string in conjunction with EXEC. Is this a syntax issue? Any help
> > > > on this issue would be greatly appreciated!
> > > >
> > > > Thanks in advance.[/color][/color][/color]