如何从 SQL Server 中的存储过程进行查询？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/1676655/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
How to query from a stored procedure in SQL Server?
提问by Patrick Desjardins
Let say I have a simple Stored Procedure:
假设我有一个简单的存储过程：
ALTER PROCEDURE [dbo].[myProc]
AS
BEGIN
SELECT * FROM myTable
END
How can I do a WHERE statement in Microsoft SQL Server Management Studio to the stored procedure? Something like that:
如何在 Microsoft SQL Server Management Studio 中对存储过程执行 WHERE 语句？类似的东西：
SELECT * FROM myProc WHERE x = 'a'; -- But that doesn't work...
回答by Hythloth
It sounds like you're trying to make a "dynamic" stored procedure.
听起来您正在尝试创建一个“动态”存储过程。
Something you might want to do is:
您可能想要做的事情是：
1) Insert the contents of your stored procedure into a temporary table
1) 将存储过程的内容插入到临时表中
2) Use dynamic sql to apply a where condition to that temporary table.
2) 使用动态 sql 将 where 条件应用于该临时表。
Something like:
就像是：
declare @as_condition varchar(500); --Your condition
create table #a
(
id bigint
)
insert into #a
execute sproc
declare @ls_sql varchar(max);
set @ls_sql = "select * from #a where " + @as_condition;
execute (@ls_sql);
回答by Andomar
SQL Server allows you to use INSERT INTO to grab a stored procedure's output. For example, to grab all processes with SPID < 10, use:
SQL Server 允许您使用 INSERT INTO 来获取存储过程的输出。例如，要获取 SPID < 10 的所有进程，请使用：
create table #sp_who (
spid smallint,
ecid smallint,
status nchar(30),
loginame nchar(128),
hostname nchar(128),
blk char(5),
dbname nchar(128),
cmd nchar(16),
request int)
insert into #sp_who execute sp_who
select * from #sp_who where spid < 10
回答by Uzair Xlade
You must declare a variable in the store procedure which will be necessary to pass to run the stored procedure. Here is an example. Keep this in mind: Before ASyou can simply declare any variable by using the @character, but after the ASyou must write Declareto declare any variable, e.g., Declare @name nvarchar (50).
您必须在存储过程中声明一个变量，该变量是运行存储过程所必需的。这是一个例子。请记住这一点：在AS您可以使用@字符简单地声明任何变量之前，但在之后AS您必须编写Declare以声明任何变量，例如，Declare @name nvarchar (50)。
ALTER PROCEDURE [dbo].[myProc]
@name varchar (50)
AS
BEGIN
SELECT * FROM myTable
where name= @name
END
回答by PBo
I think its better to use a view or a table valued function rather than the suggested approach. Both allow you to pass parameters to the function
我认为最好使用视图或表值函数而不是建议的方法。两者都允许您将参数传递给函数
回答by AdaTheDev
You can't add a WHERE clause to a stored procedure like this.
您不能像这样向存储过程添加 WHERE 子句。
You should put the clause in the sproc, like this:
您应该将子句放在 sproc 中，如下所示：
ALTER PROCEDURE [dbo].[myProc]
@X VARCHAR(10)
AS
BEGIN
SELECT * FROM myTable WHERE x=@X
END
GO
The syntax for calling a stored procedure is through the use of EXECUTEnot SELECT(e.g.):
调用存储过程的语法是通过使用EXECUTE而不是 SELECT（例如）：
EXECUTE dbo.myProc 'a'
回答by rossoft
I think you can't do that.
我认为你不能那样做。
The command to execute a stored procedure is EXECUTE.
执行存储过程的命令是EXECUTE.
See some more examplesof the EXECUTEusage.
见更多的一些例子中的EXECUTE用法。
回答by davek
If you want the WHERE clause to be something you can "turn off" you can do this, passing in a predetermined value (e.g. -1) if the WHERE limitation is to be bypassed:
如果您希望 WHERE 子句成为您可以“关闭”的东西，您可以这样做，如果要绕过 WHERE 限制，则传入一个预定值（例如 -1）：
ALTER PROCEDURE [dbo].[myProc]
@X VARCHAR(10)
AS
BEGIN
SELECT * FROM myTable WHERE x=@X or @X = -1
END
GO
