In T-SQL, this is allowed:
DECLARE @SelectedValue int
SELECT @SelectedValue = MyIntField FROM MyTable WHERE MyPrimaryKeyField = 1
So, it's possible to get the value of a SELECT and stuff it in a variable (provided it's scalar, obviously).
If I put the same select logic in a stored procedure:
CREATE PROCEDURE GetMyInt
AS
SELECT MyIntField FROM MyTable WHERE MyPrimaryKeyField = 1
Can I get the output of this stored procedure and stuff it in a variable?
Something like:
DECLARE @SelectedValue int
SELECT @SelectedValue = EXEC GetMyInt
(I know the syntax above is not allowed because I tried it!)
This question is related to
tsql
stored-procedures
select
The answer is
there are three ways you can use: the RETURN value, and OUTPUT parameter and a result set
ALSO, watch out if you use the pattern: SELECT @Variable=column FROM table ...
if there are multiple rows returned from the query, your @Variable will only contain the value from the last row returned by the query.
RETURN VALUE
since your query returns an int field, at least based on how you named it. you can use this trick:
CREATE PROCEDURE GetMyInt
( @Param int)
AS
DECLARE @ReturnValue int
SELECT @ReturnValue=MyIntField FROM MyTable WHERE MyPrimaryKeyField = @Param
RETURN @ReturnValue
GO
and now call your procedure like:
DECLARE @SelectedValue int
,@Param int
SET @Param=1
EXEC @SelectedValue = GetMyInt @Param
PRINT @SelectedValue
this will only work for INTs, because RETURN can only return a single int value and nulls are converted to a zero.
OUTPUT PARAMETER
you can use an output parameter:
CREATE PROCEDURE GetMyInt
( @Param int
,@OutValue int OUTPUT)
AS
SELECT @OutValue=MyIntField FROM MyTable WHERE MyPrimaryKeyField = @Param
RETURN 0
GO
and now call your procedure like:
DECLARE @SelectedValue int
,@Param int
SET @Param=1
EXEC GetMyInt @Param, @SelectedValue OUTPUT
PRINT @SelectedValue
Output parameters can only return one value, but can be any data type
RESULT SET for a result set make the procedure like:
CREATE PROCEDURE GetMyInt
( @Param int)
AS
SELECT MyIntField FROM MyTable WHERE MyPrimaryKeyField = @Param
RETURN 0
GO
use it like:
DECLARE @ResultSet table (SelectedValue int)
DECLARE @Param int
SET @Param=1
INSERT INTO @ResultSet (SelectedValue)
EXEC GetMyInt @Param
SELECT * FROM @ResultSet
result sets can have many rows and many columns of any data type
You'd need to use return values.
DECLARE @SelectedValue int
CREATE PROCEDURE GetMyInt (@MyIntField int OUTPUT)
AS
SELECT @MyIntField = MyIntField FROM MyTable WHERE MyPrimaryKeyField = 1
Then you call it like this:
EXEC GetMyInt OUTPUT @SelectedValue
Try do this:
EXEC @SelectedValue = GetMyInt
There is also a combination, you can use a return value with a recordset:
--Stored Procedure--
CREATE PROCEDURE [TestProc]
AS
BEGIN
DECLARE @Temp TABLE
(
[Name] VARCHAR(50)
)
INSERT INTO @Temp VALUES ('Mark')
INSERT INTO @Temp VALUES ('John')
INSERT INTO @Temp VALUES ('Jane')
INSERT INTO @Temp VALUES ('Mary')
-- Get recordset
SELECT * FROM @Temp
DECLARE @ReturnValue INT
SELECT @ReturnValue = COUNT([Name]) FROM @Temp
-- Return count
RETURN @ReturnValue
END
--Calling Code--
DECLARE @SelectedValue int
EXEC @SelectedValue = [TestProc]
SELECT @SelectedValue
--Results--
Source: Stackoverflow.com