SELECT vs. SQL Server Stored Procedure

Question

SELECT Val from storedp_Value in the SQL Server Management Studio query editor, is this possible?

UPDATE

I tried to create a temporary table, but it didn't seem to work, so I asked here.

 CREATE TABLE #Result ( batchno_seq_no int ) INSERT #Result EXEC storedp_UPDATEBATCH SELECT * from #Result DROP TABLE #Result RETURN 

Saved UpdateBatch Procedure

 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 to call it from the request window?

UPDATE # 2

Well, I would be grateful for the help in this direction or something else - this is what I am 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 is appreciated. In essence, this SQL code is corrupted as we migrated for Oracle.

Answer 1

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 use the results from a stored procedure in this way and you wrote a stored procedure, seriously consider changing the code as a view or user function. In many cases, you can replace such code with a simpler, more appropriate construct.

Answer 2

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

 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, SQL -

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

Answer 3

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

Answer 4

try it

Change Return

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

in 'Select'

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

Answer 5

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 

Answer 6

I SHOULD miss something.

Since your stored procedure does not return the result set and instead returns an integer using the RETURN function of the stored processes, you simply CANNOT INSERT ANY table (since there is no result set returned at all).

BUT, you can (assuming this is done iteratively, not over a set), just store the return value in a local variable and paste that variable value into any table.

However, if you just want to return the value in the query window results to SSMS, doing INSERT and SELECTING will be redundant. It seems to me that THIS would be enough (in the request 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 far from the base, specify a DDL for "batchno_seq", maybe I can help with that.

Hooray!