Get EXEC return value

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

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

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.

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.

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

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

otherwise

SELECT top 1 @Addr = Address FROM TestTable

Cheers
Twan

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

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