Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
GavinOhEaghra
Starting Member
2 Posts |
 Posted - 2010-04-21 : 05:13:09
|
| I have a SP with 2 update statements, 1 runs conditionally. When both run -1 is returned, otherwise 1. Both updates statements complete successfully but the -1 return is causing me a problem. Is there a way around this?Many thanks |
|
|
senthil_nagore
Master Smack Fu Yak Hacker
1007 Posts |
Posted - 2010-04-21 : 05:17:02
|
| Post your SP!Senthil.C------------------------------------------------------[Microsoft][ODBC SQL Server Driver]Operation canceledhttp://senthilnagore.blogspot.com/ |
 |
|
|
webfred
Master Smack Fu Yak Hacker
8781 Posts |
Posted - 2010-04-21 : 05:17:57
|
Yes, you can change your SP to not return -1.If that isn't the answer you're looking for then give more information please.
No, you're never too old to Yak'n'Roll if you're too young to die. |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2010-04-21 : 06:06:35
|
quote:
Originally posted by GavinOhEaghra
I have a SP with 2 update statements, 1 runs conditionally. When both run -1 is returned, otherwise 1. Both updates statements complete successfully but the -1 return is causing me a problem. Is there a way around this?Many thanks
yup. put the return 1 under else.------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
|
|
|
GavinOhEaghra
Starting Member
2 Posts |
Posted - 2010-04-21 : 07:32:55
|
quote:
Originally posted by senthil_nagore
Post your SP!Senthil.C------------------------------------------------------[Microsoft][ODBC SQL Server Driver]Operation canceledhttp://senthilnagore.blogspot.com/
The records represent webpages, with the parentID field chaining them into a hierarchy. The initial conditional update operates when a record is moved from the chain, its children then have their parentID updated to what would have been their grandparents ID. It works fine. Only thing is that the SP returns -1 not 1 even though it worked. It returns 1 if only the last Update executes (when a record updates without being moved) ALTER PROCEDURE [dbo].[DD_ContentItems_UpdateContentItem] -- Add the parameters for the stored procedure here @ID int ,@Name nvarchar(256) ,@Title nvarchar(256) ,@Keywords nvarchar(2000) ,@Description nvarchar(4000) ,@ItemAbstract nvarchar(4000) ,@Body ntext ,@ReleaseDate datetime2(7) ,@ExpireDate datetime2(7) ,@Category nvarchar(256) ,@Virtual bit ,@Languages int ,@Importance int ,@GalleryID int ,@Enabled bit ,@ParentID int ,@Roles nvarchar(256)ASBEGIN-- Grandparent adopts children if item is moving DECLARE @OldParentID int SET @OldParentID = (SELECT ParentID from [dbo].[DD_ContentItems] WHERE ContentItemID = @ID) -- check if parentID passed differs from present parentID IF (@ParentID <> @OldParentID) BEGIN -- update any children of this to use its parentid if its moving UPDATE [SQL2008_696372_gavinohara].[dbo].[DD_ContentItems] SET [ParentID] = @OldParentID ,[ModDate] = GETDATE() WHERE ParentID = @ID END -- Update record UPDATE [SQL2008_696372_gavinohara].[dbo].[DD_ContentItems] SET [Name] = @Name ,[Title] = @Title ,[Keywords] = @Keywords ,[Description] = @Description ,[ItemAbstract] = @ItemAbstract ,[Body] = @Body ,[ReleaseDate] = @ReleaseDate ,[ExpireDate] = @ExpireDate ,[Category] = @Category ,[Virtual] = @Virtual ,[Languages] = @Languages ,[Importance] = @Importance ,[GalleryID] = @GalleryID ,[Enabled] = @Enabled ,[Roles] = @Roles ,[ParentID] = @ParentID ,[ModDate] = GETDATE() WHERE ContentItemID = @IDENDGavin |
|
|
|
webfred
Master Smack Fu Yak Hacker
8781 Posts |
Posted - 2010-04-21 : 07:49:09
|
Sorry, is that all?I can't see a return statement in your posted code.
No, you're never too old to Yak'n'Roll if you're too young to die. |
|
|
|
|
|
|
|
|
SP Multiple Update Statements - Return Value -1