CODEWITHSUNDEEP
sqlsql-server
Patrick Desjardins
Patrick Desjardins

Reputation: 141013

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

Upvotes: 3

Views: 51412

Answers (7)

PBo
PBo

Reputation: 519

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

Upvotes: 1

Syed Uzair Uddin
Syed Uzair Uddin

Reputation: 3658

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

Upvotes: 0

rossoft
rossoft

Reputation: 2182

I think you can't do that.

The command to execute a stored procedure is EXECUTE.

See some more examples of the EXECUTE usage.

Upvotes: 1

Andomar
Andomar

Reputation: 238296

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

Upvotes: 3

davek
davek

Reputation: 22925

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

Upvotes: 0

Hythloth
Hythloth

Reputation: 774

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

Upvotes: 8

AdaTheDev
AdaTheDev

Reputation: 147374

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'

Upvotes: 1

Related Questions