store the result of a dynamic sql stmt 2

[amrita418]

Hello everyone,
I am not sure how to compare the result of a dynamic sql stmt in t-sql. Sorry if it is a dummy question.

For example:

declare
@v_test varchar(10),
@v_table varchar(20)

set @v_table = 'emp'
-- this works
exec ('select top 1 description from ' + @v_table)

-- but what I need now is to compare the value of description with some pre defined value.

if ('X' = exec ('select top 1 description from ' + @v_table))
....

I do not see a way to do this. Any help is greatly appreciated.

Thanks,
--aa

 

[mrdenny]

This is why dynamic SQL is such a pain. You'll need to log the results of your query into a table then compair the data in that table with the known value that you are looking for.

create table ##TempTable
(Description varchar(1000)

declare 
@v_test varchar(10),
@v_table varchar(20)

set @v_table = 'emp'
-- this works 
exec ('insert into ##TempTable select top 1 description  from ' + @v_table)

-- but what I need now is to compare the value of description with some pre defined value.

if ('X' = (select Description from ##TempTable))
....

Denny

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com

(My very old site)

 

[JamesLean]

You don't have to use a temp table here.

DECLARE @tbl varchar(20),
	@sql nvarchar(100),
	@desc varchar(50)

SET @tbl = 'emp'
SET @sql = N'SELECT TOP 1 @d = description FROM ' + @tbl

EXEC sp_executesql @sql, N'@d varchar(50) OUTPUT', @desc OUTPUT

PRINT 'Description = ' + @desc

IF @desc = 'X'
...

--James