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, May 21 '19 at 19:58
Your @@ROWCOUNT is getting reset to 0. Change it to an else block... then call it correctly. [See the demo here](https://dbfiddle.uk/?rdbms=sqlserver_2017&fiddle=8f8752e30ee25e35164e0e20ce26f2e1). @@ROWCOUNT can be tricky. [See this blog](https://blog.sqlauthority.com/2011/12/02/sql-server-effect-of-set-no-count-on-rowcount/) on one instance. Also, using `PRINT` will reset it to 0. — S3S, May 21 '19 at 20:06
i did try ths as well but why do I keep getting the same error `Must declare the scalar variable "@intValue"` even if it is declared ? @scsimon — Diksha, May 21 '19 at 20:17
That is not reproducible with the code you posted. This can be seen in the demo i posted. We can only assume you have some other code above or below this snippet on your system. — S3S, May 21 '19 at 20:19
@scsimon can you try the abobe code in SQLfiddle I provided in my question? I'll post the schema for that. (Just trying to know if the IDE has some issue...) — Diksha, May 21 '19 at 20:29
It works @Diksha... [See the demo here](https://dbfiddle.uk/?rdbms=sqlserver_2017&fiddle=ed73ac0de49a30192b94947030291ffc) I just changed the table name in the procedure. Note, this is with the edits i already suggested in my previous comments. — S3S, May 21 '19 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, May 21 '19 at 20:55
Okay so return value is just for status task and if we wanna see the output on screen we use output parameter. thanks for clearing this @SeanLange — Diksha, May 21 '19 at 21:00

Sean Lange · Answer 1 · 2019-05-21T20:36:02.570

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 '19 at 20:36

answered May 21 '19 at 20:06

Sean Lange

33,028
3
25
40

Yeah this run but did not return any result @Sean Lange – Diksha May 21 '19 at 20:16
Please see my update. I added example code for calling this. – Sean Lange May 21 '19 at 20:17
@Sean Lange The result is not important. I can not figure out how the error " "Must declare the scalar variable "@intValue"" is raised? – May 21 '19 at 20:17
2

There is zero chance you got that error using the code I just posted. That variable name is not in the code. – Sean Lange May 21 '19 at 20:18
@Sean Lange Agree with you 100%. – May 21 '19 at 20:19
@SeanLange I did call the procedure the same way you suggested. – Diksha May 21 '19 at 20:19
1

Did you change the procedure to the code I posted also? – Sean Lange May 21 '19 at 20:20
1

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. – Sean Lange May 21 '19 at 20:24
my bad @SeanLange, no result returned meant `Record Count: 0; Execution Time: 14ms` – Diksha May 21 '19 at 20:40
See my latest update. Using your posted sample data this works just fine if there is one or more rows returned. It will return NULL if there are no rows found which is consistent with your original code, albeit possibly not what you want. – Sean Lange May 21 '19 at 20:42

score 1 · Answer 2 · answered May 21 '19 at 19:54

1

Try this one

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

answered May 21 '19 at 19:54

Tried still getting the same error: `Must declare the scalar variable "@intValue".` – Diksha May 21 '19 at 19:58
Try removing extra "=" sign on the line. So, this is the correct way IF @@rowcount = 1 – May 21 '19 at 20:01
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 – May 21 '19 at 20:06
@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. – S3S May 21 '19 at 20:12
@scsimon How the error "Must declare the scalar variable "@intValue"" is raised? – May 21 '19 at 20:15
No idea... not from the code the you posted. You should have gotten an *incorrect syntax near =* error. Once you removed that as you suggested and I did in my comments, then it would run without error but not return the intended results **even** when the user calls it correctly as you described in your post. [See this here](https://dbfiddle.uk/?rdbms=sqlserver_2017&fiddle=461d712952edddca9baa63ea7d82b003). However, fixing the issue inside the proc as i explained in my comments will fix it.[See that here](https://dbfiddle.uk/?rdbms=sqlserver_2017&fiddle=8f8752e30ee25e35164e0e20ce26f2e1) – S3S May 21 '19 at 20:17

score 1 · Answer 3 · answered May 21 '19 at 21:29

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

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

3 Answers3