IF or WHILE statements with Stored Procedures

Question

IF or WHILE statements with Stored Procedures

TJT

SSCommitted

Points: 1926
More actions
February 5, 2016 at 8:05 am

#307697

I am looking for some help with IF or WHILE statements
EXAMPLE:
DECALRE @Results INT
EXEC @Results = spMyStoredProc
IF @Results IS NOT NULL
PRINT 'Hello'
ELSE
PRINT 'OK thanks'
My question is can you use statements "IF @Results IS NOT NULL" and if you can, what am I doing wrong?
Or must I use a temp table?

Viewing 15 posts - 1 through 15 (of 18 total)

You must be logged in to reply to this topic. Login to reply

John Mitchell-245523 SSC Guru Points: 148809 More actions · Answer 1

TJT (2/5/2016)
what am I doing wrong?

Nothing, as far as I can tell, except spelling DECLARE wrongly. Now, what exactly is it that doesn't work as you expect? Don't forget we can't see your screen.

John

TJT SSCommitted Points: 1926 More actions · Answer 2

This part doesn't work

IF @Results IS NOT NULL

PRINT 'Hello'

ELSE

PRINT 'OK thanks'

Simon Hundleby SSCarpal Tunnel Points: 4584 More actions · Answer 3

A stored Procedure will never return NULL

from msdn

[When used with a stored procedure, RETURN cannot return a null value. If a procedure tries to return a null value (for example, using RETURN @status when @status is NULL), a warning message is generated and a value of 0 is returned.

.

Therefore your code will always print 'Hello'

Ed Wagner SSC Guru Points: 287026 More actions · Answer 4

TJT (2/5/2016)
This part doesn't work
IF @Results IS NOT NULL
PRINT 'Hello'
ELSE
PRINT 'OK thanks'

What's the value of your @Results variable? I'll be it's an integer.

Tally Tables - Performance Personified
String Splitting with True Performance
Best practices on how to ask questions

TJT SSCommitted Points: 1926 More actions · Answer 5

OK, but even when I try this it doesn't work

IF @Results > 10

PRINT 'Hello'

ELSE

PRINT 'OK thanks'

Luis Cazares SSC Guru Points: 183706 More actions · Answer 6

This is basically your code, I just corrected the variable declaration and added the stored procedure DDL. It works as it is.

If it doesn't work, make sure that you're using SQL Server and not a different RDBMS.

CREATE PROCEDURE MyStoredProc

AS

RETURN NULL;

GO

DECLARE @Results INT;

EXEC @Results = MyStoredProc;

IF @Results IS NOT NULL

PRINT 'Hello';

ELSE

PRINT 'OK thanks';

GO

DROP PROCEDURE MyStoredProc;

Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

How to post data/code on a forum to get the best help: Option 1 / Option 2

TJT SSCommitted Points: 1926 More actions · Answer 7

The results of EXEC spMyStoredProc are:

387961

387962

Just two numbers

drew.allen SSC Guru Points: 77012 More actions · Answer 8

TJT (2/5/2016)
OK, but even when I try this it doesn't work
IF @Results > 10
PRINT 'Hello'
ELSE
PRINT 'OK thanks'

You need to be more specific. We need to know what the value of @Results is, what you expect to be printed, and what is actually being printed.

Drew

J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA

Simon Hundleby SSCarpal Tunnel Points: 4584 More actions · Answer 9

Can You Tell me what the value of @Results is

by selecting it

DECALRE @Results INT
EXEC @Results = spMyStoredProc
SELECT @Results
IF @Results IS NOT NULL
PRINT 'Hello'
ELSE
PRINT 'OK thanks'

TJT SSCommitted Points: 1926 More actions · Answer 10

TJT

SSCommitted

Points: 1926

February 5, 2016 at 8:42 am

#1856891

@Results returns

387961

387962

Jacob Wilkins One Orange Chip Points: 27976 More actions · Answer 11

If you're not using RETURN to specify a return value for the stored procedure, when you successfully run EXEC @variable=stored_proc, you'll just get a 0 assigned to the variable.

You'll want to look at using OUTPUT parameters for this. https://technet.microsoft.com/en-us/library/ms187004(v=sql.105).aspx

Cheers!

EDIT: It also looks like your stored procedure is returning multiple rows. If you're just trying to assign some part of the result set to a variable, be careful to make sure you assign the single value you want.

If you need to keep all the results, look at using INSERT INTO...EXEC with a temporary table.

drew.allen SSC Guru Points: 77012 More actions · Answer 12

TJT (2/5/2016)
@Results returns
387961
387962

You're confusing the RETURN status with the RESULT SET. Since @Results is an integer, it CANNOT contain two separate numbers. The RETURN status can be specifically set in a procedure using the RETURN keyword. https://msdn.microsoft.com/en-us/library/ms174998.aspx

Drew

J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA

Simon Hundleby SSCarpal Tunnel Points: 4584 More actions · Answer 13

@Results returns
387961
387962

No it doesn't,

Can you run the select @Results as I suggested and tell us the value.

I suspect it will be 0

Simon Hundleby SSCarpal Tunnel Points: 4584 More actions · Answer 14

To prove what Drew has just said,

can you just run

EXEC spMyStoredProc

and see what the result set is

IF or WHILE statements with Stored Procedures

Cookies on SQLServerCentral