CODEWITHSUNDEEP
sqlt-sql
Jamie Babineau
Jamie Babineau

Reputation: 766

SQL, print a column from a select query

I am trying to print a particular column from a table in a message to the user. Here's the table example

StateCode  |   EffectiveDate   |   ExpirationDate
---------------------------------------------
  AK       |   2011-12-31      |   2012-12-31
  AL       |   2011-12-31      |   2015-12-31

and so on for all states. What I'd like to do is select that the GETDATE() is not between the EffectiveDate and ExpirationDate and print a message to the user. The Select Statement I have works but if more than one state is returned it crashes. Any way to save a list and then print that? or another approach I can take

Heres the Select Query I am using now that works for 1 state returned

DECLARE @missingStates varChar(30)
SET @missingStates = Select StateCode FROM StateTable
   WHERE GETDATE() NOT Between StateTable.EffectiveDate AND StateTable.ExpirationDate

PRINT 'States Missing Effective Models'
PRINT @missingStates

Thanks for any Help

Upvotes: 6

Views: 26561

Answers (2)

juergen d
juergen d

Reputation: 204924

To get a comma seperated list of states do:

Select case when @missingStates = ''
            then @missingStates = Code 
            else @missingStates = @missingStates + ',' + Code 
       end as states
FROM StateTable
WHERE GETDATE() NOT Between StateTable.EffectiveDate AND StateTable.ExpirationDate

Upvotes: 1

Yuriy Galanter
Yuriy Galanter

Reputation: 39807

You have to combine multiple values into a single variable:

DECLARE @missingStates varChar(30)
SET @missingStates = ''

 Select @missingStates = @missingStates + Code + ' ' FROM StateTable
   WHERE GETDATE() NOT Between StateTable.EffectiveDate AND StateTable.ExpirationDate

PRINT 'States Missing Effective Models'
PRINT @missingStates

Upvotes: 3

Related Questions