DELETE Procedure. How to return a value? - SQL Server Forums

Home | Weblogs | Forums

Site Sponsored By: SQLDSC - SQL Server Desired State Configuration

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 2000 Forums

Transact-SQL (2000)

DELETE Procedure. How to return a value?

Author

Topic

shapper
Constraint Violating Yak Guru

450 Posts

Posted - 2006-12-03 : 20:09:14

Hello,

I created a DELETE Stored Procedure in SQL 2005.
When calling this procedure from my server code (VB.NET in my case) I need to know if the record was deleted or not.

How should I do this?

Should I make the procedure to return True or False? If yes, how can I do this?

My Stored Procedure is as follows (I think it is ok):

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

ALTER PROCEDURE [dbo].[Content_DeleteContent]

-- Define the procedure parameters
@ContentName NVARCHAR(100),
@ContentCulture NVARCHAR(5)

AS

-- Prevent extra result sets from interfering with SELECT statements.
SET NOCOUNT ON;

-- Declare and define ContentId
DECLARE @ContentId UNIQUEIDENTIFIER;
SELECT @ContentId = ContentId FROM dbo.Content WHERE ContentName = @ContentName

-- Check if ContentId is Not Null
IF @ContentId IS NOT NULL
BEGIN

-- Check if ContentId is Null
IF @ContentCulture IS NULL
BEGIN

-- Delete all localized contents from dbo.ContentLocalized
DELETE
FROM dbo.ContentLocalized
WHERE ContentId = @ContentId

-- Delete content from dbo.Content
DELETE
FROM dbo.Content
WHERE ContentName = @ContentName;

END
ELSE

-- Delete localized content from dbo.ContentLocalized
DELETE
FROM dbo.ContentLocalized
WHERE (ContentID = @ContentID AND ContentCulture = @ContentCulture)

END

GO

Thanks,
Miguel

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2006-12-03 : 23:44:42

Based on the value of @@ROWCOUNT you can set a bit variable to TRUE or FALSE:

IF @@ROWCOUNT > 0
  SET @FLAG = 1
ELSE
  SET @FLAG = 0

Harsh Athalye
India.
"Nothing is Impossible"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources