dcsimg
Results 1 to 11 of 11

Thread: [RESOLVED] Sql server 2008 - how to create stored procedure?

  1. #1

    Thread Starter
    Fanatic Member aNubies's Avatar
    Join Date
    Aug 2008
    Posts
    558

    Resolved [RESOLVED] Sql server 2008 - how to create stored procedure?

    Hey Guys,

    I don't have any background about stored procedure, so I don't know the syntax and how it works. Basically what I'm trying to do is, I want to insert my data from one database to another and then upon successful transfer one of the column from the source database should be updated right away telling me that that record is transferred.

    Can anyone give teach me how to do this. I created mine but I don't think this is right. Here is my code.

    Code:
    	INSERT INTO [DESTINATION].[dbo].[EMPLOYEES]
    	       (EMPLOYEENO, LASTNAME, FIRSTNAME, MIDDLENAME, MIDDLEINITIAL, SUFFIXNAME, DATEHIRED, DEPARTMENT)
    	SELECT [SOURCE].[dbo].[TBL_APPLICANT_HIRED].[EMPLOYEENO],
    		   [SOURCE].[dbo].[TBL_APPLICANT_HIRED].[LASTNAME],
    		   [SOURCE].[dbo].[TBL_APPLICANT_HIRED].[FIRSTNAME],
    		   [SOURCE].[dbo].[TBL_APPLICANT_HIRED].[MIDDLENAME],
    		   [SOURCE].[dbo].[TBL_APPLICANT_HIRED].[MIDDLEINITIAL],
    		   [SOURCE].[dbo].[TBL_APPLICANT_HIRED].[SUFFIXNAME],
    		   [SOURCE].[dbo].[TBL_APPLICANT_HIRED].[DATEHIRED],
    		   [SOURCE].[dbo].[TBL_APPLICANT_HIRED].[DEPARTMENT]
    	   FROM [SOURCE].[dbo].[TBL_APPLICANT_HIRED]
    	 WHERE [SOURCE].[dbo].[TBL_APPLICANT_HIRED].[FLAG] = 'PENDING'
    
            UPDATE....

  2. #2
    MS SQL Powerposter szlamany's Avatar
    Join Date
    Mar 2004
    Location
    CT
    Posts
    17,867

    Re: Sql server 2008 - how to create stored procedure?

    I see you have an INSERT and an UPDATE statement - no stored procedure.

    Here is an example of a simple SPROC to either INSERT or UPDATE a record

    Code:
    Create Procedure awc_TaskTasks_Occupant_Save @TaskId varchar(100), @Task varchar(100), @username varchar(100)
    				,@PopupKey varchar(100)
    				,@EmpNo varchar(100)
    				,@Occupant varchar(1000)
    As
    Set NoCount On
    Declare @UserId int
    Set @UserId=(Select UserId From User_T Where Username=@username)
    
    If @PopupKey='~add~'
    Begin
    	Insert into Activity_T values (@TaskId, 'Occupant', '', @EmpNo, '', GetDate())
    	Set @PopupKey=SCOPE_IDENTITY()
    	Insert into TaskOccupant_T values (@PopupKey, @Occupant, '', '', GetDate())
    End
    Else
    Begin
    	Update TaskOccupant_T Set Occupant=@Occupant
    		Where ActId=@PopupKey
    End

    *** Read the sticky in the DB forum about how to get your question answered quickly!! ***

    Please remember to rate posts! Rate any post you find helpful - even in old threads! Use the link to the left - "Rate this Post".

    Some Informative Links:
    [ SQL Rules to Live By ] [ Reserved SQL keywords ] [ When to use INDEX HINTS! ] [ Passing Multi-item Parameters to STORED PROCEDURES ]
    [ Solution to non-domain Windows Authentication ] [ Crazy things we do to shrink log files ] [ SQL 2005 Features ] [ Loading Pictures from DB ]

    MS MVP 2006, 2007, 2008

  3. #3

    Thread Starter
    Fanatic Member aNubies's Avatar
    Join Date
    Aug 2008
    Posts
    558

    Re: Sql server 2008 - how to create stored procedure?

    Is there a way where after inserting one data, I can store the Id or something to a variable so that I know what data I needed to update?

  4. #4
    MS SQL Powerposter szlamany's Avatar
    Join Date
    Mar 2004
    Location
    CT
    Posts
    17,867

    Re: Sql server 2008 - how to create stored procedure?

    If you put a SELECT as the last item in the SPROC and use the executescalar method to run the SPROC - you will get whatever you have in that SELECT back.

    Like mine could end with

    "Select @PopupKey"

    and that way in the event that the first IF runs it "sets" the @PopupKey variable to the ID of the record inserted and that value will be returned to the calling code in VB.

    *** Read the sticky in the DB forum about how to get your question answered quickly!! ***

    Please remember to rate posts! Rate any post you find helpful - even in old threads! Use the link to the left - "Rate this Post".

    Some Informative Links:
    [ SQL Rules to Live By ] [ Reserved SQL keywords ] [ When to use INDEX HINTS! ] [ Passing Multi-item Parameters to STORED PROCEDURES ]
    [ Solution to non-domain Windows Authentication ] [ Crazy things we do to shrink log files ] [ SQL 2005 Features ] [ Loading Pictures from DB ]

    MS MVP 2006, 2007, 2008

  5. #5
    Super Moderator FunkyDexter's Avatar
    Join Date
    Apr 2005
    Location
    An obscure body in the SK system. The inhabitants call it Earth
    Posts
    7,502

    Re: Sql server 2008 - how to create stored procedure?

    Is there a way where after inserting one data, I can store the Id or something to a variable so that I know what data I needed to update?
    If you're inserting a single record and the target table has an identity column as the primary key then the newly generated value can be retrieved by calling Scope_Identity()

    However, I think you're inserting lots of rows here, correct? Scope_Identity isn't going to help you with that. And I'm not sure you need to anyway. As long as you're inside a transaction the Query you're inserting from will return the same results both before and after the insert so you should just be able to use the same Where clause for your update as you do for your insert.

    If you want to avoid transactions then I would insert the ids of the records you're going to Insert/Update into a temp table first and then base both the Insert and Update on that.
    You can depend upon the Americans to do the right thing. But only after they have exhausted every other possibility - Winston Churchill

    Hadoop actually sounds more like the way they greet each other in Yorkshire - Inferrd

  6. #6
    MS SQL Powerposter szlamany's Avatar
    Join Date
    Mar 2004
    Location
    CT
    Posts
    17,867

    Re: Sql server 2008 - how to create stored procedure?

    Seeing FD's post seems like you are doing something bigger here. Please explain the details of your requirement from start to finish.

    *** Read the sticky in the DB forum about how to get your question answered quickly!! ***

    Please remember to rate posts! Rate any post you find helpful - even in old threads! Use the link to the left - "Rate this Post".

    Some Informative Links:
    [ SQL Rules to Live By ] [ Reserved SQL keywords ] [ When to use INDEX HINTS! ] [ Passing Multi-item Parameters to STORED PROCEDURES ]
    [ Solution to non-domain Windows Authentication ] [ Crazy things we do to shrink log files ] [ SQL 2005 Features ] [ Loading Pictures from DB ]

    MS MVP 2006, 2007, 2008

  7. #7

    Thread Starter
    Fanatic Member aNubies's Avatar
    Join Date
    Aug 2008
    Posts
    558

    Re: Sql server 2008 - how to create stored procedure?

    I have this table wherein all the hired applicant will be stored temporarily if the day they hired is not in the given payroll time. So for the users don't needed to check all the applicants and transfer them the master table.

    The stored procedure will be schedule at a given time twice a month so it will transfer the data automatically. So the number of record would be more than one(1) in time. This could be a batch writing.

    So that's the reason I need to create an Insert and at the same time update a column to label the data is successfully transferred already.

  8. #8
    Super Moderator FunkyDexter's Avatar
    Join Date
    Apr 2005
    Location
    An obscure body in the SK system. The inhabitants call it Earth
    Posts
    7,502

    Re: Sql server 2008 - how to create stored procedure?

    Then I'd go with either a temp table or a transaction.

    Here's roughly what the transaction approach would look like:-
    Code:
    Begin Transaction
    INSERT INTO [DESTINATION].[dbo].[EMPLOYEES]
    	       (EMPLOYEENO, LASTNAME, FIRSTNAME, MIDDLENAME, MIDDLEINITIAL, SUFFIXNAME, DATEHIRED, DEPARTMENT)
    	SELECT [SOURCE].[dbo].[TBL_APPLICANT_HIRED].[EMPLOYEENO],
    		   [SOURCE].[dbo].[TBL_APPLICANT_HIRED].[LASTNAME],
    		   [SOURCE].[dbo].[TBL_APPLICANT_HIRED].[FIRSTNAME],
    		   [SOURCE].[dbo].[TBL_APPLICANT_HIRED].[MIDDLENAME],
    		   [SOURCE].[dbo].[TBL_APPLICANT_HIRED].[MIDDLEINITIAL],
    		   [SOURCE].[dbo].[TBL_APPLICANT_HIRED].[SUFFIXNAME],
    		   [SOURCE].[dbo].[TBL_APPLICANT_HIRED].[DATEHIRED],
    		   [SOURCE].[dbo].[TBL_APPLICANT_HIRED].[DEPARTMENT]
    	   FROM [SOURCE].[dbo].[TBL_APPLICANT_HIRED]
    	 WHERE [SOURCE].[dbo].[TBL_APPLICANT_HIRED].[FLAG] = 'PENDING'
    
            UPDATE [SOURCE].[dbo].[TBL_APPLICANT_HIRED]
            SET [SOURCE].[dbo].[TBL_APPLICANT_HIRED].[FLAG] = 'PROCESSED',
                  [SOURCE].[dbo].[TBL_APPLICANT_HIRED].[SomeDateColumn]= GetDate()
    	 WHERE [SOURCE].[dbo].[TBL_APPLICANT_HIRED].[FLAG] = 'PENDING'
    Commit Transaction
    That where clause will return the same set of record for the update as it does for the insert.


    If this is processing a large amount of data at a busy time then a transaction might not be the best option because it's going to lock the Employees and the Applicant Hired databases for the entire duration. If that's an issue then pre-populate a temp table.
    You can depend upon the Americans to do the right thing. But only after they have exhausted every other possibility - Winston Churchill

    Hadoop actually sounds more like the way they greet each other in Yorkshire - Inferrd

  9. #9

    Thread Starter
    Fanatic Member aNubies's Avatar
    Join Date
    Aug 2008
    Posts
    558

    Re: Sql server 2008 - how to create stored procedure?

    So this is my whole code now.

    Code:
    -- ================================================
    -- Template generated from Template Explorer using:
    -- Create Procedure (New Menu).SQL
    --
    -- Use the Specify Values for Template Parameters 
    -- command (Ctrl-Shift-M) to fill in the parameter 
    -- values below.
    --
    -- This block of comments will not be included in
    -- the definition of the procedure.
    -- ================================================
    SET ANSI_NULLS ON
    GO
    SET QUOTED_IDENTIFIER ON
    GO
    -- =============================================
    -- Author:		eTrends
    -- Create date: 
    -- Description:	Transfer hired applicant to payroll master.
    -- =============================================
    CREATE PROCEDURE eTrends 
    	-- Add the parameters for the stored procedure here
    	@p1 int = 0, 
    	@p2 int = 0
    AS
    BEGIN
    	-- SET NOCOUNT ON added to prevent extra result sets from
    	-- interfering with SELECT statements.
    	SET NOCOUNT ON;
    
        -- Insert statements for procedure here
        BEGIN TRANSACTION
    	INSERT INTO [DESTINATION].[dbo].[EMPLOYEES]
    	       (EMPLOYEENO, LASTNAME, FIRSTNAME, MIDDLENAME, MIDDLEINITIAL, SUFFIXNAME, DATEHIRED, DEPARTMENT)
    	SELECT [SOURCE].[dbo].[TBL_APPLICANT_HIRED].[EMPLOYEENO],
    		   [SOURCE].[dbo].[TBL_APPLICANT_HIRED].[LASTNAME],
    		   [SOURCE].[dbo].[TBL_APPLICANT_HIRED].[FIRSTNAME],
    		   [SOURCE].[dbo].[TBL_APPLICANT_HIRED].[MIDDLENAME],
    		   [SOURCE].[dbo].[TBL_APPLICANT_HIRED].[MIDDLEINITIAL],
    		   [SOURCE].[dbo].[TBL_APPLICANT_HIRED].[SUFFIXNAME],
    		   [SOURCE].[dbo].[TBL_APPLICANT_HIRED].[DATEHIRED],
    		   [SOURCE].[dbo].[TBL_APPLICANT_HIRED].[DEPARTMENT]
    	  FROM [SOURCE].[dbo].[TBL_APPLICANT_HIRED]
    	 WHERE [SOURCE].[dbo].[TBL_APPLICANT_HIRED].[FLAG] = 'PENDING'
    	 
    	UPDATE [SOURCE].[dbo].[TBL_APPLICANT_HIRED]
    	   SET [SOURCE].[dbo].[TBL_APPLICANT_HIRED].[FLAG] = 'PROCESSED'
    	 WHERE [SOURCE].[dbo].[TBL_APPLICANT_HIRED].[FLAG] = 'PENDING'
         COMMIT TRANSACTION
    END
    GO
    Do I have a correct syntax? and yeah, this could be execute at the same time during work hours. So what do you mean by pre-populating a temp table?
    Last edited by aNubies; Jun 1st, 2016 at 12:12 AM.

  10. #10
    Super Moderator FunkyDexter's Avatar
    Join Date
    Apr 2005
    Location
    An obscure body in the SK system. The inhabitants call it Earth
    Posts
    7,502

    Re: Sql server 2008 - how to create stored procedure?

    Do I have a correct syntax?
    That looks correct to me although it's easy to miss something so you should, of course, run it to make sure it's doing what you want. The one thing I notice is that you've got two parameters (@p1, @p2) that you're not using. You should probably remove them.

    So what do you mean by pre-populating a temp table?
    Big inserts and updates can take some time to run, particularly the update and particularly if there's no index on the Flag field. Because we've put this in a transaction, it's going to lock the relevant rows in the Employees and the Applicant Hired tables (in fact I suspect it'll probably lock the whole tables) until the whole operation is complete. If the operation only takes a second or so that's probably fine, but if it takes 30 seconds, a minute or more then your other users will be forced to wait for it to complete which is going to look a bit rubbish. So there's an alternative approach you might want to consider if this looks like it will run long.

    1. Create a temp table
    2. Insert the ids of the records you want to insert/update into it
    3. Insert based on a select that joins to the temp table
    4. Update based on a join to the temp table
    5. Drop the temp table

    Something like this (I'm typing straight in so watch for syntax errors):-
    Code:
    Create Table #MyEmployees (EmployeeNo int primary key)
    
    Insert Into #MyEmployees
    Select EmployeeNo
    From TBL_APPLICANT_HIRED
    Where FLAG = 'PENDING'
    
    Insert Into EMPLOYEES
    Select VariousFields
    From TBL_APPLICANT_HIRED AH
    Join #MyEmployees ME
       on AH.EMPLOYEENO = ME.EMPLOYEENO
    
    UPDATE AH
    SET FLAG = 'PROCESSED'
    From TBL_APPLICANT_HIRED AH
    Join #MyEmployees ME
       on AH.EMPLOYEENO = ME.EMPLOYEENO
    
    Drop Table #MyEmployees
    Note there's no transaction here. Instead of holding the records locked while it does the insert and update, it "pre-selects" the records it's going to work with. Overall this will take slightly longer to run but it will spend less time blocking resources that other processes might be waiting to get at.
    Last edited by FunkyDexter; Jun 1st, 2016 at 02:18 AM.
    You can depend upon the Americans to do the right thing. But only after they have exhausted every other possibility - Winston Churchill

    Hadoop actually sounds more like the way they greet each other in Yorkshire - Inferrd

  11. #11

    Thread Starter
    Fanatic Member aNubies's Avatar
    Join Date
    Aug 2008
    Posts
    558

    Re: Sql server 2008 - how to create stored procedure?

    Thank you very much for pointing it out to me. And I tested my code with some additional and it works well.

    Thank's man.

Posting Permissions

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



Featured


Click Here to Expand Forum to Full Width