how to excute a Sp with dynamic parameter

Question

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 ?

CAn you please provide the formatted the Codes? – A Nice Guy Jan 22 '14 at 09:13 — A Nice Guy, Jan 22 '14 at 09:13

Suraj Shrestha · Answer 1 · 2014-01-22T09:23:04.163

1

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

edited Jan 22 '14 at 09:23

answered Jan 22 '14 at 09:17

Suraj Shrestha

1,790
1
25
51

score 0 · Accepted Answer · answered Jan 22 '14 at 10:47

0

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

answered Jan 22 '14 at 10:47

Anto Raja Prakash

1,328
8
12

Thank u The Sp u gave is correct but the problem is its displaying only a single row while there are 6 rows with income 500 please suggest any solution – ashu Jan 22 '14 at 13:24
@ashu you should change your SP to insert into a table `insert into SELECT FirstName, Title FROM Salaries WHERE @Income=Income ` after the SP Execute get the data from the table.... – Anto Raja Prakash Jan 23 '14 at 09:11

how to excute a Sp with dynamic parameter

2 Answers2