return code of a stored proc - SQL Server Forums

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.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

return code of a stored proc

Author

Topic

doubleotwo
Yak Posting Veteran

69 Posts

Posted - 2010-06-25 : 03:15:09

i got this stored proc


ALTER PROCEDURE [dbo].[sp_deleterooster]
	-- Add the parameters for the stored procedure here
	@roosterid bigint
,	@planningid bigint 
AS
BEGIN

	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;

    -- Insert statements for procedure here
DELETE FROM tblrooster
WHERE roosterid = @roosterid
	AND planningid = @planningid;

DELETE FROM tblrooster_planning
WHERE id = @planningid;
	
	
	

END

which does the job but gives the return code -1 in my .NET app, -1 is an error , right ?

why does it give an error ? Not possible to put multiple sql statements together ? i ended both of them with a ;, mb there is something wrong ?

please help me :)

Lumbago
Norsk Yak Master

3271 Posts

Posted - 2010-06-25 : 03:34:43

There is no need for the semicolon, sql server is smart enough to separate the statements. I can't see anything wrong the procedure but to be honest I have no idea what the return code is when running a stored procedure

Is it doing what it's supposed to do?

- Lumbago

My blog (yes, I have a blog now! just not that much content yet)
-> www.thefirstsql.com

Sachin.Nand

2937 Posts

Posted - 2010-06-25 : 03:51:27

Because you are not returning anything from your SP.
Write

Return @@RowCount

which will return No of rows effected by your query or put

NO COUNT OFF

in your SP.It will turn ON the message indicating the number of rows affected by your SP and makes the SP always return no of rows affected when called from the .NET app using ExecuteNonQuery.
Also put the query in a transaction.
You can set delete cascade on for your table if there exists any relationship between tblrooster and tblrooster_planning tables.So there will be no need for multiple delete statements.

Limitations live only in our minds. But if we use our imaginations, our possibilities become limitless.

PBUH

Lumbago
Norsk Yak Master

3271 Posts

Posted - 2010-06-25 : 04:07:32

Great advice Idera, except for the thing about cascading deletes. They are the devil and should be avoided at all cost! (my personal opinion there but I have successfully stayed clear of the "oops, I deleted a category that I shouldn't have and now all articles and data related to that category is goooone"-message so far and I intend to keep it that way!)

- Lumbago

My blog (yes, I have a blog now! just not that much content yet)
-> www.thefirstsql.com

doubleotwo
Yak Posting Veteran

69 Posts

Posted - 2010-06-25 : 04:15:09

thx alot idera

cascading delete is something like deleting an id, and the sql server sees if this id has any relation ship and therefor delete the corresponding fields ?

yeah im not gonna do that for the reason lumbago stated... + i find this actually clearer ...
but it will be an option to increase performance i guess ?

Lumbago
Norsk Yak Master

3271 Posts

Posted - 2010-06-25 : 04:34:43

It's basically an option for lazy developers. What it does is to check all the foreign keys and whenever you delete a row in a table it also deletes any related rows in all other tables. Pretty nasty stuff if you ask me. There is no increase in performance, it's only a matter of not having to explicitly write the delete query.

- Lumbago

My blog (yes, I have a blog now! just not that much content yet)
-> www.thefirstsql.com

Sachin.Nand

2937 Posts

Posted - 2010-06-25 : 05:53:03

quote:
Originally posted by Lumbago

Great advice Idera, except for the thing about cascading deletes. They are the devil and should be avoided at all cost! (my personal opinion there but I have successfully stayed clear of the "oops, I deleted a category that I shouldn't have and now all articles and data related to that category is goooone"-message so far and I intend to keep it that way!)

- Lumbago

My blog (yes, I have a blog now! just not that much content yet)
-> www.thefirstsql.com

Thanks.
About cascade deletes I just feel it is a good way to prevent orphan records getting built up in your child tables.I never had any issues with them.

Limitations live only in our minds. But if we use our imaginations, our possibilities become limitless.

PBUH

Lumbago
Norsk Yak Master

3271 Posts

Posted - 2010-06-25 : 06:10:40

I guess it depends on what kind of systems you work on and what kind of policies you have. If you have full control over everything then cascading deletes might not be a problem, but when you have 10 developers and 2 managers thinking they are experts it's another story. I find it comforting to know that whenever someone wants to delete something they will get an error message saying that what they're doing have far greater implications than what they were thinking in the first place.

But if it works for you thats great, it's just important to know the implications

- Lumbago

My blog (yes, I have a blog now! just not that much content yet)
-> www.thefirstsql.com

doubleotwo
Yak Posting Veteran

69 Posts

Posted - 2010-06-25 : 14:59:58

im happy to know that this is a possibility of SQL thanks to you guys :p ( flattering modus on )

still im afraid if implement such a thing, i will forget about its being there and then search my ass of why the hell the other records are gone :p

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources