How to call a SQL Server procedure with both input and output parameters?

Question

I'm trying to write a procedure which returns values on the basis of a select query result. Like find the ID of a user, use first name and last name to locate a user ID, and return the ID as an OUTPUT parameter. If more than one or no user is found, set a return value of -1, and set the OUTPUT parameter to 0.

I've tried the following code but I'm getting an error:

Must declare the scalar variable "@intValue"

I've search over the internet but none solved my problem.

CREATE PROCEDURE GetIdOfUser
    (@f_name VARCHAR(50), 
     @l_name VARCHAR(50),
     @outValue INT OUTPUT)
AS
    DECLARE @intValue INT;

    SELECT @intValue = userID 
    FROM users
    WHERE firstName = @f_name AND lastName = @l_name

    IF @@rowcount > 1 -- more than one user?
    BEGIN
        SET @outValue = 0
        RETURN -1;
    END

    IF @@rowcount == 1
    BEGIN
        SET @outValue = @intValue;
    END



DECLARE @myretValue INT;

EXEC GetIdOfUser 'raj', 'ahuja', @myretValue;

SELECT @myretValue;

I am using SQLfiddle[sqlfiddle.com] for learning SQL Server concepts. I'm quite new to creating procedures in SQL Server so any help would be appreciated.

EDIT 1: SQL Server Version is 2017

EDIT 2: Schema and sample data that I'm working with.

create table users
(
    userID int,
    firstName varchar(50),
    lastName varchar(50),
    title varchar(50)
);

insert into users(userID, firstName, lastName, title) 
values(501, 'natasha', 'ghosh', 'title1'),
      (502, 'raj', 'ahuja', 'title2'),
      (503, 'katy', 'perry', 'title3'),
      (504, 'john', 'cyrus', 'title4'),
      (505, 'anindita', 'pal', 'title5');

IF @@rowcount == 1 should be IF @@rowcount = 1. Not sure why you got the error you got. — S3S
– S3S, Commented May 21, 2019 at 19:58
Your @@ROWCOUNT is getting reset to 0. Change it to an else block... then call it correctly. See the demo here. @@ROWCOUNT can be tricky. See this blog on one instance. Also, using PRINT will reset it to 0. — S3S
– S3S, Commented May 21, 2019 at 20:06
It works @Diksha... See the demo here I just changed the table name in the procedure. Note, this is with the edits i already suggested in my previous comments. — S3S
– S3S, Commented May 21, 2019 at 20:47
Oh I bet I know what the problem here is. @Diksha the return statement is a very different animal than an output variable. I have a feeling you are expecting to see the return value in your output variable. That isn't how procedures work. It has a return value which should be used for the status of the execution, not to return data. Then you have output parameters which are completely different. You can have many of them and they can be any datatype. The return value is always an int. — Sean Lange
– Sean Lange, Commented May 21, 2019 at 20:55

Sean Lange · Accepted Answer · 2019-05-21 20:36:02Z

3

I would simplify this a little bit. No need to populate multiple variables here. See if something a little simpler like this works.

CREATE PROCEDURE GetIdOfUser
(
    @f_name VARCHAR(50), 
    @l_name VARCHAR(50),
    @outValue INT OUTPUT
)
AS
    SELECT @outValue = userID 
    FROM users
    WHERE firstName = @f_name 
        AND lastName = @l_name

    if @@rowcount > 1
        set @outValue = -1

GO

Here is how you would use this.

DECLARE @myretValue INT;

EXEC GetIdOfUser 'raj', 'ahuja', @myretValue OUTPUT;

SELECT @myretValue;

--EDIT--

With your new sample data I decided to use the table name specialist since you posted two different names. The procedure I posted here works perfectly.

create table specialist(
userID int,
firstName varchar(50),
lastName varchar(50),
title varchar(50)
);
insert into specialist(userID, firstName, lastName, title) values(501, 'natasha', 'ghosh', 'title1');
insert into specialist(userID, firstName, lastName, title) values(502, 'raj', 'ahuja', 'title2');
insert into specialist(userID, firstName, lastName, title) values(503, 'katy', 'perry', 'title3');
insert into specialist(userID, firstName, lastName, title) values(504, 'john', 'cyrus', 'title4');
insert into specialist(userID, firstName, lastName, title) values(505, 'anindita', 'pal', 'title5');

GO

CREATE PROCEDURE GetIdOfUser
(
    @f_name VARCHAR(50), 
    @l_name VARCHAR(50),
    @outValue INT OUTPUT
)
AS
    SELECT @outValue = userID 
    FROM specialist
    WHERE firstName = @f_name 
        AND lastName = @l_name

    if @@rowcount > 1
        set @outValue = -1

GO

DECLARE @myretValue INT;

EXEC GetIdOfUser 'raj', 'ahuja', @myretValue OUTPUT;

SELECT @myretValue;

edited May 21, 2019 at 20:36

answered May 21, 2019 at 20:06

Sean Lange

33.7k3 gold badges28 silver badges42 bronze badges

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

10 Comments

Diksha Over a year ago

Yeah this run but did not return any result @Sean Lange

Sean Lange Over a year ago

Please see my update. I added example code for calling this.

Sean Lange Over a year ago

There is zero chance you got that error using the code I just posted. That variable name is not in the code.

Sean Lange Over a year ago

Did you change the procedure to the code I posted also?

Sean Lange Over a year ago

You got no value in the output variable? This just isn't that difficult but you are not providing information here. You need to state what happens, not "I got an error" or "it did not return result". This procedure is painfully simple and it will either return a value or throw an exception. There just isn't any middle ground here.

|

user11380812 · Accepted Answer · 2019-05-21 19:54:55Z

1

Try this one

DECLARE @myretValue int
EXEC GetIdOfUser 'raj', 'ahuja', @myretValue output
select @myretValue;

answered May 21, 2019 at 19:54

user11380812

6 Comments

Diksha Over a year ago

Tried still getting the same error: Must declare the scalar variable "@intValue".

user11380812 Over a year ago

Try removing extra "=" sign on the line. So, this is the correct way IF @@rowcount = 1

user11380812 Over a year ago

I created the table [Users] as well as your stored procedure. The procedure executes without any errors. Do you perhaps know which SQL Server version do you use? I tested on SQL Server 2008 - 2012

S3S Over a year ago

@DarkoMartinovic @@ROWCOUNT is getting reset after the first IF block. That's why the second condition is always false. It will execute without error, but won't return the correct results.

user11380812 Over a year ago

@scsimon How the error "Must declare the scalar variable "@intValue"" is raised?

|

Kanwal Sarwara · Accepted Answer · 2019-05-21 21:29:29Z

You have multiple issues, I would try to get it working with minimal changes.

CREATE PROCEDURE GetIdOfUser
    (@f_name VARCHAR(50), 
     @l_name VARCHAR(50),
     @outValue INT OUTPUT)
AS
    DECLARE @intValue INT

By default sql fiddle uses ; to end the batch, change that to GO or remove ;

    SELECT @intValue = userID 
    FROM users
    WHERE firstName = @f_name AND lastName = @l_name

    SET @outValue = CASE WHEN @@rowcount = 1 THEN @intValue ELSE 0 END

Simplified this with case statement, you do not need multiple if statements here. Also your original check was for greather than and equal to, not less than (when no rows are found) and would have returned 0 in that case. This handles all cases.

GO

End the batch or the code below also becomes part of procedure. I believe putting all the procedure logic inside BEGIN ... END would also work.

DECLARE @myretValue INT

EXEC GetIdOfUser 'raj', 'ahuja', @myretValue OUTPUT

In procedure call you need to define the output variable as OUTPUT for it to actually work, or sql would not set value of the variable.

SELECT @myretValue

Using above code it all works well and I get the response 502, fiddle link - http://sqlfiddle.com/#!18/ddb50/47

Collectives™ on Stack Overflow

How to call a SQL Server procedure with both input and output parameters?

3 Answers 3

10 Comments

6 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

10 Comments

6 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related