CODEWITHSUNDEEP
sqlsql-servert-sql
Grafit
Grafit

Reputation: 699

Is it possible to set a part of a select statement in a variable

I have a query of which the select-part is really long. I'd like to split this in several pieces, especially because some parts are in there twice or even more often.

What I'd like is something like the following:

Declare @SQLPart as varchar(1000)
Set @SQLPart = 'Field1,
                case ... as Field2,'

Select ..., @SQLPart, ... From .....

Unfortunately this results error messages. I tried something like EXEC(@SQLPart) as well but of course this also didn't work. How would I solve this?

Upvotes: 3

Views: 224

Answers (3)

Backs
Backs

Reputation: 24903

Yes, dynamic sql and sp_executesql:

CREATE TABLE ##Temp (Field1 int, Field2 int)

Declare @SQLPart nvarchar(1000)
Set @SQLPart = N'Field1, Field2 '

DECLARE @SQL nvarchar(1000) = N'SELECT ' + @SQLPart + 'FROM ##Temp'
PRINT @SQL

EXEC sp_executesql @SQL

DROP TABLE ##Temp

Your SQL code must be nvarchar type. Alse sp_executesql is better than EXECUTE function, when you have many similar queries, sp_executesql caches executaion plans, and it can be better in perfomance.

Upvotes: 4

John Cappelletti
John Cappelletti

Reputation: 81970

SQL Server does not support Macro-Substitution, so you would have to use Dynamic SQL.

Declare @SQL varchar(max) ='Select ... ' + @SQLPart + '... from ...'
Exec(@SQL)

Upvotes: 2

Abdul Rasheed
Abdul Rasheed

Reputation: 6719

You can use dynamic sql here,and use a EXECUTE keyword to execute this dynamic query

Declare @SQLPart as varchar(1000)
Set @SQLPart = 'Field1,
                case ... as Field2,'

EXECUTE ('SELECT ....,'+@SQLPart+',... FROM ...')

Upvotes: 2

Related Questions