Changing Variable When Using Dynamic SQL

Question

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?

Create a temporary table of conditions (or a CTE) and then use a join... — Tony Hopkinson
– Tony Hopkinson, Commented Nov 20, 2013 at 16:06

Anon · Accepted Answer · 2013-11-20 16:35:55Z

2

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

answered Nov 20, 2013 at 16:35

Anon

10.9k1 gold badge32 silver badges31 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Changing Variable When Using Dynamic SQL

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related