January 29, 2009 at 10:04 am
OK - I've done this lots of times before with no problem. But now I cannot seem to get a stored proc to return an OUT parameter. I just started building an app on a shared SQL 2005 server (with my own db). I SWEAR a stored proc I wrote yesterday worked, but doesn't today. Is there some server-wide setting that could have changed? I can't think of anything, but you never know.
Look at this code:
CREATE PROCEDURE dbo.TestProc
-- Add the parameters for the stored procedure here
@val1 int,
@val2 int OUT
AS
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
-- Insert statements for procedure here
set @val2=@val1+1;
GO
if I call TestProc like this:
declare @result int
exec dbo.TestProc 5,@result
print @result
It should print the value 6, correct? Instead, @result is null. If I include a print statement inside the stored proc, like this:
CREATE PROCEDURE dbo.TestProc
-- Add the parameters for the stored procedure here
@val1 int,
@val2 int OUT
AS
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
-- Insert statements for procedure here
set @val2=@val1+1;
print @val2
GO
and then call it like this:
declare @result int
exec dbo.TestProc 5,@result
print @result
It prints out 6 then nothing.
Maybe it's one of those situations where I've been staring at it too long and the answer is head-bonkingly obvious. Please help!
January 29, 2009 at 10:10 am
Try this:
declare @result int
exec dbo.TestProc 5,@result OUTPUT
print @result
January 29, 2009 at 11:39 am
OH MY FRICKIN' GOD!!!!!!!!
Thanks!
BTW - I just gave myself a bonk on the head for you.
January 29, 2009 at 11:54 am
BOL, my friend it is. Used it, yes, to answer you, else I too would not know. Don't often use that syntax.
January 29, 2009 at 12:24 pm
Well unfortunately that was just the top layer of the problem. My real problem is that my call to the stored proc from PHP isn't returning the output parameter. I'll look for a different forum to post that.
January 29, 2009 at 12:39 pm
Aw, PHP. Yes another forum would be good, web programmer I am not.
Viewing 6 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply