Problem with Dynamic SQL in Stores Procedures.

[JohnPham]

Hi All,

Would any one help me with this? Here is the portion of my stores procedure.

declare @something varchar(2000)
declare @lastname varchar(10)
declare @myvalue varchar(10)
declare @allvalue varchar(40)

set @myvalue = '''john'''
set @allvalue = '''Basketball'''
Set @something = 'SELECT ' + @lastname + ' = lname
FROM tableA WHERE fname = ' + @myvalue + ' AND activity in (' + @allvalue + ')'

exec(@something)

When I run the exec(@something) it does not work. But when I take the variable @lastname out of
statement, it comes back with some value.
Like this and it works.
Set @something = 'SELECT lname
FROM tableA WHERE fname = ' + @myvalue + ' AND activity in (' + @allvalue + ')'
exec(@something)


The point is , I need this variable @lastname value to set with lname so I can track
later in my stored procedures.


I am not sure that I did the right way as the first statement. Please help.

John

 

[JamesLean]

This should work for your example:

DECLARE @lastname varchar(10),
  @myvalue varchar(10),
  @allvalue varchar(40)

SELECT @myvalue = 'john', @allvalue = 'basketball'

SELECT @lastname = lname
FROM tablea
WHERE fname = @myvalue AND activity = @allvalue

--James

 

[JohnPham]

Thanks James.

This should work with variable @allvalue is just have one value.
What if @allvalue have more than one value and dynamic pass from somewhere. That why I have to use 'IN' in the statment.
For example:
@allvalue = 'Basketball','FootBall','Tennis'

Thank you.

John

 

[SQLBill]

According to the Books OnLine, "...Transact-SQL variables can hold a single data value..." (Bolding is for emphasis). Refer to BOL, use Index tab and enter Variables, Transact-SQL.

-SQLBill

 

[JamesLean]

OK, try this:

DECLARE @lastname varchar(10),
  @myvalue varchar(10),
  @allvalue varchar(40),
  @sql nvarchar(200)

SELECT @myvalue = 'john', @allvalue = '''basketball'',''football'''

SET @sql = 'SELECT @lastname = lname FROM tablea WHERE fname = ''' + @myvalue + ''' AND activity IN (' + @allvalue + ')'

EXEC sp_executesql @sql, N'@lastname varchar(10) OUTPUT', @lastname OUTPUT

--James

 

[JohnPham]

Thanks SQLBill.

That the reason why I try it does not comes back as the result I need.
Thank you for your info.

John

 

[topher0303]

Try changing your dynamic SQL to:

Set @something = 'SELECT @lastname = lname
FROM tableA WHERE fname = ' + @myvalue + ' AND activity in (' + @allvalue + ')'

Your version is inserting the value of @lastname into the string. What you are wanting is to place the actual variable in the string.

Chris.

 

[JohnPham]

Thanks James.

It works. Thank you very much for this. It helps me a lot. I have tried this whole day yesterday but could not come to any clue for it to work.
Big thanks to James.

John

 

[JamesLean]

Chris,
Your SQl string is correct but you can't simply execute this and get the variable back. Dynamic SQL executes in its own scope and therefore the var is not accessible to the calling procedure.

Using sp_executesql as per my example returns the var to the proc. --James

 

[JohnPham]

Hi Chris,

I did try that as well but it shows the error message as
'@lastname must be declared' even though I have declared it.

John

 

[sunila7]

Hi John,

Try this..... I have put a Top 1 b'cos @Lastname can hold only one value

DECLARE @lastname varchar(10),
@myvalue varchar(10),
@allvalue varchar(40),
@sql nvarchar(200)

SELECT @myvalue = 'john', @allvalue = '''basketball'',''football'''

SET @sql = 'SELECT TOP 1 lname FROM tablea WHERE fname = ''' + @myvalue + ''' AND activity IN (' + @allvalue + ')'

EXEC sp_executesql @sql, N'@lastname varchar(10) OUTPUT', @lastname OUTPUT


Sunil