CODEWITHSUNDEEP
sqlsql-servert-sqlstored-procedures
Kirk Fleming
Kirk Fleming

Reputation: 517

User Stored Proc Returns NULL--underlying query works as planned

Yes, it's another one of these. Questions with near-identical titles did not apply or I didn't see the connection.

My usp looks like this:

>    CREATE PROCEDURE uspHasItem
>         @sr_id INT,
>         @ActionableItem VARCHAR(1) OUTPUT
>    AS BEGIN
>         SELECT @ActionableItem = (CASE
>         WHEN EXISTS (
>                      SELECT stuff FROM here
>                       WHERE condition
>                         AND field = (
>                                      SELECT MAX(something)
>                                        FROM another_place
>                                       WHERE field1 = @sr_id)
>                     )
>         THEN '1'
>         ELSE '0' END);
>     END
>     RETURN

I call this in the customary fashion, AFAIK:

DECLARE @Actionable VARCHAR(1);
EXEC uspHasItem
    @sr_id = 1222,
    @ActionableItem = @Actionable;
SELECT @Actionable AS AI;

I can verify the input values to be valid and it doesn't matter what I provide, and in anycase it shouldn't matter what the input value is--I always get NULL as my output.

Now, if I pull out that query and tweak it provide the @sr_id values from a table:

SELECT
 SRT.SR_ID,
 CASE
        WHEN EXISTS (
                     SELECT stuff FROM here
                      WHERE condition
                        AND field = (
                                     SELECT MAX(something)
                                       FROM another_place
                                      WHERE field1 = SRT.SR_ID)
                    )
        THEN '1'
        ELSE '0' END AS ACTIONABLE
FROM SRTABLE SRT;

I get exactly the result I expect. I am not seeing the problem and am not getting any error.

How is it possible to not get any value, and is my test execution of the usp with the SELECT statement afterward where the actual problem is? Suggestions?

Upvotes: 1

Views: 43

Answers (2)

Dave C
Dave C

Reputation: 7402

When you run your proc, don't put a ; after every line, this breaks the scope, also add OUTPUT

DECLARE @Actionable VARCHAR(1)
EXEC uspHasItem
    @sr_id = 1222,
    @ActionableItem = @Actionable OUTPUT
SELECT @Actionable AS AI;

Upvotes: -1

Rick S
Rick S

Reputation: 6586

Did you forget output?

DECLARE @Actionable VARCHAR(1)

EXEC uspHasItem
    @sr_id = 1222,
    @ActionableItem = @Actionable output
SELECT @Actionable AS AI

Upvotes: 3

Related Questions