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

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.

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

Select allOpen in new window

Well, I was assuming @SQL was already declared since it was already being used.

No. That's not what I mean.

I mean the character N'

Without which I got an error.

Raj

yep, good catch.

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.

Then you're wrong...you obviosly didn't try out suggestions.  Im objecting.

Then you're wrong...you obviosly didn't try out suggestions.  Im objecting.

dhinckle,

Try out our suggestions.

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

Raj

Split between RajkumarGS (29113188) and myself (29111758)

AND...read your EE email, PAQ_Man.  :)

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

Agreed...your comment should be the answer as it has the syntax 100% correct.