Query to return column form other query (1 Viewer)

[imtheodore]

Not sure if you can understand this but I'll try...

I need to write a stored procedure that runs a query and I need the result of that query (which will always be one record) to be the criteria for the next query

Example...

select user form logins where logindate between day1 and day2

then query 2 would have to be

select (query above result) from users where...

Is this possible?

Thanks,
Dave

 

[ByteMyzer]

Yes, using Cursors. See the following link: http://msdn.microsoft.com/en-us/library/ms180169.aspx

 

[pbaldy]

Could also be done with a temp table:

SELECT ...
INTO #FirstQuery
FROM...
WHERE...

then

SELECT...
FROM Users INNER JOIN #FirstQuery ON...
WHERE...

or

SELECT...
FROM Users
WHERE FieldName IN(SELECT FieldName FROM #FirstQuery)

or whatever.

 

[imtheodore]

Thanks guys, I figured it out using cursors. In case anyone esle has this problem, I'll post my Procedure:

CREATE PROCEDURE [dbo].[Build_Grid]
@First_Date_Parameter datetime,
@Second_Date_Parameter datetime


AS
SET NOCOUNT ON
Truncate Table Grid_Date
DECLARE @login NVARCHAR(30)
DECLARE @Credentials NVARCHAR(30)
DECLARE @logins NVARCHAR(30)
DECLARE @SSQL NVARCHAR(200)
Select @Credentials = 'CRNA'
-- Create a cursor containing all of the CRNA's
DECLARE MyCursor CURSOR FOR SELECT login FROM logins where credentials = @Credentials order by Lastname, Call_Descript
OPEN MyCursor
-- Fetch the first CRNA
FETCH NEXT FROM MyCursor into @login
WHILE @@FETCH_STATUS = 0
BEGIN
-- get a single CRNA
select @logins = (select login from logins where login = @login)-- order by lastname, call_descript)

BEGIN
set @sSQL=('insert into grid_date (login,day,date,shift)(select @logins,day,date,' + @logins + ' from ccstaff where date between @first_Date_parameter and @second_date_parameter)')

EXECUTE sp_executesql @sSQL,N'@logins nvarchar(30),@first_date_parameter datetime,@second_date_parameter datetime',@logins,@first_date_parameter,@second_date_parameter
end
-- Fetch next patient from the Cursor
FETCH NEXT FROM MyCursor into @login
End
CLOSE MyCursor
DEALLOCATE MyCursor