T-SQL: I need to fill variable from executed dynamic query.

I need to fill a variable from executed dynamic query.

Obviously this doesn't work but something like:

Set @SQL = 'Select @Test = Process_Flag from ' + @TableName + ' where ' + @Condition
exec(@SQL) in way that returns @Test value

Can anyone be of help?
dhinckleAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

chapmandewCommented:
declare @test varchar(200)
Set @SQL = 'Select @Test = Process_Flag from ' + @TableName + ' where ' + @Condition
execute sp_executesql @SQL, '@test varchar(200) output', @test output
select @test
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Om PrakashCommented:
Create table #ProcessFlag (pfValue varchar(50))
Set @SQL = 'insert into #ProcessFlag Select Process_Flag from ' + @TableName + ' where ' + @Condition
exec(@SQL)
SELECT * FROM #ProcessFlag -- This will contian the value needed.
0
Rajkumar GsSoftware EngineerCommented:
A slight correction in chapmandew's query, which I think does matter.

Raj
DECLARE @Test VARCHAR(50), @SQL NVARCHAR(4000) 
Set @SQL = 'Select @Test = Process_Flag from ' + @TableName + ' where ' + @Condition
-- PRINT @SQL
execute sp_executesql @SQL, N'@test varchar(50) output', @test output 
PRINT @test

Open in new window

0
10 Tips to Protect Your Business from Ransomware

Did you know that ransomware is the most widespread, destructive malware in the world today? It accounts for 39% of all security breaches, with ransomware gangsters projected to make $11.5B in profits from online extortion by 2019.

chapmandewCommented:
Well, I was assuming @SQL was already declared since it was already being used.
0
Rajkumar GsSoftware EngineerCommented:
No. That's not what I mean.

I mean the character N'

Without which I got an error.

Raj
0
chapmandewCommented:
yep, good catch.
0
dhinckleAuthor Commented:
From what I can tell, nothing short of this will get the value of Process_Flag into a stored procedure:

Create Table #DyResult (Process_Flag int)
Set @SQL = 'Select Process_Flag from ' + @TableName + ' Where ' + @Where      
      Insert #DyResult Exec(@SQL)
      Set @Test = (Select Process_Flag from #DyResult)

this came from MSDN (getting creative with exec) and works for my purposes.

Thanks.

0
chapmandewCommented:
Then you're wrong...you obviosly didn't try out suggestions.  Im objecting.
0
chapmandewCommented:
Then you're wrong...you obviosly didn't try out suggestions.  Im objecting.
0
Rajkumar GsSoftware EngineerCommented:
dhinckle,

Try out our suggestions.

I double-tested the code I posted on a table. It is working.

Raj
0
Rajkumar GsSoftware EngineerCommented:
dhinckle,

You can use this test script to validate our solution.

Raj
-- CREATE A TEST TABLE
CREATE TABLE #tblTesting
(
	TestID	INT,
	TestName	VARCHAR(20)
)

-- DUMP SOME TEST DATA
INSERT INTO #tblTesting
	SELECT 1, 'Test1' UNION
	SELECT 2, 'Test2' UNION
	SELECT 3, 'Test3' 

SELECT * FROM #tblTesting

-- EXECUTE FROM HERE ----------------------------------------------
DECLARE @TableName VARCHAR(25), @Condition VARCHAR(100)
SET @TableName = '#tblTesting'
SET @Condition = ' TestID = 2 '

DECLARE @Test VARCHAR(50), @SQL NVARCHAR(4000)  
Set @SQL = 'Select @Test = TestName from ' + @TableName + ' where ' + @Condition 
-- PRINT @SQL 
execute sp_executesql @SQL, N'@test varchar(50) output', @test output  

SELECT @test AS TestName
-- EXECUTE TILL HERE ----------------------------------------------

DROP TABLE #tblTesting

Open in new window

0
chapmandewCommented:
Split between RajkumarGS (29113188) and myself (29111758)

AND...read your EE email, PAQ_Man.  :)
0
Rajkumar GsSoftware EngineerCommented:
User's question was how to fill a variable from executed dynamic query.

om_prakash_p's script was not filling a variable, instead writing to a table and selecting the value from it.

The proper solution is that me & chapmandew suggested.

So split points between my comment ( http:#a29159774 ) and chapmandew's comment ( http:#a29111758 )

My comment http:#a29159774 is more accurate than my comment that suggested by chapmandew ( http:#a29113188 ). Because it demonstrates with sample data.

Raj
0
chapmandewCommented:
Agreed...your comment should be the answer as it has the syntax 100% correct.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server 2005

From novice to tech pro — start learning today.