May 10, 2016 at 9:24 am
CREATE PROCEDURE spUpdate_PII
@SSN VARCHAR(20) = '999-99-9999',
@DOB Varchar(20) = 'January 1,2016',
@CaseNotePhone VarChar(20) = '111-111-1111',
@BodyReleaseAuthPhone Varchar(20)= '222-222-2222',
@rptByTelNo Varchar(20) = '333-333-3333',
@tagByPhone Varchar(20)= '444-444-44444',
@NOKPhone Varchar(20) = '555-555-5555',
@NOKHomePhone Varchar(20) = '666-666-6666',
@ResAddr Varchar(20) = 'tblDecedent.ResAddr',
@NOKadr Varchar(20) = 'tblNOK.NOKAddr',
@CaseNoteMemo Varchar(50)= 'This is Scrubbed Comment on the Active Phone Log'
AS
BEGIN TRANSACTION
BEGIN
SET NOCOUNT ON
UPDATE [dbo].[tblDeced SET SSN= @SSN,ResAddr = @ResAddr
UPDATE [dbo].[tblCal SET BirthDate = @DOB
UPDATE [dbo].[tblCal1 SET CalNotePhone = @CalNotePhone
UPDATE [dbo].[tblcall3 SET BodyReleaseAuthPhone = @BodyReleaseAuthPhone
UPDATE [dbo].[tblIntake] SET RptdByTelNo = @rptByTelNo
UPDATE [dbo].[tblIncident] SET TagByPhone = @tagByPhone
UPDATE [dbo].[tblNOK] SET NOKPhone = @NOKPhone,NOKHomePhone = @NOKHomePhone,NOKAddr= @NOKadr
UPDATE [dbo].[tblCaseNotes] SET CaseNoteMemo = @CaseNoteMemo
COMMIT TRANSACTION
END
this procedure should be scheduled on sql server agent job and run nightly to delete those columns before report is being run.
is there a way that the script will have one UPDATE instead of five or more and second,
how can data being not hard coded on the procedure?
thanks
May 10, 2016 at 9:44 am
Nassan (5/10/2016)
CREATE PROCEDURE spUpdate_PII@SSN VARCHAR(20) = '999-99-9999',
@DOB Varchar(20) = 'January 1,2016',
@CaseNotePhone VarChar(20) = '111-111-1111',
@BodyReleaseAuthPhone Varchar(20)= '222-222-2222',
@rptByTelNo Varchar(20) = '333-333-3333',
@tagByPhone Varchar(20)= '444-444-44444',
@NOKPhone Varchar(20) = '555-555-5555',
@NOKHomePhone Varchar(20) = '666-666-6666',
@ResAddr Varchar(20) = 'tblDecedent.ResAddr',
@NOKadr Varchar(20) = 'tblNOK.NOKAddr',
@CaseNoteMemo Varchar(50)= 'This is Scrubbed Comment on the Active Phone Log'
AS
BEGIN TRANSACTION
BEGIN
SET NOCOUNT ON
UPDATE [dbo].[tblDeced SET SSN= @SSN,ResAddr = @ResAddr
UPDATE [dbo].[tblCal SET BirthDate = @DOB
UPDATE [dbo].[tblCal1 SET CalNotePhone = @CalNotePhone
UPDATE [dbo].[tblcall3 SET BodyReleaseAuthPhone = @BodyReleaseAuthPhone
UPDATE [dbo].[tblIntake] SET RptdByTelNo = @rptByTelNo
UPDATE [dbo].[tblIncident] SET TagByPhone = @tagByPhone
UPDATE [dbo].[tblNOK] SET NOKPhone = @NOKPhone,NOKHomePhone = @NOKHomePhone,NOKAddr= @NOKadr
UPDATE [dbo].[tblCaseNotes] SET CaseNoteMemo = @CaseNoteMemo
COMMIT TRANSACTION
END
this procedure should be scheduled on sql server agent job and run nightly to delete those columns before report is being run.
is there a way that the script will have one UPDATE instead of five or more and second,
how can data being not hard coded on the procedure?
thanks
First, no, you are updating 8 different tables so you need 8 update statements.
Second, the values provided to the parameters are defaults if you don't provide values for one or more of the parameters.
May 10, 2016 at 9:59 am
There is no WHERE clause. This procedure will update all rows in the each column with the same value, which I'm sure this is not what you intend.
"Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho
May 10, 2016 at 10:03 am
the client want to just populate those values in the columns they provide me and they don't care if values are same.
thanks all.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply