CODEWITHSUNDEEP
sqlsql-serversql-server-2008
BMaingi
BMaingi

Reputation: 38

How can I use a query result as a column for a separate query?

I have a query as below.

SELECT 'U_' + (LEFT(DATENAME(dw, GETDATE()), 3)) + 'Confirm'

This brings a result of, for instance, U_ThuConfirm

I then need to use that result as a column in a different query with that field in the table I am trying to query. as below;

SELECT ('U_' + (LEFT(DATENAME(dw, GETDATE()), 3)) + 'Confirm') FROM MyTable

I have tried variables, but it is just displaying the result, not actually checking that column.

Upvotes: 1

Views: 87

Answers (2)

Tom Brown
Tom Brown

Reputation: 793

For something like this - where the choices are well-defined you can use a case statement

SELECT  CASE 
        WHEN DATEPART(dw, GetDate()) = 1 THEN U_SunConfirm
        WHEN DATEPART(dw, GetDate()) = 2 THEN U_MonConfirm
        WHEN DATEPART(dw, GetDate()) = 3 THEN U_TueConfirm
        WHEN DATEPART(dw, GetDate()) = 4 THEN U_WedConfirm
         ... etc
        END
        AS Confirms

Upvotes: 0

Brendan
Brendan

Reputation: 1247

You will need create a string that contains your query : 

DECLARE @SQL VARCHAR(500)
SET @SQL = 'SELECT U_' + (LEFT(DATENAME(dw, GETDATE()), 3)) + 'Confirm FROM MyTable'

Then execute it like this

EXEC (@SQL)

Upvotes: 1

Related Questions