-
May 31st, 2016, 04:36 AM
#1
Thread Starter
Fanatic Member
[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....
-
May 31st, 2016, 05:08 AM
#2
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
-
May 31st, 2016, 05:18 AM
#3
Thread Starter
Fanatic Member
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?
-
May 31st, 2016, 05:42 AM
#4
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.
-
May 31st, 2016, 05:47 AM
#5
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.
The best argument against democracy is a five minute conversation with the average voter - Winston Churchill
Hadoop actually sounds more like the way they greet each other in Yorkshire - Inferrd
-
May 31st, 2016, 05:53 AM
#6
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.
-
May 31st, 2016, 06:31 AM
#7
Thread Starter
Fanatic Member
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.
-
May 31st, 2016, 06:51 AM
#8
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.
The best argument against democracy is a five minute conversation with the average voter - Winston Churchill
Hadoop actually sounds more like the way they greet each other in Yorkshire - Inferrd
-
May 31st, 2016, 10:26 PM
#9
Thread Starter
Fanatic Member
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.
-
Jun 1st, 2016, 02:15 AM
#10
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.
The best argument against democracy is a five minute conversation with the average voter - Winston Churchill
Hadoop actually sounds more like the way they greet each other in Yorkshire - Inferrd
-
Jun 1st, 2016, 03:15 AM
#11
Thread Starter
Fanatic Member
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
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|