How to create Stored Procedure


This is example how to create stored procedure with parameter :

CREATE PROCEDURE [dbo].[sp_RPT_USG]
@FromDate datetime,
@ToDate datetime
AS
SELECT dbo.MR_OE_HEADER.MRN_NO, dbo.MR_OE_HEADER.P_REGISTER, dbo.MR_OE_HEADER.P_VISIT_NO, dbo.MR_SET_PATIENT_TYPE.PATIENT_TYPE_CD, dbo.MR_SET_CHARGE_TYPE.TYPE_CD, dbo.MR_P_REGISTRATION.P_NAME, dbo.MR_SET_CHARGES.ITEM_CD, dbo.MR_SET_CHARGES.ITEM_DESC, dbo.MR_SET_DEPT.DEPT_CD, dbo.MR_SET_DEPT.DEPT_DESC, dbo.MR_COMM_HEADER.VISIT_DT, dbo.MR_OE_DETAIL.DOCTOR_NO, dbo.MR_SET_DOCTOR.DOC_NM
FROM dbo.MR_OE_HEADER INNER JOIN
dbo.MR_OE_DETAIL ON dbo.MR_OE_HEADER.OE_HEADER_NO = dbo.MR_OE_DETAIL.OE_HEADER_NO INNER JOIN
dbo.MR_P_REGISTRATION ON dbo.MR_OE_HEADER.MRN_NO = dbo.MR_P_REGISTRATION.MRN_NO INNER JOIN
dbo.MR_P_REGISTRATION_DETAIL ON dbo.MR_P_REGISTRATION.MRN_NO = dbo.MR_P_REGISTRATION_DETAIL.MRN_NO INNER JOIN dbo.MR_SET_CHARGES ON dbo.MR_OE_DETAIL.CHARGE__NO = dbo.MR_SET_CHARGES.ITEM_NO INNER JOIN dbo.MR_SET_DEPT ON dbo.MR_OE_DETAIL.DEPT_NO = dbo.MR_SET_DEPT.DEPT_NO INNER JOIN
dbo.MR_COMM_HEADER ON dbo.MR_P_REGISTRATION.MRN_NO = dbo.MR_COMM_HEADER.MRN_NO INNER JOIN
dbo.MR_SET_PATIENT_TYPE ON dbo.MR_COMM_HEADER.PATIENT_TYPE = dbo.MR_SET_PATIENT_TYPE.PATIENT_TYPE_NO INNER JOIN dbo.MR_SET_CHARGE_TYPE ON dbo.MR_COMM_HEADER.CHARGE_TYPE = dbo.MR_SET_CHARGE_TYPE.CHARGE_TYPE_NO INNER JOIN dbo.MR_SET_DOCTOR ON dbo.MR_OE_DETAIL.DOCTOR_NO = dbo.MR_SET_DOCTOR.DOCTOR_NO
WHERE (dbo.MR_OE_DETAIL.CHARGE__NO = 860 OR dbo.MR_OE_DETAIL.CHARGE__NO = 861) and (MR_COMM_HEADER.VISIT_DT>=@FromDate and MR_COMM_HEADER.VISIT_DT<= @ToDate)
GO

"INSERT INTO" WITH DIFFERENT SERVER

INSERT INTO dbo.MR_SET_DOCTOR
SELECT SERVERA.HIMSINDBRWJ0708.dbo.base_doctor_maintenance.doc_serial_no, SERVERA.HIMSINDBRWJ0708.dbo.base_doctor_maintenance.doc_code, SERVERA.HIMSINDBRWJ0708.dbo.base_doctor_maintenance.disp_name, SERVERA.HIMSINDBRWJ0708.dbo.base_doctor_maintenance.employee_serial_no, SERVERA.HIMSINDBRWJ0708.dbo.base_doctor_maintenance.EDATE, SERVERA.HIMSINDBRWJ0708.dbo.base_doctor_maintenance.last_modified
FROM SERVERA.HIMSINDBRWJ0708.dbo.base_doctor_maintenance LEFT OUTER JOIN
dbo.MR_SET_DOCTOR ON SERVERA.HIMSINDBRWJ0708.dbo.base_doctor_maintenance.doc_serial_no = dbo.MR_SET_DOCTOR.DOCTOR_NO
WHERE (dbo.MR_SET_DOCTOR.DOCTOR_NO IS NULL)

Note :
- Both server must be registered in sysservers table at master database on each server.