469,590 Members | 2,464 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,590 developers. It's quick & easy.

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 13213
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 suresh191 | last post: by
4 posts views Thread by guiromero | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.