locked
returned value from stored procedure cannot be used in query RRS feed

  • Question

  • User1868852945 posted

    the @myVariable below has the expected value but then is not available for use in the query that follows.  The results grid shows my expected value but when  I inspect the value just prior to the query it shows zero.

    Any ideas how to fix?  thanks.

    use myDB;
    declare @myVariable varchar(128)
    exec @myVariable = get_prior_baseline @p_db_type='P',@p_own='X',@p_env='Q',@p_baseline='Y';

    select myValue
    from myTable
    where myColumn = @myVariable;  (zero rows get returned but I have proven that it should return a row)

    Thursday, January 7, 2016 5:44 AM

Answers

  • User1124521738 posted

    try this (you have to assign value to the output variable @total_sales)

    alter PROCEDURE get_sales @type varchar(1), @total_sales INT OUTPUT
    AS
    set @total_sales = (SELECT top 1 qty 
        FROM sales a
        where type = @type)
    go
    

    and call same as before

    declare @w_sales int
    exec get_sales @type='X', @total_sales=@w_sales OUTPUT
    select qty from sales where qty = @w_sales
    go

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Thursday, January 7, 2016 10:13 PM

All replies

  • User1124521738 posted

    change to use an output variable https://www.toadworld.com/platforms/sql-server/w/wiki/10261.stored-procedures-output-parameters-return-values

    see below example (quoted from above link)

    CREATE PROC sales_for_type @type VARCHAR(55), @total_sales INT OUTPUT
    AS
    SELECT SUM(qty) FROM sales a, titles b
    WHERE
    a.title_id = b.title_id
    and
    b.type = @type

    This procedure can be executed as follows:

    DECLARE @total_sales_business int
    EXEC sales_for_type business, @total_sales=@total_sales_business OUTPUT

    Results:

    90

    Thursday, January 7, 2016 6:50 AM
  • User1868852945 posted

    Thanks for the feedback, but my question was how to use the returned output to a subsequent query.  Here is a simple example where the PROC returns the correct value of 100, but when used in the subsequent query it is null (or in this case I think zero), causing no rows returned...

    USE [myDB]
    drop TABLE dbo.sales
    CREATE TABLE dbo.sales(type varchar(1), qty numeric(18, 0) NULL)
    insert into sales values('X',100);
    insert into sales values('Y',200);
    go
    alter PROCEDURE get_sales @type varchar(1), @total_sales INT OUTPUT
    AS
    SELECT qty FROM sales a
    where type = @type
    go

    declare @w_sales int
    exec get_sales @type='X', @total_sales=@w_sales OUTPUT
    select qty from sales where qty = @w_sales
    go

    Thanks again and am hoping for a solution - this must be an issue with the handling of the declared variable w_sales.

    Thursday, January 7, 2016 10:03 PM
  • User1124521738 posted

    try this (you have to assign value to the output variable @total_sales)

    alter PROCEDURE get_sales @type varchar(1), @total_sales INT OUTPUT
    AS
    set @total_sales = (SELECT top 1 qty 
        FROM sales a
        where type = @type)
    go
    

    and call same as before

    declare @w_sales int
    exec get_sales @type='X', @total_sales=@w_sales OUTPUT
    select qty from sales where qty = @w_sales
    go

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Thursday, January 7, 2016 10:13 PM