I have an sql table that consist of three key constraints
Policy_Nbr, Trans_CodeOrig, Trans_Eff_Date. Now I need to create a stored procedure that will handle
violations on the table. What I mean here is that the old values(three key constraints) when I do an update on the table should
be included in the stored procedure. I must add them in. Here is the stored procedure I am working on.
Now I am need to include three parameters.
ExecOnTransOverride.upd(dr.Item(1), dr.Item(2), dr.Item(3), dr.Item(4), dr.Item(5), dr.Item(6), dr.Item(8), dr.Item(7), DateTime.Now, dr.Item(7), dr.Item(9),
oldPolicyNumber, oldTransCode, oldTransEffDate)
************ ********** ************
CREATE PROCEDURE dbo.prc_TransOverride_upd
(
@Policy_Nbr varchar(7),
@Trans_CodeOrig varchar(6),
@Trans_Eff_Date datetime,
- Three parameters added here
---------------------------------------------------
@Policy_Nbr_Old varchar(7),
@Trans_CodeOrig_Old varchar(6),
@Trans_Eff_Date_Old datetime,
---------------------------------------------------
@Override_Code varchar(2),
@NR_CodeOvr varchar(1),
@Trans_CodeOvr varchar(6),
@CreatedDate datetime,
@CreatedUID nvarchar(48),
@ModifiedDate datetime,
@ModifiedUID nvarchar(48),
@Plan_Year int
)
As
BEGIN
DECLARE @Err Int
UPDATE [TransOverride]
Set
[Policy_Nbr] = @Policy_Nbr,
[Trans_CodeOrig] = @Trans_CodeOrig,
[Trans_Eff_Date] = @Trans_Eff_Date,
[Override_Code] = @Override_Code,
[NR_CodeOvr] = @NR_CodeOvr,
[Trans_CodeOvr] = @Trans_CodeOvr,
[CreatedDate] = @CreatedDate,
[CreatedUID] = @CreatedUID,
[ModifiedDate] = @ModifiedDate,
[ModifiedUID] = @ModifiedUID,
[Plan_Year] = @Plan_Year
WHERE
[Policy_Nbr] = @Policy_Nbr AND
[Trans_CodeOrig] = @Trans_CodeOrig AND
[Trans_Eff_Date] = @Trans_Eff_Date
--------------- Now I must add these three parameters in my where clause so the update does not violate
--------------- the key constraints on the table. See below table for reference.
Set @Err = @@Error
RETURN @Err
End
CREATE TABLE [TransOverride] (
[Policy_Nbr] [varchar] (7) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[Trans_CodeOrig] [varchar] (6) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[Trans_Eff_Date] [datetime] NOT NULL ,
[Override_Code] [varchar] (2) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[NR_CodeOvr] [varchar] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Trans_CodeOvr] [varchar] (6) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[CreatedDate] [datetime] NOT NULL CONSTRAINT [DF__TransOver__Creat__70A8B9AE] DEFAULT (getdate()),
[CreatedUID] [nvarchar] (48) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[ModifiedDate] [datetime] NOT NULL CONSTRAINT [DF__TransOver__Modif__719CDDE7] DEFAULT (getdate()),
[ModifiedUID] [nvarchar] (48) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Plan_Year] [int] NOT NULL CONSTRAINT [DF__TransOver__Plan___56757D0D] DEFAULT (2004),
CONSTRAINT [cnTransOverride_PK] PRIMARY KEY CLUSTERED
(
[Policy_Nbr],
[Trans_CodeOrig],
[Trans_Eff_Date]
) ON [PRIMARY] ,
CONSTRAINT [cnTransOverride_NR_CodeOvr_CK] CHECK ([NR_CodeOvr] = '' or ([NR_CodeOvr] = 'R' or [NR_CodeOvr] = 'N')),
CONSTRAINT [cnTransOverride_Override_Code_CK] CHECK ([Override_Code] = '' or ([Override_Code] = 'OT' or ([Override_Code] = 'AU' or ([Override_Code] = 'MS' or ([Override_Code] = 'EN' or ([Override_Code] = 'AO' or [Override_Code] = 'AC'))))))
) ON [PRIMARY]
GO
Now here is my ado.net function update that will call the above stored procedure
passing the parameters.
Public Shared Function upd( _
ByVal strPolicy_Nbr As Object, _
ByVal strTrans_CodeOrig As Object, _
ByVal dteTrans_Eff_Date As Object, _
ByVal varOverride_Code As Object, _
ByVal varNR_CodeOvr As Object, _
ByVal varTrans_CodeOvr As Object, _
ByVal dteCreatedDate As Object, _
ByVal strCreatedUID As Object, _
ByVal dteModifiedDate As Object, _
ByVal varModifiedUID As Object, _
ByVal lngPlan_Year As Object, _
ByVal strPolicy_Nbr_old As Object, _
ByVal strTrans_CodeOrig_old As Object, _
ByVal dteTrans_Eff_Date_old As Object _
) As Object
' construct new connection And command objects
Dim conn As SqlConnection = GetConn()
Dim cmd As SqlCommand = GetSprocCmd("prc_TransOverride_upd", conn)
Dim param As SqlParameter
' add return value param
param = New SqlParameter("@RETURN_VALUE", SqlDbType.Int)
param.Direction = ParameterDirection.ReturnValue
cmd.Parameters.Add(param)
' add params
' parameter for Policy_Nbr column
param = New SqlParameter("@Policy_Nbr", System.Data.SqlDbType.VarChar, 7)
param.Direction = ParameterDirection.Input
param.Value = strPolicy_Nbr
cmd.Parameters.Add(param)
' parameter for Trans_CodeOrig column
param = New SqlParameter("@Trans_CodeOrig", System.Data.SqlDbType.VarChar, 6)
param.Direction = ParameterDirection.Input
param.Value = strTrans_CodeOrig
cmd.Parameters.Add(param)
' parameter for Trans_Eff_Date column
param = New SqlParameter("@Trans_Eff_Date", System.Data.SqlDbType.DateTime, 8)
param.Direction = ParameterDirection.Input
param.Value = dteTrans_Eff_Date
cmd.Parameters.Add(param)
' The addition of three parameters
' The addition of three
' add params
' parameter for Policy_Nbr column
param = New SqlParameter("@Policy_Nbr_Old", System.Data.SqlDbType.VarChar, 7)
param.Direction = ParameterDirection.Input
param.Value = strPolicy_Nbr_old
cmd.Parameters.Add(param)
' parameter for Trans_CodeOrig column
param = New SqlParameter("@Trans_CodeOrig_Old", System.Data.SqlDbType.VarChar, 6)
param.Direction = ParameterDirection.Input
param.Value = strTrans_CodeOrig_old
cmd.Parameters.Add(param)
' parameter for Trans_Eff_Date column
param = New SqlParameter("@Trans_Eff_Date_Old", System.Data.SqlDbType.DateTime, 8)
param.Direction = ParameterDirection.Input
param.Value = dteTrans_Eff_Date_old
cmd.Parameters.Add(param)
' parameter for Override_Code column
param = New SqlParameter("@Override_Code", System.Data.SqlDbType.VarChar, 2)
param.Direction = ParameterDirection.Input
param.Value = varOverride_Code
cmd.Parameters.Add(param)
' parameter for NR_CodeOvr column
param = New SqlParameter("@NR_CodeOvr", System.Data.SqlDbType.VarChar, 1)
param.Direction = ParameterDirection.Input
param.Value = varNR_CodeOvr
cmd.Parameters.Add(param)
' parameter for Trans_CodeOvr column
param = New SqlParameter("@Trans_CodeOvr", System.Data.SqlDbType.VarChar, 6)
param.Direction = ParameterDirection.Input
param.Value = varTrans_CodeOvr
cmd.Parameters.Add(param)
' parameter for CreatedDate column
param = New SqlParameter("@CreatedDate", System.Data.SqlDbType.DateTime, 8)
param.Direction = ParameterDirection.Input
param.Value = dteCreatedDate
cmd.Parameters.Add(param)
' parameter for CreatedUID column
param = New SqlParameter("@CreatedUID", System.Data.SqlDbType.NVarChar, 48)
param.Direction = ParameterDirection.Input
param.Value = strCreatedUID
cmd.Parameters.Add(param)
' parameter for ModifiedDate column
param = New SqlParameter("@ModifiedDate", System.Data.SqlDbType.DateTime, 8)
param.Direction = ParameterDirection.Input
param.Value = dteModifiedDate
cmd.Parameters.Add(param)
' parameter for ModifiedUID column
param = New SqlParameter("@ModifiedUID", System.Data.SqlDbType.NVarChar, 48)
param.Direction = ParameterDirection.Input
param.Value = varModifiedUID
cmd.Parameters.Add(param)
' parameter for Plan_Year column
param = New SqlParameter("@Plan_Year", System.Data.SqlDbType.Int, 4)
param.Direction = ParameterDirection.Input
param.Value = lngPlan_Year
cmd.Parameters.Add(param)
' open connection
conn.Open()
' Execute command
cmd.ExecuteNonQuery()
' get return value
Dim result As Integer = GetSProcReturnValue(cmd)
' close connection
conn.Close()
Return result
End Function
Any help guys would be a thumbs up!