CODEWITHSUNDEEP
sql-servert-sql
Question3CPO
Question3CPO

Reputation: 1202

Error: Pass Returned Date From One Procedure To Next Procedure

Based on this article by Pinal Dave.

Error: Operand type clash: date is incompatible with int

I am trying to build a process involving three stored procedures (which individually are called by other processes, thus eliminating the ability to combine them) the first of which returns a date, which is then used for the parameter on the next procedure, and the process repeats itself the third time (the returned value of the second procedure is used as the parameter for the third procedure).

The below code shows a simplified similar process, where one procedure should return a date, which will then be used by the next procedure:

CREATE TABLE Pass(
    ID SMALLINT IDENTITY(1,1),
    IDDate DATE
)

INSERT INTO Pass (IDDate)
VALUES (DATEADD(DD,-1,GETDATE()))
    , (GETDATE())
    , (DATEADD(DD,1,GETDATE()))

CREATE PROCEDURE s_One
AS
BEGIN
    DECLARE @date DATE
    SET @date = DATEADD(DD,1,GETDATE())
    RETURN @date  -- generates the error
END

CREATE PROCEDURE s_Two 
@date DATE
AS
BEGIN

    SELECT *
    FROM IDDate
    WHERE [IDDate] = @date

END

DECLARE @d DATE
EXEC @d = s_One
SELECT @d
EXEC s_Two @d

According to the linked article, in theory, the returned value should be able to be used as a parameter for the next procedure, though the error I'm seeing is an operand type clash with a date and int:

Msg 206, Level 16, State 2, Procedure s_One, Line 6 Operand type clash: date is incompatible with int

Final Change Necessary:

CREATE PROCEDURE s_One
( @date DATE OUTPUT )
AS
BEGIN
    SELECT @date = DATEADD(DD,1,GETDATE())
END

DECLARE @d DATE
EXEC s_One @d OUTPUT
SELECT @d
EXEC s_Two @d

Upvotes: 2

Views: 128

Answers (2)

Question3CPO
Question3CPO

Reputation: 1202

Note: the accepted answer is the correct answer when able to update procedures.

If, however, you are unable to update your stored procedures to allow for an OUTPUT, and you need to pass only one value returned from one procedure to the next (and it's not an INT), one hack I found around this was to declare a variable table with one value, then declare a variable which becomes the value from the table and use it in the next procedure. So, using the example I provided in the OP:

CREATE PROCEDURE s_One
AS
BEGIN
    DECLARE @date DATE
    SET @date = DATEADD(DD,1,GETDATE())
    SELECT @date
END

CREATE PROCEDURE s_Two 
@date DATE
AS
BEGIN

    SELECT *
    FROM IDDate
    WHERE [IDDate] = @date

END

-- Pass a returned variable from one procedure to the next (if no OUTPUT in first procedure is allowed).
DECLARE @d TABLE(
     ID TINYINT DEFAULT 1,
     DDate DATE
)

INSERT INTO @d (DDate)
EXEC s_One

DECLARE @dt DATE
SELECT @dt = DDate FROM @d WHERE ID = 1

EXEC s_Two @dt

Upvotes: 0

Sonam
Sonam

Reputation: 3466

You have to declare a parameter in the stored procedure with the Output keyword to get the return value from the procedure.

CREATE PROCEDURE s_One
@Returndate Date OUTPUT
AS
BEGIN

Also, to save the value of the parameter in a variable that can be used in the calling program, the calling program must use the OUTPUT keyword when executing the procedure.

Exec s_One @Returndate Output

Upvotes: 2

Related Questions