SQL Server Performance

Get EXEC return value

Discussion in 'General Developer Questions' started by CanadaDBA, Jul 21, 2004.

  1. CanadaDBA New Member

    How can I assign returned values from an exec command?

    declare @Addr VarChar(100)

    exec( SELECT top 1 Address FROM TestTable )


    What about if the SELECT part of the above example returns a matrix?

    CanadaDBA
  2. rlahoty New Member

    Try something like this:

    USE northwind
    GO

    DECLARE
    @categoryid INT

    BEGIN

    EXEC sp_executesql N'SELECT TOP 1 categoryid FROM Categories',
    N'@categoryid INT OUTPUT', @categoryid OUTPUT

    PRINT @categoryid

    END



    This is when your query returns just one value.

    HTH
    -Rajeev Lahoty
  3. Chappy New Member

    The method posted above is useful for *more* than one return value
    A simpler way for a single return value is..



    CREATE PROCEDURE dbo.retValTest
    AS
    BEGIN
    RETURN 6
    END
    GO

    DECLARE @retVal int
    EXEC @retVal = retValTest
    PRINT 'retVal = '
    PRINT @retVal

    Catching the return value is not mandatory when a procedure uses RETURN.
  4. rlahoty New Member

    The method that I posted is not valid for matrix of results, right? It is possible to get more than one values for one row of output of course but it won't do any good if the query returned more than one row, right?

    Correct me, if I am wrong.
  5. Chappy New Member

    Sorry, I should have been clearer. I meant that by using your method, you could define as many output parameters as you wanted (each output param being used to return a single value), whereas my method only supports one, because you can only RETURN a single integer
  6. Twan New Member


    and presumably the exec command is being used because the sql will be dynamic?

    otherwise

    SELECT top 1 @Addr = Address FROM TestTable

    Cheers
    Twan
  7. CanadaDBA New Member

    EXECuting a SP returns the value but my question was getting result from EXEC(...), and I think Rajeev's code can help. Thanks both of you.

    What about if the SELECT returns more than one row? I think I have to handle it in a WHILE. Right?


    quote:Originally posted by Chappy

    The method posted above is useful for *more* than one return value
    A simpler way for a single return value is..



    CREATE PROCEDURE dbo.retValTest
    AS
    BEGIN
    RETURN 6
    END
    GO

    DECLARE @retVal int
    EXEC @retVal = retValTest
    PRINT 'retVal = '
    PRINT @retVal

    Catching the return value is not mandatory when a procedure uses RETURN.

    CanadaDBA
  8. CanadaDBA New Member

    Sure! But In some situation you have to use EXEC because of the SELECT command is not known and is going to build by previous codes.

    ...
    SET @Cmd = 'SELECT '
    SET @Cmd = @Cmd + case cond1 then ...
    ...
    EXEC (@Cmd)


    quote:Originally posted by Twan


    and presumably the exec command is being used because the sql will be dynamic?

    otherwise

    SELECT top 1 @Addr = Address FROM TestTable

    Cheers
    Twan

    CanadaDBA

Share This Page