Page 1 of 2 12 LastLast
Results 1 to 15 of 17
  1. #1
    Join Date
    Feb 2004
    Location
    Alpine Califormia
    Posts
    1,789

    Unanswered: Appending Stored Procedure

    I need to make sure I'm doing this correctly can you help me out guys please?? This is an Appending Stored procedure it should move values from the EmployeeGamingLicense table when the status is turned into TERMINATED to the GCEmployeeTerms table. Heres what I have so far, having problems with the rest of the script getting errors


    Code:
    CREATE PROCEDURE [insert_GCEmployeeTerms_1]
    	(@TM_#_1 	[int],
    	 @FirstName_2 	[nvarchar](50),
    	 @LastName_3 	[nvarchar](50),
    	 @SocialSecurityNumber_4 	[int],
    	 @DateHired_5 	[datetime],
    	 @Status_6 	[nvarchar](50),
    	 @TerminationDate_7 	[datetime],
    	 @Title_8 	[nvarchar](50),
    	 @DepartmentName_9 	[nvarchar](50),
    	 @TermReason_10 	[ntext],
    	 @VoluntaryInvoluntary_11 	[nvarchar](50))
    
    AS INSERT INTO [CommissionEmployee_Exclusionsdb].[dbo].[GCEmployeeTerms] 
    	 ( [TM #],
    	 [FirstName],
    	 [LastName],
    	 [SocialSecurityNumber],
    	 [DateHired],
    	 [Status],
    	 [TerminationDate],
    	 [Title],
    	 [DepartmentName],
    	 [TermReason],
    	 [VoluntaryInvoluntary]) 
     
    SELECT 
    	( @TM_#,
    	 @FirstName,
    	 @LastName,
    	 @SocialSecurityNumber,
    	 @DateHired,
    	 @Status,
    	 @TerminationDate,
    	 @Title,
    	 @DepartmentName,
    	 @TermReason,
    	 @VoluntaryInvoluntary)
    FROM EmployeeGamingLicense
    WHERE STATUS = 'TERMINATED'
    GO

  2. #2
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Methinks thou wants not both tables and variables in yon SELECT list. There be dragons in that mix!

    -PatP

  3. #3
    Join Date
    Feb 2004
    Location
    Alpine Califormia
    Posts
    1,789

    LoL

    Thank You pat for that very old English/shakesperan/King Arthur answer..lol. Let me see if I interpreted that correctly...Your saying that my select list is incorrect, refering of both tables...I need to go look it up..sorry this is my first append stored procedure.

  4. #4
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    If you stop and think about it, you are passing a whole flock (that would be a really technical term, eh?) of parameters to the procedure. This would be a good thing if you wanted to insert those parameters one time. You are also using a table with a where clause, which would be a good thing if you wanted to insert columns from that table for every row that qualified. However, doing both means that you want to insert one copy of the parameters that you are passing for every row that qualifies... Which is probably NOT what you intended!

    You probably want to either use the table with column names, or the parameters without the table, but only one or the other!

    -PatP

  5. #5
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    I'm just being nosy now, but did you ever get this one sorted out to your satisfaction?

    -PatP

  6. #6
    Join Date
    Feb 2004
    Location
    Alpine Califormia
    Posts
    1,789

    :(

    Sniff Sniff Sniff ...No

    I just want to insert data from one table to another automatically through stored procedures. I can make part of the procedure (that Inserts), need to tell it to pull from another table...I dont know, keep getting errors its like a puzzle I can only get part of it but not all (Banging head against wall)

  7. #7
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,800
    Provided Answers: 11
    Quote Originally Posted by Pat Phelan
    a whole flock of parameters
    Pat. I thought it was a bevy of parameters. Hmm. May need to add it to the lists...

    http://encyclopedia.thefreedictionar...e%20term%20A-K

  8. #8
    Join Date
    Feb 2004
    Location
    Alpine Califormia
    Posts
    1,789

    ok

    ok so your saying its just a bunch of parameters..a horde of parameters

  9. #9
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Quote Originally Posted by desireemm
    Sniff Sniff Sniff ...No
    Now, now M'Dear! You musn't get too upset. Might I suggest trying something like:
    Code:
    CREATE PROCEDURE [insert_GCEmployeeTerms_1]
    AS
    
    INSERT INTO [CommissionEmployee_Exclusionsdb].[dbo].[GCEmployeeTerms] (
       [TM #],  [FirstName],  [LastName]
    ,  [SocialSecurityNumber],  [DateHired],  [Status]
    ,  [TerminationDate],  [Title],  [DepartmentName]
    ,  [TermReason],  [VoluntaryInvoluntary]
    ) SELECT
       a.TM_#,  a.FirstName,  a.LastName
    ,  a.SocialSecurityNumber,  a.DateHired,  a.Status
    ,  a.TerminationDate,  a.Title,  a.DepartmentName
    ,  a.TermReason,  a.VoluntaryInvoluntary)
       FROM EmployeeGamingLicense AS a
       WHERE a.STATUS = 'TERMINATED'
    
    RETURN
    GO
    -PatP

  10. #10
    Join Date
    Feb 2004
    Location
    Alpine Califormia
    Posts
    1,789

    Yes!!!!!!!!!!!!!!!

    I did what you said pat I got some Errors but what you put up gave me a great start I was not Referring to the Table.Fields that will do the inserting for me Fields in that Table are not exactly the same..(the column names) OK I GET IT A LIGHT HAS GONE OFF AND I TOTALLY GET IT NOW..Now I see why SQL was like WHAT THE HELL ARE YOU TALKING ABOUT..OMG I Die a Happy Woman now...Thank you so much Pat Your Truly a SQL GOD..I totally Get it now.


    THANK YOU

  11. #11
    Join Date
    Feb 2004
    Location
    Alpine Califormia
    Posts
    1,789

    Oh yes

    and Mccowley was correct I see his point now

  12. #12
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Now that you grok how this works, you'll need to think a bit about how to limit the carnage. You won't always want to turn that procedure loose to copy every terminated employee row, or you'll end up with a gazillion copies of each row in GCEmployeeTerms (one for every time the procedure is run while that terminated row is present), which probably isn't what you want!

    -PatP

  13. #13
    Join Date
    Feb 2004
    Location
    Alpine Califormia
    Posts
    1,789

    Ok

    Ok...what happens is our dept gets a list of terminated employees, so when we get this list we go to the [GamingCommissiondb].[dbo].[EmployeeGamingLicense] which is the active table look up the employee and change their status to 'TERMINATED'. Now there can be as much as 20-30 terminations. The Stored Procedure will then take those employees that have been changed to Terminated and APPEND (Insert) them into the TERMINATION.tbl. One of the purposes of doing this is if the employee comes back as a rehire we can check to see if there was any negative action taken against their gaming license. Does that make sense??

  14. #14
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Oh heavens yes, but unless you delete the row from the active table when this happens, that row will be copied every time the procedure runs (at least as it is written right now). The only criteria that you are using to select rows is if they are flagged as terminated... You might want to be a wee bit more selective than that, maybe throwing in a test on the termination date or something wild and crazy like that!

    -PatP

  15. #15
    Join Date
    Feb 2004
    Location
    Alpine Califormia
    Posts
    1,789

    Ok

    I understand what your saying, I dont want my users to delete a record in error so I didnt want to delet the record from the active table through the stored procedure. I have a view that shows only the terminated employees from the active table that I look at everyone 2 weeks, just a QC thing. also both the tables have the TM# as a primary key, the first time I tried to execute the stored procedure I got "Pk constraint error duplicates"..something like that, so once I found out what records had already been appended to the TERMINATION table and which ones had not been the SP executed successfully. Does that make sense?

Posting Permissions

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