How to check SP has worked? - 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

General SQL Server Forums

New to SQL Server Programming

How to check SP has worked?

Author

Topic

insanepaul
Posting Yak Master

178 Posts

Posted - 2009-06-08 : 04:28:56

I have a simple SP but don't know how to check that it has successfully run. I'm guessing there should be a return type of either true or false

CREATE PROC sp_DeleteTool
@stoolguid nvarchar(50)
AS

delete from strcellformulae where stoolguid = @stoolguid

senthil_nagore
Master Smack Fu Yak Hacker

1007 Posts

Posted - 2009-06-08 : 04:40:26

exec sp_DeleteTool 'id'

then manually run the select query across the strcellformulae table!

Senthil.C
------------------------------------------------------
[Microsoft][ODBC SQL Server Driver]Operation canceled

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2009-06-08 : 05:23:25

[code]
create PROC sp_DeleteTool
(
@stoolguid nvarchar(50)
)
as
delete from strcellformulae where stoolguid = @stoolguid
return @@rowcount
go

-- Make a testrun...
declare @returnvalue int
exec @returnvalue=sp_DeleteTool '163'
select @returnvalue
[/code]

Where @returnvalue has number of effected records by delete from @@rowcount

No, you're never too old to Yak'n'Roll if you're too young to die.

insanepaul
Posting Yak Master

178 Posts

Posted - 2009-06-08 : 05:48:28

quote:
Originally posted by webfred

create PROC sp_DeleteTool
(
@stoolguid nvarchar(50)
)
as
delete from strcellformulae where stoolguid = @stoolguid
return @@rowcount
go

-- Make a testrun...
declare @returnvalue int
exec @returnvalue=sp_DeleteTool '163'
select @returnvalue

Where @returnvalue has number of effected records by delete from @@rowcount

Your reply makes sense however I realised that I need to put a list of delete statements in a transaction so I've digged deeper and found this:

BEGIN TRANSACTION
--1. strCellFormulae
DELETE FROM strcellformulae WHERE stoolguid = @stoolguid
IF @@ERROR <> 0
Begin
ROLLBACK
--raiserror(message,severity, state)
RAISERROR('Error in deleting tool from strcellformulae.',16,1)
RETURN
END

From the c# code that calls the SP what can I use instead of this:
declare @returnvalue int
exec @returnvalue=sp_DeleteTool '163'
select @returnvalue

senthil_nagore
Master Smack Fu Yak Hacker

1007 Posts

Posted - 2009-06-08 : 05:56:55

quote:
Originally posted by insanepaul

quote:
Originally posted by webfred

create PROC sp_DeleteTool
(
@stoolguid nvarchar(50)
)
as
delete from strcellformulae where stoolguid = @stoolguid
return @@rowcount
go

-- Make a testrun...
declare @returnvalue int
exec @returnvalue=sp_DeleteTool '163'
select @returnvalue

Where @returnvalue has number of effected records by delete from @@rowcount

Your reply makes sense however I realised that I need to put a list of delete statements in a transaction so I've digged deeper and found this:
BEGIN TRANSACTION
--1. strCellFormulae
DELETE FROM strcellformulae WHERE stoolguid = @stoolguid
IF @@ERROR <> 0
Begin
ROLLBACK
--raiserror(message,severity, state)
RAISERROR('Error in deleting tool from strcellformulae.',16,1)
RETURN
END

From the c# code that calls the SP what can I use instead of this:
declare @returnvalue int
exec @returnvalue=sp_DeleteTool '163'
select @returnvalue

Better u check in c# forum!

Senthil.C
------------------------------------------------------
[Microsoft][ODBC SQL Server Driver]Operation canceled

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources