November 30, 2012 at 5:20 am
I have a question about passing values from on stored procedure to another that I just can't think of the answer to...
I've written one stored procedure that declares @timestamp at the beginning...
@TimeStamp DATETIME
SET @TimeStamp = DATEADD(MM, -1, CAST(FLOOR( CAST( GETDATE() AS FLOAT ) )AS DATETIME))
Then the sproc does a load of work that will take a fair few hours (past midnight) before it needs to call another sproc with the following so it uses the same time...
EXEC sp_storedproc2 @TimeStamp
However, in the second stored procedure (that runs on a different database which is why there is a second stored proc) I can't for the life of me think how to code it so that it picks up the @timestamp:
DELETE FROM Table2 WHERE [Date] < @TimeStamp
I'm sure as soon as I see the answer I'll kick myself!!
Dh g gjhfvghhgfdfg
November 30, 2012 at 5:25 am
All you will need to do is to qualify the DB name when executing it from within the same proc
Take the below code, DB1 calls DB2 and passes in the same @TimeStamp parameter
CREATE PROCEDURE DB1.dbo.Proc1
AS
DECLARE @TimeStamp DATETIME
SET @TimeStamp = DATEADD(MM, -1, CAST(FLOOR( CAST( GETDATE() AS FLOAT ) )AS DATETIME))
DO WHAT I NEED TO DO...
...
...
...
--Now call the other procedure in the other database passing in the @TimeStamp parameter above
--using the 3 part naming convention DBName.Schema.Object
EXEC DB2.dbo.Proc2 @TimeStamp
GO
CREATE PROCEDURE DB2.dbo.Proc2 (@TimeStamp DATETIME)
AS
DELETE FROM Table2 WHERE [DATEADD] < @TimeStamp
GO
November 30, 2012 at 5:32 am
Perfect! Thanks!
It was this bit...
CREATE PROCEDURE DB2.dbo.Proc2 (@TimeStamp DATETIME)
AS
DELETE FROM Table2 WHERE [DATEADD] < @TimeStamp
GO
That I just couldn't remember the format for!
And yes, as soon as I saw that it was obvious in my head.... I blame Friday brain!
Dh g gjhfvghhgfdfg
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply