CODEWITHSUNDEEP
sqlstored-proceduresdynamicexecutedeclare
ashu
ashu

Reputation: 507

how to excute a Sp with dynamic parameter

I have created a stored procedure to find name and designation by supplying income of an employee

Create  PROCEDURE GetEmployeessalaryInOutputVariable
(
    @Income INT,                 -- Input parameter,  Income of the Employee
    @FirstName VARCHAR (30) OUT, -- Output parameter to collect the Employee name
    @Title VARCHAR (30)OUT       -- Output Parameter to collect the Employee designation
)
AS
BEGIN
SELECT FirstName=@FirstName, Title=@Title 
    FROM Salaries WHERE @Income=Income
END

After this I tried to execute the Sp as follows

Declare @FirstName as varchar(30) -- Declaring the variable to collect the Employeename
Declare @Title as varchar(30)     -- Declaring the variable to collect the Designation
Execute GetEmployeessalaryInOutputVariable 500 , @FirstName output, @Title output
select @FirstName,@Title as Designation

As soon as I write the above statement, it displays an error displaying

Invalid object name GetEmployeessalaryInOutputVariable

Why is it behaving like that although the procedure has been created and exists?

Also, how can I run the query to get proper results ?

Upvotes: 0

Views: 85

Answers (2)

Anto Raja Prakash
Anto Raja Prakash

Reputation: 1388

Create  PROCEDURE GetEmployeessalaryInOutputVariable
(
    @Income INT,                 -- Input parameter,  Income of the Employee
    @FirstName VARCHAR (30) OUT, -- Output parameter to collect the Employee name
    @Title VARCHAR (30)OUT       -- Output Parameter to collect the Employee designation
)
AS
BEGIN
SELECT @FirstName = FirstName, @Title=Title 
    FROM Salaries WHERE @Income=Income
END

Your SP should be like this

Upvotes: 0

Suraj Shrestha
Suraj Shrestha

Reputation: 1808

Execute GetEmployeessalaryInOutputVariable 500 , 'FirstName', 'Title'

OR

Declare @FirstName as varchar(30)
set @FirstName = 'FirstName'
Declare @Title as varchar(30)   
set @Title = 'title'
Execute GetEmployeessalaryInOutputVariable 500 , @FirstName, @Title

Upvotes: 1

Related Questions