CODEWITHSUNDEEP
sqlsql-server
Pharylon
Pharylon

Reputation: 9916

Changing Variable When Using Dynamic SQL

I hit a problem today where I thought, "Hey, this might be a good time to try some Dynamic SQL!" However, I'm having a problem. The simplified version might be...

Declare @Condition VARCHAR(MAX)

SET @Query = 'INSERT INTO #Report_Table 
                        SELECT column1, column2, column3
                        FROM my_table
                        WHERE column4 = ''' + @Condition +''''
SET @Condition = 'Blah'
EXEC(@Query)
SET @Condition = 'OtherBlah'
EXEC(@Query)

However, I've found this doesn't work. It gives me no results when I try. It appears the @Condition variable must be set before the @Query variable. But by doing that, I can't run the @Query multiple times using different @Conditions. Is there some way to do what I want?

Upvotes: 0

Views: 65

Answers (1)

Anon
Anon

Reputation: 10908

CREATE TABLE #Conditions (condition varchar(max))

INSERT #Condition VALUES
('Blah'),
('OtherBlah')

INSERT INTO #Report_Table
SELECT column1,column2,column3
FROM my_table
INNER JOIN #Conditions
  ON column4 = condition

Upvotes: 2

Related Questions