Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations strongm on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Explanation as to how this function works

Status
Not open for further replies.

tbode2006

Programmer
Aug 24, 2006
62
US
Code:
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go

 
ALTER PROCEDURE [dbo].[fsAction_d]
 @ActionIdentifier int
 
AS
 
/******************************************************************/
/*  Delete procedure returns:                                     */
/*     a) -999 if an error occurred                               */
/*     b) The number of records deleted (0 or 1)                  */
/******************************************************************/
 
/******************************************************************/
/*  IMPORTANT NOTE!!!                                             */
/*     Do not insert any DML statements (SELECT, INSERT, UPDATE   */
/*     DELETE, OPEN CURSOR, or FETCH) before the following        */
/*     section of code (labeled 'transaction mgt').  In           */
/*     chained transaction mode, any DML statement (including     */
/*     SELECTs) will start a transaction if none currently        */
/*     exists, and will increment the value of @@trancount!       */
/******************************************************************/
 
DECLARE @_error int
DECLARE @_rowcount int
DECLARE @ret_val int
 
DECLARE @i_started_tran int		      /* transaction mgt */
DECLARE @rollback_needed int		      /* transaction mgt */
 
IF  @@trancount = 0			      /* transaction mgt */
BEGIN					      /* transaction mgt */
    BEGIN TRANSACTION			      /* transaction mgt */
    SELECT @i_started_tran = 1		      /* transaction mgt */
END					      /* transaction mgt */
ELSE					      /* transaction mgt */
    SELECT @i_started_tran = 0		      /* transaction mgt */
 
SELECT @rollback_needed = 0		      /* transaction mgt */
 
/******************************************************************/
/*  SELECTs and other DML statments can begin after this point    */
/******************************************************************/
 
SET NOCOUNT ON
 
SELECT @ret_val = 0
[highlight] 
/*********** delete user table record *****************/
 
DELETE Action
WHERE
    @ActionIdentifier = ActionIdentifier
[/highlight] 
SELECT @_error = @@error, @_rowcount = @@rowcount 
 
IF (@_error <> 0)
   BEGIN
      SELECT @ret_val = (-999)
      RAISERROR ('Delete failed in procedure fsAction_d ', 16, 1) 
      SELECT @rollback_needed = 1
      GOTO label_return
   END
ELSE
   BEGIN
      SELECT @ret_val = @_rowcount
   END
 
label_return:
 
IF  @i_started_tran = 1			      /* transaction mgt */
    IF  @rollback_needed = 1		      /* transaction mgt */
    BEGIN				      /* transaction mgt */
        ROLLBACK TRANSACTION		      /* transaction mgt */
        RETURN @ret_val			      /* transaction mgt */
    END					      /* transaction mgt */
    ELSE				      /* transaction mgt */
    BEGIN				      /* transaction mgt */
        COMMIT TRANSACTION		      /* transaction mgt */
        RETURN @ret_val			      /* transaction mgt */
    END					      /* transaction mgt */
ELSE					      /* transaction mgt */
    RETURN @ret_val			      /* transaction mgt */

I have been given a client database to work with and may need to use the following code. This appears to be some of generic delete function. I do not understand at all how this works. Can someone enlighten me about the highlighted area? On the "DELETE Action" line, Action is in BLUE indicating that it is a reserved word on SQL 2005. I can find no reference to this syntax.
 
Action isn't a system table or view name, so it's an object that's been created in the database. You should qualify it(ex [Action]) to avoid confusion.

Action is a property used in SQL-DMO backups...I'm not sure if it's used anywhere else.

Generally, the statement is deleting records from the table or view called [Action], where the ActionIdentifier is equal to whatever integer value is supplied when calling the sproc (you can see that @ActionIdentifier is an input param).
 
Action isn't a system table or view name, so it's an object that's been created in the database.

If it is in the database, how do I go about locating it? What subtree is it under? I see Server Objects under the main tree but it is not there in my SQL Server instance.
 
This is a table or View, so you mush exand your database node and expand Tables and/or Views node, Search it there.

Borislav Borissov
VFP9 SP1, SQL Server 2000/2005.
Microsoft MVP VFP
 
BTW it IS in Database.
And it is an object from that DB.
I think TheSQL means that this is not a SYSTEM object in DataBase.

Borislav Borissov
VFP9 SP1, SQL Server 2000/2005.
Microsoft MVP VFP
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top