How to query from a stored procedure in SQL Server?

Question

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

Answer 1

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'

Answer 2

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

Answer 3

I think you can't do that.

First, the command to execute a stored procedure is EXECUTE. Here it is the reference of it:

http://msdn.microsoft.com/en-us/library/ms188332.aspx

See some more examples of the EXECUTE usage here:

http://www.sqlteam.com/article/stored-procedures-returning-data

Answer 4

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

Answer 5

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