CODEWITHSUNDEEP
sql-servert-sqlssms
Allan Chau
Allan Chau

Reputation: 703

How to pass a query as a variable

DECLARE @SomeVariableTable TABLE (
    Field INT
)
INSERT INTO @SomeVariableTable
    SELECT    st.Field
    FROM      SomeTable st;

SELECT    *

FROM      AnotherTable at

WHERE     at.SomeField IN @SomeVariableTable

How can I make the above code work?

I have looked at concatenating the entire column into a VARCHAR and while this works for VARCHARs it doesn't work for INTs.

Upvotes: 0

Views: 41

Answers (2)

M.Ali
M.Ali

Reputation: 69564

This is how you would do what you are trying to do .....

DECLARE @SomeVariableTable TABLE (
    Field INT
)
INSERT INTO @SomeVariableTable
    SELECT    st.Field
    FROM      SomeTable st;

SELECT    *
FROM      AnotherTable at
WHERE     at.SomeField IN (
                           SELECT Field
                           FROM @SomeVariableTable
                           )

But why would you do this anyway? you could have simply done something like 

SELECT    *
FROM      AnotherTable at
WHERE     at.SomeField IN (
                           SELECT Field
                           FROM SomeTable
                           )

Upvotes: 1

Rahul
Rahul

Reputation: 77906

Your code is almost fine except the last SELECT. Change the last SELECT part as below.

SELECT    *
FROM      AnotherTable at    
WHERE     at.SomeField IN (select Field from @SomeVariableTable)

Upvotes: 1

Related Questions