May 16, 2011 at 9:51 am
Hey guys and gals . . .
I've been messing with this code all morning, and haven't been able to get it to work properly.
Here's the scoop: I'm writing a simple SP that performs an update. It looks something like this (code changed to protect the innocent):
ALTER PROCEDURE [ProcName]
@IDvarchar(255),
@Emailvarchar(255),
@Resultint output
AS
BEGIN
SET NOCOUNT ON;
declare @sql nvarchar(1024)
declare @PList nvarchar(512)
set @PList = N'@GetID varchar(255), @GetEmail varchar(255), @GetRows int output'
set @sql = N'update [SomeTable] set VerifyMe = 1 where ID = @GetID and Email = @GetEmail; set @GetRows = @@rowcount;'
exec sp_executesql @sql, @PList,
@GetEmail = @Email,
@GetID = @ID,
@GetRows = @result output
END
The UPDATE works fine, but here's where I run into trouble: I also want the SP to return the number of rows affected by the UPDATE (specifically, I'm trying to check to see if number of rows = zero -- BTW, ID is of type uniqueidentifer). I thought it'd be an easy implementation, but I've tried a number of different things from BOL, and can't seem to get it to work.
Any thoughts from the peanut gallery?
+--------------------------------------------------------------------------------------+
Check out my blog at https://pianorayk.wordpress.com/
May 16, 2011 at 10:00 am
Errr... Works for me as you have it. (I removed the rest of the variables & parameters for simplicity)
DECLARE @Result int
declare @sql nvarchar(1024)
declare @PList nvarchar(512)
set @PList = N'@GetRows int output'
set @sql = N'select * from sys.objects; set @GetRows = @@rowcount;'
exec sp_executesql @sql, @PList,
@GetRows = @result OUTPUT;
SELECT @Result AS Rowsaffected
What does it not do for you?
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
May 16, 2011 at 10:09 am
Ray K (5/16/2011)
Hey guys and gals . . .I've been messing with this code all morning, and haven't been able to get it to work properly.
Here's the scoop: I'm writing a simple SP that performs an update. It looks something like this (code changed to protect the innocent):
ALTER PROCEDURE [ProcName]
@IDvarchar(255),
@Emailvarchar(255),
@Resultint output
AS
BEGIN
SET NOCOUNT ON;
declare @sql nvarchar(1024)
declare @PList nvarchar(512)
set @PList = N'@GetID varchar(255), @GetEmail varchar(255), @GetRows int output'
set @sql = N'update [SomeTable] set VerifyMe = 1 where ID = @GetID and Email = @GetEmail; set @GetRows = @@rowcount;'
exec sp_executesql @sql, @PList,
@GetEmail = @Email,
@GetID = @ID,
@GetRows = @result output
END
The UPDATE works fine, but here's where I run into trouble: I also want the SP to return the number of rows affected by the UPDATE (specifically, I'm trying to check to see if number of rows = zero -- BTW, ID is of type uniqueidentifer). I thought it'd be an easy implementation, but I've tried a number of different things from BOL, and can't seem to get it to work.
Any thoughts from the peanut gallery?
My first thought is that there's a problem with the implicit conversion going on between the uniqueidentifier and the varchar(255). Especially since a simplified version worked for Gail. I'd say check the parameters you're passing, and try using uniqueidentifier instead of varchar(255)
--------------------------------------
When you encounter a problem, if the solution isn't readily evident go back to the start and check your assumptions.
--------------------------------------
It’s unpleasantly like being drunk.
What’s so unpleasant about being drunk?
You ask a glass of water. -- Douglas Adams
May 16, 2011 at 10:42 am
You might want to check the difference between your actual code and your posted code.
I've done that before. Changed my code to post on the forums and, in the process, inadvertently solved my problem. Maybe that's why your code works for Gail and isn't working for you. @=)
May 16, 2011 at 10:46 am
Actually, the ID/uniqueidentifier check is working fine.
What I'm having trouble with is how to return the ROWCOUNT result. (I don't usually -- in fact, practically never -- have to return a result from an UPDATE, which is why I'm having trouble with this; I've never had to deal with this.) I'm not sure how to handle the results of the OUTPUT parameter when I call the SP. When I tried calling the SP, I got the 'Must declare the scalar variable "@Result".' message.
+--------------------------------------------------------------------------------------+
Check out my blog at https://pianorayk.wordpress.com/
May 16, 2011 at 10:48 am
Brandie Tarvin (5/16/2011)
You might want to check the difference between your actual code and your posted code.I've done that before. Changed my code to post on the forums and, in the process, inadvertently solved my problem. Maybe that's why your code works for Gail and isn't working for you. @=)
Sorry Brandie -- was in mid-type when you replied!
I know what you're talking about, but don't think that's the case here. I'm thinking the problem is in how I'm calling my SP. (Probably should've mentioned that before, huh? :ermm:)
+--------------------------------------------------------------------------------------+
Check out my blog at https://pianorayk.wordpress.com/
May 16, 2011 at 10:50 am
Brandie Tarvin (5/16/2011)
You might want to check the difference between your actual code and your posted code.I've done that before. Changed my code to post on the forums and, in the process, inadvertently solved my problem. Maybe that's why your code works for Gail and isn't working for you. @=)
I'd love to have the link to that thread :w00t:.
May 16, 2011 at 10:54 am
Ray K (5/16/2011)
When I tried calling the SP, I got the 'Must declare the scalar variable "@Result".' message.
Declare @Result int;
Execute TestProc 'ID','Email', @Result output;
That's how you call an output variable. But now that I've tested it in both 2k5 and 2k8, I see what you're saying. I'm getting a "Command(s) completed successfully" message, but no output.
What's going on with the @Plist thing? Why do you have that in the final execute?
May 16, 2011 at 10:58 am
Brandie Tarvin (5/16/2011)
What's going on with the @Plist thing? Why do you have that in the final execute?
Mainly force of habit -- I copy/pasted from some other code I had, where I was creating dynamic SQL based on parameters that were passed. Wasn't thinking! (I seem to do that a lot! ;-))
(BTW, that did the trick -- thanks!)
+--------------------------------------------------------------------------------------+
Check out my blog at https://pianorayk.wordpress.com/
May 16, 2011 at 11:03 am
Ray K (5/16/2011)
Brandie Tarvin (5/16/2011)
What's going on with the @Plist thing? Why do you have that in the final execute?
Mainly force of habit -- I copy/pasted from some other code I had, where I was creating dynamic SQL based on parameters that were passed. Wasn't thinking! (I seem to do that a lot! ;-))
(BTW, that did the trick -- thanks!)
I'm glad you got it working... You did get the result coming back out, yes?
May 16, 2011 at 11:05 am
Brandie Tarvin (5/16/2011)
Ray K (5/16/2011)
Brandie Tarvin (5/16/2011)
What's going on with the @Plist thing? Why do you have that in the final execute?Mainly force of habit -- I copy/pasted from some other code I had, where I was creating dynamic SQL based on parameters that were passed. Wasn't thinking! (I seem to do that a lot! ;-))
(BTW, that did the trick -- thanks!)
I'm glad you got it working... You did get the result coming back out, yes?
Yes I did! (And after you pointed out my little mental lapse, maybe now I'll go back and rewrite my SP!)
(On the other hand, if it ain't broke . . . )
+--------------------------------------------------------------------------------------+
Check out my blog at https://pianorayk.wordpress.com/
May 16, 2011 at 11:08 am
Ray K (5/16/2011)
Brandie Tarvin (5/16/2011)
Ray K (5/16/2011)
Brandie Tarvin (5/16/2011)
What's going on with the @Plist thing? Why do you have that in the final execute?Mainly force of habit -- I copy/pasted from some other code I had, where I was creating dynamic SQL based on parameters that were passed. Wasn't thinking! (I seem to do that a lot! ;-))
(BTW, that did the trick -- thanks!)
I'm glad you got it working... You did get the result coming back out, yes?
Yes I did! (And after you pointed out my little mental lapse, maybe now I'll go back and rewrite my SP!)
(On the other hand, if it ain't broke . . . )
... leave it around to confuse future DBAs and Devs?
You are a cruel man, Ray. @=)
May 16, 2011 at 11:10 am
Brandie Tarvin (5/16/2011)
Ray K (5/16/2011)
Yes I did! (And after you pointed out my little mental lapse, maybe now I'll go back and rewrite my SP!)(On the other hand, if it ain't broke . . . )
... leave it around to confuse future DBAs and Devs?
You are a cruel man, Ray. @=)
😀
+--------------------------------------------------------------------------------------+
Check out my blog at https://pianorayk.wordpress.com/
May 16, 2011 at 11:31 am
Brandie Tarvin (5/16/2011)
Ray K (5/16/2011)
When I tried calling the SP, I got the 'Must declare the scalar variable "@Result".' message.Declare @Result int;
Execute TestProc 'ID','Email', @Result output;
That's how you call an output variable. But now that I've tested it in both 2k5 and 2k8, I see what you're saying. I'm getting a "Command(s) completed successfully" message, but no output.
No, you won't get any output, because the variable is not selected anywhere.
Now this,...
Declare @Result int;
Execute TestProc 'ID','Email', @Result output;
SELECT @Result
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
May 16, 2011 at 11:37 am
GilaMonster (5/16/2011)
Brandie Tarvin (5/16/2011)
Ray K (5/16/2011)
When I tried calling the SP, I got the 'Must declare the scalar variable "@Result".' message.Declare @Result int;
Execute TestProc 'ID','Email', @Result output;
That's how you call an output variable. But now that I've tested it in both 2k5 and 2k8, I see what you're saying. I'm getting a "Command(s) completed successfully" message, but no output.
No, you won't get any output, because the variable is not selected anywhere.
Now this,...
Declare @Result int;Execute TestProc 'ID','Email', @Result output;
SELECT @Result
Point taken. I'm so used to having the SELECT @OutputVariable inside the proc that I forgot the final SELECT outside the proc.
Example:
Create Proc TestProc (@result int output)
AS
Set @Result = 5;
SELECT @Result;
GO
Declare @Result int;
Exec TestProc @Result output
That works perfectly without the external SELECT.
Viewing 15 posts - 1 through 14 (of 14 total)
You must be logged in to reply to this topic. Login to reply