February 5, 2016 at 8:05 am
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?
February 5, 2016 at 8:13 am
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
February 5, 2016 at 8:19 am
This part doesn't work
IF @Results IS NOT NULL
PRINT 'Hello'
ELSE
PRINT 'OK thanks'
February 5, 2016 at 8:30 am
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'
February 5, 2016 at 8:32 am
TJT (2/5/2016)
This part doesn't workIF @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. 😉
February 5, 2016 at 8:33 am
OK, but even when I try this it doesn't work
IF @Results > 10
PRINT 'Hello'
ELSE
PRINT 'OK thanks'
February 5, 2016 at 8:36 am
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;
February 5, 2016 at 8:37 am
The results of EXEC spMyStoredProc are:
387961
387962
Just two numbers
February 5, 2016 at 8:40 am
TJT (2/5/2016)
OK, but even when I try this it doesn't workIF @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
February 5, 2016 at 8:42 am
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'
February 5, 2016 at 8:42 am
@Results returns
387961
387962
February 5, 2016 at 8:46 am
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.
February 5, 2016 at 8:50 am
TJT (2/5/2016)
@Results returns387961
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
February 5, 2016 at 9:04 am
@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
February 5, 2016 at 9:07 am
To prove what Drew has just said,
can you just run
EXEC spMyStoredProc
and see what the result set is
Viewing 15 posts - 1 through 15 (of 18 total)
You must be logged in to reply to this topic. Login to reply