SELECT against stored procedure SQL Server

sql, sql-server, sql-server-2008, t-sql

Solution

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.

Problem

`SELECT Val from storedp_Value` within the query editor of SQL Server Management Studio, is this possible? UPDATE I tried to create a temp table but it didn't seem to work hence why I asked here. ``` CREATE TABLE #Result ( batchno_seq_no int ) INSERT #Result EXEC storedp_UPDATEBATCH SELECT * from #Result DROP TABLE #Result RETURN ``` Stored Procedure UpdateBatch ``` delete from batchno_seq; insert into batchno_seq default values; select @batchno_seq= batchno_seq_no from batchno_seq RETURN @batchno_seq ``` What am I doing wrong and how do I call it from the query window? UPDATE #2 Ok, I'd appreciate help on this one, direction or anything - this is what I'm trying to achieve. ``` select batchno_seq from (delete from batchno_seq;insert into batchno_seq default values; select * from batchno_seq) BATCHNO INTO TEMP_DW_EKSTICKER_CLASSIC ``` This is part of a larger select statement. Any help would be much appreciated. Essentially this SQL is broken as we've migrated for Oracle.

Original source