Using SELECT, How to select 2 columns into 2 variables

[btturner]

In a SQL 2K SP, how can I select 2 columns into 2 variables w/out specifying a DECLARE/OPEN/FETCH/CLOSE Cursor.

Example: (this SQL is not working)

declare @PostStartDate datetime, @PostEndDate datetime

select PostStartDate, PostStartDate
into @PostStartDate, @PostEndDate
from AuditJob

 

[LFCfan]

i think:

select
@PostStartDate = PostStartDate,
@PostEndDate = PostStartDate
from AuditJob
where ...


this would only work if there's just one row returned though!

HTH
LFCfan

 

[LFCfan]

Not sure at all if that answers the question you asked - if not, apologies, and put it down to the lateness of the day!
)
LFCfan

 

[tlbroadbent]

select
@PostStartDate = PostStartDate,
@PostEndDate = PostStartDate
from AuditJob
where ...

This will actually work even if multiple rows are returned by the query. The values from the last row processed will be stored in the variables after the statment completes. This is usually not a desirable result. It is best to add WHERE criteria so that only one row is returned by the query.

If you want to get the best answer for your question read faq183-874 and faq183-3179.
Terry L. Broadbent - DBA
SQL Server Page:

http://tlbroadbent.home.attbi.com/sql/sql_articles.htm

 

[gvphubli]

lfcfan if right, so just think can u make sure the query will return only one row..or else use cursor...to loop through all the rows. And get seperate set of variables..for each row processed.