Getting Error while using exec statement

Hi

I am trying to use exec statement like this

Code Snippet

declare @.a varchar(10)

declare @.b varchar(10)

declare @.c varchar(20)

set @.a = 'SomeData'

select @.c = '@.b=' +'@.a'

exec ('select ' + @.c)

but i am getting this error

Must declare the variable '@.a'.

well we can achieve above problem using

Code Snippet

set @.a = 'SomeData'

select @.b=@.a

select @.b

but i am expecting like this (because i am building a formula)

exec ('select ' + @.c)

please advice

Thanks

You have a scope problem; the '@.a' variable is not known in the domain of your exec string. The scope of the code that is run as part of an "exec string" is completely separate from the scope of the code that compiled the exec string. Therefore, none of your variables -- @.a, @.b, or @.c -- have any meaning from within the execution scope of what gets done while your "exec string" is being performed. You might want to use the "sp_executesql" stored procedure in this case so that you might be able to use these variables as parameters.

Here are some previous posts on the subject:

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1087502&SiteID=1
http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=660466&SiteID=1
http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=578918&SiteID=1

You also need to understand that there are some inherent problems with dynamic SQL. This article might provide some insights:

Dynamic SQL - The Curse and Blessings of Dynamic SQL
http://www.sommarskog.se/dynamic_sql.html

|||

I've used like this

Code Snippet

set @.a = 'SomeData'

select @.c = '@.b=' +'@.a'

EXEC sp_executesql N'select @.c'

but this time i got this Error

Must declare the variable '@.c'.

please advice

Thanks

|||

You must also pass the parameter to sp_executesql (any the parameters included in the string.

Try something like this:

Code Snippet

set @.a = 'SomeData'

select @.c = '@.b=' +'@.a'

EXECUTE sp_executesql N'SELECT @.c', @.c

Of course, SELECT @.B='SomeData' isn't going to make a lot of sense and will cause an error.

But hey, you just asked for help with passing the parameter -not writing valide SQL statements...

|||

The following sample might help you to understand..

Code Snippet

Declare @.a as int

Declare @.b as int

Declare @.result as varchar(100)

Declare @.FormulaQuery as nvarchar(100)

Set @.a = 10

Set @.b = 20

select @.FormulaQuery = 'Set @.result = (@.b + @.a)'

EXECUTE sp_executesql

@.FormulaQuery --Your Computable Expression/Formula

, N'@.a int, @.b int, @.result int output' -- All the Param definitions

, @.a -- First Param value

, @.b -- Second Param value

, @.result output -- Third Param value which return the value from the expression

Select @.result