CODEWITHSUNDEEP
sql-serverstored-procedureswhere-clause
user6025438
user6025438

Reputation:

How to use Where Clause in Exec Stored Procedure

I'm having a Stored Procedure

Table Structure:

CREATE TABLE employee(
    id          INTEGER NOT NULL PRIMARY KEY,
    first_name  VARCHAR(10),
    last_name   VARCHAR(10),
    salary      DECIMAL(10,2),
    city        VARCHAR(20),   
)

Adding Some Data to the employee Table:

INSERT INTO employee VALUES (1, 'Karvendhan',  'Palani',4789,'Agra');
GO

INSERT INTO employee VALUES (2, 'Bala',  'Manigandan',8789,'Bombay');
GO

INSERT INTO employee VALUES (3, 'Raj' ,  'Kishore',   5567,'London');
GO

INSERT INTO employee VALUES (4, 'Prayag',  'Pavithran',  4467,'Bombay');
GO

INSERT INTO employee VALUES (5, 'Selva',  'Kumar',  3456, 'Bombay');
GO

select * from employee;

My Stored Procedure:

CREATE PROCEDURE GetEmployeeBasedOnCity
(
    @city VARCHAR(20)
)

AS
BEGIN
    select * from employee where city = @city
END;

I'm Executing the Stored Procedure: exec GetEmployeeBasedOnCity 'Bombay';

I'm getting the Output:

id      first_name       last_name        salary          city
-----------------------------------------------------------------
2       Bala             Manigandan       8789.00        Bombay
4       Prayag           Pavithran        4467.00        Bombay
5       Selva            Kumar            3456.00        Bombay

I need to put where clause at the time of exec GetEmployeeBasedOnCity 'Bombay'; not inside the Stored Procedure. Kindly assist me...

Imagine the Query (Rough Expectation)

exec GetEmployeeBasedOnCity 'Bombay' WHERE salary >5000

Upvotes: 8

Views: 35874

Answers (1)

Dan Guzman
Dan Guzman

Reputation: 46203

Your best approach would be to execute the needed query independently of the stored procedure. But if you must use the existing stored procedure without changes, you could insert the proc results into a temporary table and then apply additional filters on that table:

CREATE TABLE #employee(
    id          INTEGER NOT NULL PRIMARY KEY,
    first_name  VARCHAR(10),
    last_name   VARCHAR(10),
    salary      DECIMAL(10,2),
    city        VARCHAR(20),   
);

INSERT INTO #employee
    EXEC GetEmployeeBasedOnCity 'Bombay';

SELECT *
FROM #employee
WHERE salary > 5000;

Upvotes: 12

Related Questions