February 15, 2015 at 10:54 pm
Hi ,
I have create Store procedure for all the Source and destination table for loading new and updated record.
For example I have below Store procedure
1-SP_Archive_using_merge_Fdoor
2-SP_Archive_using_merge_Fdoop
now I want to call all the sp on the basis of input like If filename is Fdoor then it shold fire the SP_Archive_using_merge_Fdoor , if file name is Fdoop then it shoilud fire the SP_Archive_using_merge_Fdoop like that .
below is the 2 sp .
--First SP
ALTER PROCEDURE [dbo].[SP_Archive_using_merge_Fdoor]
AS
BEGIN
SET NOCOUNT ON
DECLARE @Source_RowCount int
DECLARE @New_RowCount int
DECLARE @updated_Rowcount int
DECLARE @Matched_record int
DECLARE @TimeIn smalldatetime
DECLARE @LatestVersion int
SET NOCOUNT ON
----Fdoor--
SELECT @TimeIn = GETDATE()
SELECT @LatestVersion = 1
SELECT @Source_RowCount = COUNT(1) FROM ImportBBxFdoor ---To get source record count
MERGE Archive.dbo.ArchiveBBxFdoor AS TARGET
USING (SELECT *,
CAST(SUBSTRING(Col001, 1, 3) + SUBSTRING(Col002, 1, 3) + SUBSTRING(Col003, 1, 8) AS varchar(100)) BBxKey,
HASHBYTES('MD5', CAST(CHECKSUM(Col001, Col002, Col003, Col004) AS varchar(max))) RowChecksum
FROM dbo.ImportBBxFdoor) AS SOURCE
ON (SUBSTRING(SOURCE.Col001, 1, 3) + SUBSTRING(SOURCE.Col002, 1, 3) + SUBSTRING(SOURCE.Col003, 1, 8)) = target.BBxKey
AND (target.LatestVersion = 1 OR target.LatestVersion IS NULL)
WHEN MATCHED AND (source.BBxKey = Target.BBxKey AND source.RowChecksum <> TARGET.RowChecksum) THEN
UPDATE SET
TARGET.TimeIn = @TimeIn,
TARGET.BBXKey = SOURCE.BBXKey,
TARGET.RowChecksum = SOURCE.RowChecksum,
TARGET.Col001 = SOURCE.Col001,
TARGET.Col002 = SOURCE.Col002,
TARGET.Col003 = SOURCE.Col003,
TARGET.Col004 = SOURCE.Col004,
TARGET.LatestVersion = 0
WHEN NOT MATCHED THEN --For New record
INSERT (TimeIn, BBXKey, RowChecksum, Col001, Col002, Col003, Col004, LatestVersion)
VALUES (GETDATE(), SOURCE.BBXKey, SOURCE.RowChecksum, SOURCE.Col001, SOURCE.Col002, SOURCE.Col003, SOURCE.Col004, @LatestVersion);
SELECT @New_RowCount = @@ROWCOUNT --to get new inserfted count
END
Second SP
ALTER PROCEDURE [dbo].[b]SP_Archive_using_merge_Fdoop[/b]
AS
BEGIN
SET NOCOUNT ON
DECLARE @Source_RowCount int
DECLARE @New_RowCount int
DECLARE @updated_Rowcount int
DECLARE @Matched_record int
DECLARE @TimeIn smalldatetime
DECLARE @LatestVersion int
SET NOCOUNT ON
----Fdoor--
SELECT @TimeIn = GETDATE()
SELECT @LatestVersion = 1
SELECT @Source_RowCount = COUNT(1) FROM ImportBBxFdoop ---To get source record count
MERGE Archive.dbo.ArchiveBBxFdoop AS TARGET
USING (SELECT *,
CAST(SUBSTRING(Col001, 1, 3) + SUBSTRING(Col002, 1, 3) + SUBSTRING(Col003, 1, 8) AS varchar(100)) BBxKey,
HASHBYTES('MD5', CAST(CHECKSUM(Col001, Col002, Col003, Col004) AS varchar(max))) RowChecksum
FROM dbo.ImportBBxFdoop) AS SOURCE
ON (SUBSTRING(SOURCE.Col001, 1, 3) + SUBSTRING(SOURCE.Col002, 1, 3) + SUBSTRING(SOURCE.Col003, 1, 8)) = target.BBxKey
AND (target.LatestVersion = 1 OR target.LatestVersion IS NULL)
WHEN MATCHED AND (source.BBxKey = Target.BBxKey AND source.RowChecksum <> TARGET.RowChecksum) THEN
UPDATE SET
TARGET.TimeIn = @TimeIn,
TARGET.BBXKey = SOURCE.BBXKey,
TARGET.RowChecksum = SOURCE.RowChecksum,
TARGET.Col001 = SOURCE.Col001,
TARGET.Col002 = SOURCE.Col002,
TARGET.Col003 = SOURCE.Col003,
TARGET.Col004 = SOURCE.Col004,
TARGET.LatestVersion = 0
WHEN NOT MATCHED THEN --For New record
INSERT (TimeIn, BBXKey, RowChecksum, Col001, Col002, Col003, Col004, LatestVersion)
VALUES (GETDATE(), SOURCE.BBXKey, SOURCE.RowChecksum, SOURCE.Col001, SOURCE.Col002, SOURCE.Col003, SOURCE.Col004, @LatestVersion);
SELECT @New_RowCount = @@ROWCOUNT --to get new inserfted count
END
Please help me with the correct code
regards,
Vipin jha
February 15, 2015 at 11:37 pm
now I want to call all the sp on the basis of input like If filename is Fdoor then it shold fire the SP_Archive_using_merge_Fdoor , if file name is Fdoop then it shoilud fire the SP_Archive_using_merge_Fdoop like that .
IF @Filename = 'Fdoor'
BEGIN
EXEC SP_Archive_using_merge_Fdoor
END
ELSE
BEGIN
SP_Archive_using_merge_Fdoop
END
-- beginning stored procedures with SP_ is a bad idea. that's usually reserved for built-in stored procedures.
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply