How to query from a stored procedure in SQL Server?

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:

SELECT * FROM myProc WHERE x = 'a'; -- But that doesn't work...

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

2) Use dynamic sql to apply a where condition to that temporary table.

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

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:

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

You can't add a WHERE clause to a stored procedure like this.

You should put the clause in the sproc, like this:

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 EXECUTE not SELECT(e.g.):

EXECUTE dbo.myProc 'a'

I think you can't do that.

The command to execute a stored procedure is EXECUTE.

See some more examples of the EXECUTE usage.

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

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:

ALTER PROCEDURE [dbo].[myProc]    
@X VARCHAR(10)
AS

BEGIN
    SELECT * FROM myTable WHERE x=@X or @X = -1
END

GO

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 AS you can simply declare any variable by using the @ character, but after the AS you must write Declare to declare any variable, e.g., Declare @name nvarchar (50).

ALTER PROCEDURE [dbo].[myProc]

@name varchar (50)

AS
BEGIN
    SELECT * FROM myTable
    where name= @name
END