take a value from a query and make it a variable in an SP

[PreacherUK]

Hi all,
Below is the code I am using in my stored procedure. I want to pass in the date. I will be taking this date from a field in a table. I am extracting this date with the following query:

SELECT TOP 100 PERCENT Proc_Date
FROM dbo.tblGLNLoanall
GROUP BY Proc_Date
ORDER BY Proc_Date

How would I go about passing this date that is returned into the sp below?

I am currently using this to run my sp (for testing purposes)

DECLARE @Now AS DateTime
SET @Now = CAST('03 Mar 2004' AS DATETIME)
EXEC aaatest_output_days @Now
GO


I eventually want to run this from a DTS package as part of a larger import process using an 'Execute SQL Task' object, this should be possible yes?.

CREATE PROCEDURE aaatest_output_days
(
@Date As DateTime
)
AS

-- SET DATEFIRST to default value of 1 (Monday).
SET DATEFIRST 1

-- Declare variables
DECLARE @DayofWeek AS INTEGER
SET @DayOfWeek = (SELECT DATEPART(dw, @Date))



If @dayofweek = 5
BEGIN
-- this should run if it is friday
-- Note this is just for testing
PRINT ('IT IS FRIDAY (' + CAST (@Date As VARCHAR(11)) + ' - ' + CAST(@DayOfWeek AS VARCHAR(2))+ ')')
END
Else
BEGIN
-- this should run if it is not friday
PRINT ('IT IS NOT FRIDAY (' + CAST (@Date As VARCHAR(11)) + ' - ' + CAST(@DayOfWeek AS VARCHAR(2))+ ')')
END
GO


Thanks for any help or pointers
Dave

 

[ArtieChoke]

You could make another procedure that will get the date, then call the second procedure:

declare @mydate datetime
SELECT @mydate = TOP 100 PERCENT Proc_Date
FROM dbo.tblGLNLoanall
GROUP BY Proc_Date
ORDER BY Proc_Date

exec aaatest_output_days @mydate

"I think we're all Bozos on this bus!" - Firesign Theatre

 

[Kalin]

Wouldn't it be easier to use a cursor, which retrieves the date, store the date in a variable and execute the procedure within the cursor loop.

Grtz,

Kalin

 

[PreacherUK]

Thanks for the feedback guys. I solved this a couple days ago, using a similar approach as artie suggests:

DECLARE @Now AS DateTime
SELECT @now = ( SELECT TOP 100 PERCENT Proc_Date FROM dbo.tblGLNLoanall GROUP BY Proc_Date ORDER BY Proc_Date)
EXEC sp_multi_append_mtd @Now
GO

That passed the date into my sp just fine. I'm fortunate in that there will only ever be one date in the table I'm referring to.

I was warned away from using cursors as being the more processor intensive approach and causing record locking problems.