CODEWITHSUNDEEP
sql-serverstored-procedures
Kibria
Kibria

Reputation: 141

How to use output parameter with other columns names in SQL Server

I have a simple table with columns - id, name, and salary.

I want to get the name, salary and annual salary by id using a stored procedure.

I thought of creating a simple stored procedure like this:

CREATE PROCEDURE spGetDetails
    @id int,
    @annualSal int out
AS
BEGIN
    SELECT 
        name, salary, 
        @annualSal = (salary * 12) 
    FROM 
        tblPrac 
    WHERE 
        id = @id
END

But I'm getting an error:

A SELECT statement that assigns a value to a variable must not be combined with data-retrieval operations

If this qs is already asked please give me the link and I'll delete this qs. I searched but I think I'm missing the key word. Thanks

Upvotes: 3

Views: 1811

Answers (3)

Raj
Raj

Reputation: 10853

You don't need an OUTPUT parameter. You can simply query like this - 

CREATE PROCEDURE spGetDetails
    @id int
    AS
    BEGIN
    SET NOCOUNT ON
    SELECT Name, Salary, (Salary*12) AS AnnualSalary FROM tblPrac WHERE id = @id
    END

Upvotes: 2

GuidoG
GuidoG

Reputation: 12059

You have stuff mixed so you need to choose between 2 possibilities

CREATE PROCEDURE spGetDetails
  @id int,
  @name varchar(100) out,
  @salary decimal(16,2) out,
  @annualSal decimal(16,2) out
AS
BEGIN
   set nocount on

   SELECT  @name = name, 
           @salary = salary, 
           @annualSal = (salary * 12) 
   FROM    tblPrac 
   WHERE   id = @id
END

or this

CREATE PROCEDURE spGetDetails
  @id int
AS
BEGIN
   set nocount on

   SELECT  name, 
           salary, 
           (salary * 12) as anualSal
   FROM    tblPrac 
   WHERE   id = @id
END

Upvotes: 0

gotqn
gotqn

Reputation: 43666

You need to store the data and separate the operations (as the error message is explaining):

CREATE PROCEDURE spGetDetails
(
    @id int,
    @annualSal int out
)
AS
BEGIN;

    SET NOCOUNT ON;

    DECLARE @DataSource TABLE
    (
        [name] VARCHAR(12)
       ,[salary] DECIMAL(9,2)
    );

    INSERT INTO @DataSource ([name], [salary])
    SELECT  name, salary
    FROM tblPrac 
    WHERE id = @id;

    SELECT [name]
          ,[salary]
    FROM @DataSource;

    SELECT @annualSal = 12 * [salary]
    FROM @DataSource;

    SET NOCOUNT OFF;

    RETURN;
END;

Upvotes: -1

Related Questions