Results 1 to 9 of 9
  1. #1
    Join Date
    Jun 2010
    Posts
    21

    Unanswered: Help with my first Stored Procedure

    Hello all...

    I've created a web form that when submitted should add the submitted data to three different tables.

    I've never really created a SP before so I'm a bit confused on the proper and easiest way to do this.

    I can go into SSMS and click on the Script To Insert for each of the tables but not really sure how to combine the three or make it so that all tables get updated or make sure that none do if the operation fails.

    Table 1 is called Season with the following columns
    SeasonID,
    SeasonYear
    SeasonDescription

    Table 2 is called Team, with the following columns
    TeamID
    TameName
    TeamImage
    TeamAbbr

    Table 3 is called TeamType, with the following columsn
    TeamTypeID
    TeamType

    Any thoughts or suggestions would be great...

    Thanks,
    Bob
    Last edited by bobmc; 08-02-10 at 00:31. Reason: Added 3rd Table..

  2. #2
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

  3. #3
    Join Date
    Jun 2010
    Posts
    21
    This is my first attempt, I'm pretty sure its wrong but I'm not sure how to test it either. I know I'd use the exec command but not sure how I'd pass the variables for the IDs of the other tables in that command. Also I used SCOPE_IDENTITY() twice, which I couldn't find any examples of anyone doing this, but pretty much all of the examples I reviewed were for only two tables, so wasn't sure.

    Code:
    CREATE PROCEDURE SP_InsertSeasonTeam    
      
    
    @SeasonYear varchar(30),    
    @SeasonDescription varchar(30),  
    @TeamTypeFKID int,  
    @SeasonFKID int,
    @TeamName varchar(100),
    @TeamImage varchar(400),
    @TeamAbbr varchar(8),
    @TeamTypeID int,
    @TeamType varchar(25)
    
      
               
    AS    
        
    declare @SeasonID int     
                            
    BEGIN TRANSACTION Tran_InsertSeasonTeam                    
               
    
    
    INSERT INTO MCSN_Season(SeasonYear, SeasonDescription) VALUES (@SeasonYear, @SeasonDescription)
    INSERT INTO MCSN_TeamType(TeamType) VALUES (@TeamType)
        
    set @SeasonID=SCOPE_IDENTITY() 
    set @TeamTypeID=SCOPE_IDENTITY()    
        
    INSERT INTO MCSN_Team(TeamName, TeamImage, TeamAbbr, TeamTypeFKID, SeasonFKID) VALUES (@TeamName, @TeamImage, @TeamAbbr, @SeasonID, @TeamTypeID) 
        
    COMMIT TRANSACTION Tran_InsertSeasonTeam            
    
    
    GO
    Would be very greatfull for any feedback on how to make this work right..

    Thanks,
    Bob

  4. #4
    Join Date
    Aug 2010
    Posts
    1
    Check out this page. It has a good introduction on how to write T-SQL.
    SQL INSERT INTO Statement

  5. #5
    Join Date
    Jun 2010
    Posts
    21
    Thanks for reply nabber, however I've reviewed that site before and honestly it didn't help me much.

    Thanks,
    Bob

  6. #6
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    SCOPE_IDENTITY() is for the last statement, so you need it call it immediately after the statement you need the identity value from.
    However, assuming season years and team types are unique (and it looks like they should be) then you don't even need to use SCOPE_IDENTITY.
    Testimonial:
    pootle flump
    ur codings are working excelent.

  7. #7
    Join Date
    Jun 2010
    Posts
    21
    Pootle..Thanks for the reply, however, I have to say now I'm even more confused..

    I have to pass the ID of the Season Table and the TeamType Table to the Team's Table during the INSERT because of the FK relations. This way I can display a report that shows all the Teams, and their Type within a specifc Season.

    Wouldn't I need SCOPE_IDENTITY() for that?

    Thanks,
    Bob

  8. #8
    Join Date
    Nov 2004
    Posts
    1,427
    Provided Answers: 4
    I am learning myself how to tackle errors during the execution of SP's. I tried two different ways to manage the transactions.
    General part:
    Code:
    CREATE TABLE Season(
    	SeasonID			int	IDENTITY (1, 1) NOT NULL,
    	SeasonYear			varchar(30)			NOT NULL
    	,  --CONSTRAINT cc_give_error CHECK (SeasonYear IS NULL), --Remove the ', --' before CONSTRAINT to force an error
    	SeasonDescription	varchar(30)			NOT NULL,
    	CONSTRAINT PK_Season PRIMARY KEY (SeasonID)
    )
    
    CREATE TABLE Team(
    	TeamID			int	IDENTITY (1, 1) NOT NULL,
    	TeamTypeID		int					not null,
    	SeasonID		int					not null,
    	TeamName		varchar(100)		not null,
    	TeamImage		varchar(400)		not null,
    	TeamAbbr		varchar(8)			not null,
    	CONSTRAINT PK_Team PRIMARY KEY (TeamID)
    )
    
    CREATE TABLE TeamType(
    	TeamTypeID		int	IDENTITY (1, 1) NOT NULL,
    	TeamType		varchar(25)			NOT NULL,
    	CONSTRAINT PK_TeamType PRIMARY KEY (TeamTypeID)
    )
    First method (old school), testing on the @@ERROR:
    Code:
    CREATE PROCEDURE Proc_InsertSeasonTeam (
    	@SeasonYear varchar(30),
    	@SeasonDescription varchar(30),
    
    	@TeamName varchar(100),
    	@TeamImage varchar(400),
    	@TeamAbbr varchar(8),
    
    	@TeamType varchar(25)
    )
    
    AS
    SET NOCOUNT ON 
    declare @SeasonID int     
    declare @TeamTypeID int
    
    BEGIN TRANSACTION                    
    
    	INSERT INTO Season(SeasonYear, SeasonDescription)
    	 VALUES (@SeasonYear, @SeasonDescription)
    	SET  @SeasonID = SCOPE_IDENTITY() 
    	IF @@ERROR <> 0
    	BEGIN
    		-- Rollback the transaction
    		ROLLBACK
    
    		-- Raise an error and return
    		RAISERROR ('Error in INSERT INTO Season.', 16, 1)
    		SET NOCOUNT OFF
    		RETURN
    	END
    
    	INSERT INTO TeamType(TeamType)
    	 VALUES (@TeamType)
    	SET @TeamTypeID = SCOPE_IDENTITY()    
    	IF @@ERROR <> 0
    	BEGIN
    		-- Rollback the transaction
    		ROLLBACK
    
    		-- Raise an error and return
    		RAISERROR ('Error in INSERT INTO TeamType.', 16, 1)
    		SET NOCOUNT OFF
    		RETURN
    	END
    	    
    	INSERT INTO Team(TeamName, TeamImage, TeamAbbr, TeamTypeID, SeasonID)
    	 VALUES (@TeamName, @TeamImage, @TeamAbbr, @TeamTypeID, @SeasonID) 
    	IF @@ERROR <> 0
    	BEGIN
    		-- Rollback the transaction
    		ROLLBACK
    
    		-- Raise an error and return
    		RAISERROR ('Error in INSERT INTO Team.', 16, 1)
    		SET NOCOUNT OFF
    		RETURN
    	END
        
    COMMIT TRANSACTION 
    SET NOCOUNT OFF
    GO
    
    EXECUTE Proc_InsertSeasonTeam @SeasonYear = '2010', @SeasonDescription = 'This season',
    		@TeamName = 'My Team', @TeamImage = 'NiceImage.img', @TeamAbbr = 'abbrev',
    		@TeamType = 'MyTeamType'
    		
    select * from Season
    select * from Team
    select * from TeamType
    
    /*
    Msg 547, Level 16, State 0, Procedure Proc_InsertSeasonTeam, Line 20
    The INSERT statement conflicted with the CHECK constraint "cc_give_error". The conflict occurred in database "test", table "dbo.Season", column 'SeasonYear'.
    The statement has been terminated.
    Msg 515, Level 16, State 2, Procedure Proc_InsertSeasonTeam, Line 48
    Cannot insert the value NULL into column 'SeasonID', table 'test.dbo.Team'; column does not allow nulls. INSERT fails.
    The statement has been terminated.
    Msg 50000, Level 16, State 1, Procedure Proc_InsertSeasonTeam, Line 56
    Error in INSERT INTO Team.
    
    (0 row(s) affected)
    
    (0 row(s) affected)
    
    (0 row(s) affected)
    
    */
    This didn't seem to work. Code AFTER the RETURN is still executed! What did I do wrong?
    This code works on MSSQL 2000 and up.

    Second method, with a TRY CATCH block (works on MSSQL 2005 and up):
    Code:
    CREATE PROCEDURE Proc_InsertSeasonTeamTryCatch (
    	@SeasonYear varchar(30),
    	@SeasonDescription varchar(30),
    
    	@TeamName varchar(100),
    	@TeamImage varchar(400),
    	@TeamAbbr varchar(8),
    
    	@TeamType varchar(25)
    )
    
    AS
    SET NOCOUNT ON 
    declare @SeasonID int     
    declare @TeamTypeID int
    
    IF @@TRANCOUNT > 0
    BEGIN
    	DECLARE @ERROR_MSG VARCHAR(500)
    	SET @ERROR_MSG = 'Procedure ' + ERROR_PROCEDURE() + ' may not be called from within a transaction.'
    	RAISERROR(@ERROR_MSG, 16, 1)
    	ROLLBACK TRANSACTION
    	RETURN
    END
    
    
    BEGIN TRY
    	BEGIN TRANSACTION                    
    
    		INSERT INTO Season(SeasonYear, SeasonDescription)
    		 VALUES (@SeasonYear, @SeasonDescription)
    		SET  @SeasonID = SCOPE_IDENTITY() 
    
    		INSERT INTO TeamType(TeamType)
    		 VALUES (@TeamType)
    		SET @TeamTypeID = SCOPE_IDENTITY()    
    		    
    		INSERT INTO Team(TeamName, TeamImage, TeamAbbr, TeamTypeID, SeasonID)
    		 VALUES (@TeamName, @TeamImage, @TeamAbbr, @TeamTypeID, @SeasonID) 
        
    	COMMIT TRANSACTION 
    
        END TRY
    
        BEGIN CATCH
            IF @@TRANCOUNT > 0
    		BEGIN
    			ROLLBACK TRANSACTION
    			DECLARE @ErrMsg nvarchar(4000), @ErrSeverity int 
    			SELECT @ErrMsg = ERROR_MESSAGE(), @ErrSeverity = ERROR_SEVERITY()
    			RAISERROR(@ErrMsg, @ErrSeverity, 1)
    		END
    	END CATCH
    	
    SET NOCOUNT OFF
    GO
    
    EXECUTE Proc_InsertSeasonTeamTryCatch @SeasonYear = '2010', @SeasonDescription = 'This season',
    		@TeamName = 'My Team', @TeamImage = 'NiceImage.img', @TeamAbbr = 'abbrev',
    		@TeamType = 'MyTeamType'
    		
    select * from Season
    select * from Team
    select * from TeamType
    
    /*
    Msg 50000, Level 16, State 1, Procedure Proc_InsertSeasonTeamTryCatch, Line 52
    The INSERT statement conflicted with the CHECK constraint "cc_give_error". The conflict occurred in database "test", table "dbo.Season", column 'SeasonYear'.
    
    (0 row(s) affected)
    
    (0 row(s) affected)
    
    (0 row(s) affected)
    */
    Here the error is caught nicely. The code is also much more readable compared with the previous SP.

    Tested on MSSQL 2008.
    With kind regards . . . . . SQL Server 2000/2005/2012
    Wim

    Grabel's Law: 2 is not equal to 3 -- not even for very large values of 2.
    Pat Phelan's Law: 2 very definitely CAN equal 3 -- in at least two programming languages

  9. #9
    Join Date
    Jun 2010
    Posts
    21
    Wim..Thanks so much! I can't say I completely understand every line in the SP but this will give me a great place to start learning how to do it correctly.

    Thanks!
    Bob

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •