CODEWITHSUNDEEP
sqlsql-serversql-server-2008t-sql
Nathan Smith
Nathan Smith

Reputation: 8347

SELECT against stored procedure SQL Server

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.

Upvotes: 11

Views: 61404

Answers (6)

Steve Rowland
Steve Rowland

Reputation: 36

I MUST be missing something.

Since your stored procedure does not return a result set, and instead returns an integer, using the RETURN functionality of stored procs, you simply CANNOT INSERT into ANY table (since there isn't any result set coming back, at all).

BUT, you can (assuming that this is done iteratively, and not over a set) simply store the return value into a local variable, and insert that variable's value into whatever table is necessary.

However, if you simply want to return the value in the results of a Query Window in SSMS, doing the INSERT and SELECTING is overkill. It seems to me like THIS would suffice (in a query window):

DECLARE @RetVal INT = 0;

SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
BEGIN TRANSACTION;
EXEC @RetVal = storedp_UPDATEBATCH;
COMMIT TRANSACTION;
SELECT @RetVal;
  --OR
  --PRINT @RetVal;

If this is way far off base, please provide the DDL for "batchno_seq", maybe I can be of better assistance that way.

Cheers!

Upvotes: 0

Konstantin Taranov
Konstantin Taranov

Reputation: 629

The best article (in my opinion) about all possible methods for sharing data between stored procedures in SQL Server you can find here: http://www.sommarskog.se/share_data.html

Upvotes: 2

user3588999
user3588999

Reputation: 1

My approach 

select * into new_table  from (select t1.col1,t1.col2,..
from table1 t1 
union 
select t2.cola,t2.colb,..
from table2 t2) as union_table

Upvotes: 0

tsohtan
tsohtan

Reputation: 850

Try this

Change 'Return'

delete from batchno_seq;   
insert into batchno_seq default values;
select @batchno_seq= batchno_seq_no from batchno_seq
RETURN @batchno_seq

to 'Select'

delete from batchno_seq;   
insert into batchno_seq default values;
select @batchno_seq= batchno_seq_no from batchno_seq
SELECT @batchno_seq

Upvotes: 0

T I
T I

Reputation: 9933

This is not possible in sql server, you can insert the results into a temp table and then further query that

CREATE TABLE #temp ( /* columns */ )

INSERT INTO #temp ( /* columns */ )
EXEC sp_MyStoredProc

SELECT * FROM #temp
WHERE 1=1

DROP TABLE #temp

Or you can use OPENQUERY but this requires setting up a linked server, the SQL is

SELECT * FROM (ThisServer, 'Database.Schema.ProcedureName <params>')

Upvotes: 5

Gordon Linoff
Gordon Linoff

Reputation: 1269763

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.

Upvotes: 14

Related Questions