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

[Robin Tucker]

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.

[Aamer Nazir]

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.

[Erland Sommarskog]

[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

[Robin Tucker]

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.

[Aamer Nazir]

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.