DELETE Procedure. How to return a value?

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

hi miguel.

below is the sp i modified. i've added an output parameter, which will be null if record(s) are deleted properly. so you can check in your code using output parameter.

SET ANSI_NULLSON
GO
SET QUOTED_IDENTIFIERON
GO

ALTER PROCEDURE [dbo].[Content_DeleteContent]

-- Define the procedure parameters
@.ContentNameNVARCHAR(100),
@.ContentCultureNVARCHAR(5) ,
@.nFlag tinyint output

AS

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

-- Declare and define ContentId
DECLARE @.ContentIdUNIQUEIDENTIFIER;
SELECT @.ContentId = ContentIdFROM dbo.ContentWHERE ContentName =
@.ContentName

-- Check if ContentId is Not Null
IF @.ContentIdISNOT NULL
BEGIN

-- Check if ContentId is Null
IF @.ContentCultureISNULL
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;

Select @.nFlag = ContentIDfrom dbo.ContentLocalized
WHERE ContentId = @.ContentId
if @.nFlagisnull
Begin
Select @.nFlag = 1FROM dbo.Content
WHERE ContentName = @.ContentName;
End

END
ELSE

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

Select @.nFlage = ContentIDFROM dbo.ContentLocalized
WHERE (ContentID = @.ContentID AND ContentCulture = @.ContentCulture)

END

END

hope it helps.

regards,

satish.|||Rather than do SELECT's after the DELETE's checking for @.@.ROWCOUNT works faster since the number of records deleted is already stored in the function. You could just check if @.@.ROWCOUNT >0 and set your flag to 1 or 0 appropriately.|||

Hi Dinakar

miguel has usedSET NOCOUNT ON.

thanks,

satish.

|||

Hello,

I defined "Set NoCount On" because this is done by default in SQL Server 2005 Management Studio and because this is how I see in most code examples.

I am just starting with SQL. Should I change it?

Could somebody give me some info on this?

Thanks,

Miguel

|||

hi miguel,

sorry i m not aware of default in sql 2k5

set nocount on --in case you update/insert/delete record on a table it wont return number of affected records

set nocount off --in case you update/insert/delete record on a table it will return number of affected records

hope it clarifies

now if you set it to off in your sp then after delete you can check

if @.@.ROWCOUNT > 0

begin

end

@.@.ROWCOUNT contains no.of records affected due to preceeding operation.

regards,

satish.

|||

SET NOCOUNT will only suppress messages from being printed in the Messages tab which are returned to the calling application. It does not have any effect on @.@.ROWCOUNT. You can still do your T-SQL Queries and have the results of rows affected in @.@.ROWCOUNT, get the value into variable and return it via OUTPUT parameters. Having the SET NOCOUNT ON is a good thing, generally. It prevents unnecessary messages from being passed across to the application.

Satish,

You are probably checking the Messages tab by running a T-SQL statement between SET NOCOUNT commands.

Try this:

SET NOCOUNT ON

SELECT TOP 10 * FROM anyTable

SELECT @.@.ROWCOUNT as RowsAffected

SET NOCOUNT OFF

|||

errrrr silly of me.

thanks dinakar you removed my misunderstanding about @.@.ROWCOUNT and SET NOCOUNT

cheers,

satish.