How to execute a query from a stored procedure in SQL Server?

Question

Say I have a simple stored procedure:

ALTER PROCEDURE [dbo].[myProc]
AS
BEGIN
   SELECT * FROM myTable
END

How can I execute a WHERE statement in Microsoft SQL Server Management Studio in a stored procedure? Something like that:

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

Answer 1

It looks like you are trying to create a “dynamic” stored procedure.

Something you can do is:

1) Paste the contents of the stored procedure into the temporary table

2) Use dynamic sql to apply the where clause to this temporary table.

Sort of:

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 2

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

Answer 3

WHERE , .

sproc, :

ALTER PROCEDURE [dbo].[myProc]
    @X VARCHAR(10)
AS
BEGIN
SELECT * FROM myTable WHERE x=@X
END
GO

EXECUTE SELECT (.):

EXECUTE dbo.myProc 'a'

Answer 4

, .

EXECUTE.

EXECUTE.

Answer 5

, . . : AS @, AS Declare , , Declare @name nvarchar (50).

ALTER PROCEDURE [dbo].[myProc]

@name varchar (50)

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

Answer 6

, , .

Answer 7

, WHERE -, "", , (, -1), WHERE :

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

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

GO