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

Question

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

The error message clearly stating that, you can't do Assign & Selecting in a single statement. — Shakeer Mirza
– Shakeer Mirza, Commented Sep 20, 2017 at 7:51
As the error says, you can't combine the two operations, but in this case it seems like pretty superfluous use of an output paramater. Since whatever is calling the procedure is most likely reading the first two columns, why not just use a third column "AnnualSalary", and have whatever is calling it obtain the annual salary through this rather than an output parameter? — GarethD
– GarethD, Commented Sep 20, 2017 at 7:52
@GarethD is right. No need for the @annualSal int out paramter — Abdul Samad
– Abdul Samad, Commented Sep 20, 2017 at 8:02
Why are you trying to use an output parameter for one of the values and not for the other two (Name, Salary)? You need to either use 3 output parameters, or 3 select columns, you can't mix and match. This is why you are getting an error. — GarethD
– GarethD, Commented Sep 20, 2017 at 8:25

Raj · Accepted Answer · 2017-09-20 09:17:00Z

2

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

edited Sep 20, 2017 at 9:17

answered Sep 20, 2017 at 7:53

Raj

10.9k2 gold badges47 silver badges53 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

Kibria Over a year ago

Actually, I was trying to use the output parameter, I know using the alias can give me the same result.

Raj Over a year ago

If you really need to use an OUTPUT parameter, then you will need to get the value for it in an independent query. You cannot select some other columns as well as populate that parameter in the same query

GuidoG Over a year ago

only thing that is missing here is the 'set nocount on' statement

Raj Over a year ago

@GuidoG - Added. Thanks

GuidoG · Accepted Answer · 2017-09-20 08:35:27Z

0

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

answered Sep 20, 2017 at 8:35

GuidoG

12.3k6 gold badges59 silver badges102 bronze badges

1 Comment

Kibria Over a year ago

Yes I have to either select all as columns or use output parameter for all columns.

gotqn · Accepted Answer · 2017-09-20 08:02:31Z

-1

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;

answered Sep 20, 2017 at 8:02

gotqn

44k47 gold badges167 silver badges257 bronze badges

Collectives™ on Stack Overflow

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

3 Answers 3

4 Comments

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

4 Comments

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related