CODEWITHSUNDEEP
sql-servert-sqlvariablesselect
rlphilli
rlphilli

Reputation: 111

Only the Select Portion of Query as Variable to be used multiple times

I have a set of queries that use the same Select portion of the queries with different joins, Where clauses, etc. below the Select portion. The Select portion is long and since I have to run version of this query close to 30 times, whenever I have to make a change, I have to make that change 30 times. Is there a way to capture just the Select portion of these queries as a variable and call that variable each time? This process is very cumbersome, so if I were able to do this for the Select portion and most of the Where clauses could be condensed into about 5 distinct groupings, that would be wonderful, but I have no idea of how to do it. Here's a simple example of what I'm looking for:

DECLARE @TestSelect varchar(max) = (SELECT Field1, Field2, Field3, ..., Field 22);

@TestSelect
FROM Table1
LEFT OUTER JOIN Table2
ON Table1.id = Table2.id

Is this possible using tsql? And if not using tsql, what would be the best vehicle for this, because as these steps are being run, I am inserting the results into another table.

Upvotes: 0

Views: 43

Answers (1)

Garrett Vlieger
Garrett Vlieger

Reputation: 9494

The best way to do this is to build your query in a string and then use sp_executesql to run the dynamic query. For example:

DECLARE @TestSelect varchar(max)
DECLARE @TestQuery varchar(max)

SET @TestSelect = '(SELECT Field1, Field2, Field3, ..., Field 22)'
SET @TestQuery = @TestSelect + ' FROM Table1 LEFT OUTER JOIN Table2 ON Table1.id = Table2.id'

EXECUTE sp_executesql @TestQuery

Upvotes: 2

Related Questions