471,855 Members | 1,124 Online
Bytes | Software Development & Data Engineering Community
Post +

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 471,855 software developers and data experts.

Problem with Setting a variable in SQL String

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_list_mod + ' from ' + @Tbl_Name + '_Sorted' + ' where
Identity_Column=' + ltrim(rtrim(str(@loop_counter))) + ')'

exec(@sqlStatement)

The error message that I get is as follows:

Must declare the variable '@compare_string'.

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.
Jul 20 '05 #1
5 13293
You need a parms string and an exec string, like this:

SET @Parms = `@compare_string`

set @sqlStatement='Set @compare_string=' + '(Select ' +
@group_column_list_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(@sqlStatement)

The error message that I get is as follows:

Must declare the variable '@compare_string'.

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.

Jul 20 '05 #2
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_String=N'@compare_string nvarchar(4000)'

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

Print @sqlStatement
EXECUTE sp_executesql @sqlStatement,@parameter_String,@compare_string

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" <id*************************@reallyidont.com> wrote in message news:<bs*******************@news.demon.co.uk>...
You need a parms string and an exec string, like this:

SET @Parms = `@compare_string`

set @sqlStatement='Set @compare_string=' + '(Select ' +
@group_column_list_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(@sqlStatement)

The error message that I get is as follows:

Must declare the variable '@compare_string'.

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.

Jul 20 '05 #3
[posted and mailed, please reply in news]

Aamer Nazir (aa***********@hotmail.com) writes:
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_list_mod + ' from ' + @Tbl_Name + '_Sorted' + ' where
Identity_Column=' + ltrim(rtrim(str(@loop_counter))) + ')'

exec(@sqlStatement)

The error message that I get is as follows:

Must declare the variable '@compare_string'.

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).


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, so****@algonet.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp
Jul 20 '05 #4
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_list_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" <aa***********@hotmail.com> wrote in message
news:60**************************@posting.google.c om...
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_String=N'@compare_string nvarchar(4000)'

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

Print @sqlStatement
EXECUTE sp_executesql @sqlStatement,@parameter_String,@compare_string

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" <id*************************@reallyidont.com> wrote in

message news:<bs*******************@news.demon.co.uk>...
You need a parms string and an exec string, like this:

SET @Parms = `@compare_string`

set @sqlStatement='Set @compare_string=' + '(Select ' +
@group_column_list_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(@sqlStatement)

The error message that I get is as follows:

Must declare the variable '@compare_string'.

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.

Jul 20 '05 #5
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" <id*************************@reallyidont.com> wrote in message news:<bs*******************@news.demon.co.uk>...
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_list_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" <aa***********@hotmail.com> wrote in message
news:60**************************@posting.google.c om...
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_String=N'@compare_string nvarchar(4000)'

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

Print @sqlStatement
EXECUTE sp_executesql @sqlStatement,@parameter_String,@compare_string

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" <id*************************@reallyidont.com> wrote in

message news:<bs*******************@news.demon.co.uk>...
You need a parms string and an exec string, like this:

SET @Parms = `@compare_string`

set @sqlStatement='Set @compare_string=' + '(Select ' +
@group_column_list_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(@sqlStatement)
>
> The error message that I get is as follows:
>
> Must declare the variable '@compare_string'.
>
> 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.

Jul 20 '05 #6

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

5 posts views Thread by | last post: by
1 post views Thread by Andrew | last post: by
15 posts views Thread by Ron L | last post: by
6 posts views Thread by David Hearn | last post: by
5 posts views Thread by tshad | last post: by
reply views Thread by YellowAndGreen | last post: by
aboka
reply views Thread by aboka | last post: by

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.