How to SELECT FROM stored procedure
You can use a User-defined function or a view instead of a procedure.
A procedure can return multiple result sets, each with its own schema. It's not suitable for using in a SELECT
statement.
How to select from stored procedure output
You could store the result of the stored procedure into a temporary table (or a table variable, or even a physical table) and then query that one:
--drop table if exists #temp
create table #temp(name nvarchar(100), rows int, reserved nvarchar(100), data nvarchar(100), index_size nvarchar(100), unused nvarchar(100))
insert into #temp
exec sp_spaceused 'dummytable'
select [rows], [reserved]
from #temp
SELECT against stored procedure SQL Server
Well, no. To select from a stored procedure you can do the following:
declare @t table (
-- columns that are returned here
);
insert into @t(<column list here>)
exec('storedp_Value');
If you are using the results from a stored procedure in this way and you wrote the stored procedure, seriously consider changing the code to be a view or user defined function. In many cases, you can replace such code with a simpler, better suited construct.
T-SQL get SELECTed value of stored procedure
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
Related Topics
Detecting Column Changes in a Postgres Update Trigger
Notify My Wcf Service When My Database Is Updated
How to Extract Multiple Strings from Single Rows in SQL Server
Hive - How to Further Optimize a Hiveql Query
Convert Delimited String to Rows in Oracle
Ddmmyyyy to SQL Datetime in SQL
SQL 2 Counts with Different Filter
How to Write Update Query to Update Two Tables with SQL Data Source
Inserting Multiple Rows in Sybase Ase
Derby's Handling of Null Values
Good Database and Structure to Store Synonyms
Time Based Priority in Active Record Query
Is This Normalization Correct? (Two Many-To-Manys Connected by a Many-To-One)
SQL - Converting 24-Hour ("Military") Time (2145) to "Am/Pm Time" (9:45 Pm)