选择存储过程SQL Server。

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

从SQL Server Management Studio的查询编辑器storedp_Value中选择Val，是否可能?

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

存储过程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

更新# 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.

这是一个较大的select语句的一部分。如有任何帮助，我们将不胜感激。本质上，这个SQL已经被破坏了，因为我们已经为Oracle迁移了。

6 个解决方案

#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 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.

如果您以这种方式使用存储过程的结果并编写了存储过程，请认真考虑将代码更改为视图或用户定义的函数。在许多情况下，您可以用更简单、更适合的构造来替换这些代码。

#2

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

这在sql server中是不可能的，您可以将结果插入到临时表中，然后进一步查询

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

或者可以使用OPENQUERY，但这需要设置一个链接服务器，SQL是

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

#3

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

关于SQL Server中存储过程之间共享数据的所有可能方法的最好文章(在我看来)如下:http://www.sommarskog.se/share_data.html

#4

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

#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

#6

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).

由于存储过程不返回结果集，而是使用存储procs的返回功能返回一个整数，因此您无法插入任何表(因为根本没有返回任何结果集)。

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):

但是，如果您只是想要在SSMS中返回查询窗口结果中的值，那么执行INSERT和select是超kill。在我看来，这就足够了(在查询窗口中):

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.

如果这远远不够，请提供“batchno_seq”的DDL，也许我可以提供更好的帮助。

Cheers!

干杯!

#1