jdphenix jdphenix - 2 months ago 8
SQL Question

Assign name to query temporarily

Is there a mechanism in T-SQL that allows one to name a query temporarily?

Specifically I would like to be able to do this without using stored procedures, as these will ultimately be one-offs, and I'd like them to not exist after the end of the batch.

Something like,

/* this is woefully invalid syntax */
DECLARE @reps = SELECT DISTINCT NAME FROM Representatives

SELECT * FROM OtherTable
WHERE RepName not in @reps

Answer

If I understand your question, I think you may be looking for some Dynamic SQL

Declare @Reps varchar(max) = 'Select Distinct [Name] FROM Representatives'

Declare @SQL varchar(max) = 'Select * From OtherTable Where RepName not in ('+@Reps+')'
Exec(@SQL)

Another option would be to create a Table Variable

Declare @Reps table (Name varchar(50))
Insert Into @Reps Select Distinct Name From Representatives

Select * 
 From  OtherTable
 Where RepName not in (Select * From @Reps)
Comments