CODEWITHSUNDEEP
sqlsql-serverstored-proceduresssms
PJW
PJW

Reputation: 5417

Stored Procedure Returns NULLS

I am new to stored procedures and haven't used them in my projects previously. I have created my first procedure but it is not returning the expected results.

The procedure is created as follows -

CREATE PROCEDURE GetRetirementDetails @DOB DATE, @Gender VARCHAR(10)
AS
DECLARE @DateRet DATE,
        @AgeRet DECIMAL(18,2)

SELECT @DateRet = [DateRet], @AgeRet = [AgeRet]
FROM [State]
WHERE [From] <= @DOB
AND [To] >= @DOB
AND (Gender = @Gender OR Gender = 'Both')

I am executing the procedure with the following statement -

DECLARE @DateRet DATE,
        @AgeRet DECIMAL(18,2),
        @DOB DATE,
        @Gender VARCHAR(10)

SET @DOB = '1975-10-10'
SET @Gender = 'Male'

EXECUTE GetRetirementDetails @DOB, @Gender
SELECT @DateRet, @AgeRet

The results returned are NULL, NULL

However, when I run the following statement, which is the procedure with the parameters manually input, I get the required results.

SELECT [DateRet], [AgeRet]
FROM [State]
WHERE [From] <= '1975-10-10'
AND [To] >= '1975-10-10'
AND (Gender = 'Male' OR Gender = 'Both')

Required output is NULL, 67.00

I'm sure my errors are basic, but I'm struggling to see what it is given I haven't used stored procedures before. Any help would be most appreciated.

Upvotes: 0

Views: 385

Answers (3)

Hiran
Hiran

Reputation: 1180

You need to change your SP, with output parameters as below:

CREATE PROCEDURE GetRetirementDetails @DOB DATE, @Gender VARCHAR(10)
,@DateRet DATE output,@AgeRet DECIMAL(18,2) output
AS

SELECT @DateRet = [DateRet], @AgeRet = [AgeRet]
FROM [State]
WHERE [From] <= @DOB
AND [To] >= @DOB
AND (Gender = @Gender OR Gender = 'Both')

Then call it as below:

DECLARE @DateRet DATE,
        @AgeRet DECIMAL(18,2),
        @DOB DATE,
        @Gender VARCHAR(10)

SET @DOB = '1975-10-10'
SET @Gender = 'Male'

EXECUTE GetRetirementDetails @DOB, @Gender,@DateRet,@AgeRet
SELECT @DateRet, @AgeRet

Upvotes: 0

Keith
Keith

Reputation: 1038

Just want to mention, that you can just return data in a stored proc, you do not need to have it as an output parameter, as you cannot hold more than one value in the parameters you have set. In your example, if the data returns more than one row, you will only get the last available record. e.g.

IF OBJECT_ID('tempdb..#tmpTest') IS NOT NULL DROP TABLE #tmpTest
GO
DECLARE @dteRunDate DATETIME;
SELECT @dteRunDate = GETDATE();

CREATE TABLE #tmpTest
(
    [FROM]      DATETIME    ,
    [TO]        DATETIME    ,
    [GENDER]    NVARCHAR(10),
    [AGERET]    INT
)
INSERT INTO #tmpTest
(
    [FROM]                  ,
    [TO]                    ,
    [GENDER]                ,
    [AGERET]
)
SELECT @dteRunDate,@dteRunDate,'M',10 UNION ALL
SELECT @dteRunDate,@dteRunDate,'F',NULL UNION ALL
SELECT @dteRunDate,@dteRunDate,'BOTH',20;


DECLARE @DateRet    DATETIME                ,
        @AgeRet     DECIMAL(18,2)           ,
        @DOB        DATETIME                ,
        @Gender     NVARCHAR(10)

SELECT  @DOB = @dteRunDate;
SELECT  @Gender = 'M';


SELECT  @DateRet    = [FROM]        , 
        @AgeRet     = [AgeRet]
FROM    #tmpTest
WHERE   [From] <=   @DOB
AND     [To]    >=  @DOB
AND     (Gender = @Gender OR Gender = 'Both')


SELECT  @DateRet,@AgeRet;

Will ignore all matching rows and return the last. Instead, I could use: 

IF OBJECT_ID('tempdb..#tmpTest') IS NOT NULL DROP TABLE #tmpTest
GO
DECLARE @dteRunDate DATETIME;
SELECT @dteRunDate = GETDATE();

CREATE TABLE #tmpTest
(
    [FROM]      DATETIME    ,
    [TO]        DATETIME    ,
    [GENDER]    NVARCHAR(10),
    [AGERET]    INT
)
INSERT INTO #tmpTest
(
    [FROM]                  ,
    [TO]                    ,
    [GENDER]                ,
    [AGERET]
)
SELECT @dteRunDate,@dteRunDate,'M',10 UNION ALL
SELECT @dteRunDate,@dteRunDate,'F',NULL UNION ALL
SELECT @dteRunDate,@dteRunDate,'BOTH',20;


DECLARE @DOB        DATETIME                ,
        @Gender     NVARCHAR(10)

SELECT  @DOB = @dteRunDate;
SELECT  @Gender = 'M';


SELECT  [FROM]              , 
        [AgeRet]
FROM    #tmpTest
WHERE   [From] <=   @DOB
AND     [To]    >=  @DOB
AND     (Gender = @Gender OR Gender = 'Both')

Which will return all rows that match.

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1269873

If you want to return values from a stored procedure, you need to declare them as parameters . . . and then call correctly:

I am new to stored procedures and haven't used them in my projects previously. I have created my first procedure but it is not returning the expected results.

The procedure is created as follows -

CREATE PROCEDURE GetRetirementDetails (
    @in_DOB DATE,
    @in_Gender VARCHAR(10),
    @out_DateRet DATE OUTPUT,
    @out_AgeRet DECIMAL(18, 2) OUTPUT
) AS
BEGIN
    SELECT @out_DateRet = [DateRet], @out_AgeRet = [AgeRet]
    FROM [State]
    WHERE [From] <= @in_DOB AND [To] >= @in_DOB AND
          (Gender = @in_Gender OR Gender = 'Both')
END;

Then call it as:

DECLARE @DateRet DATE,
        @AgeRet DECIMAL(18,2),
        @DOB DATE,
        @Gender VARCHAR(10);

SET @DOB = '1975-10-10';
SET @Gender = 'Male';

EXECUTE GetRetirementDetails @DOB, @Gender, @DateRet OUTPUT, @AgeRet OUTPUT;
SELECT @DateRet, @AgeRet;

Upvotes: 3

Related Questions