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
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.
No comments:
Post a Comment