Stored Procedure to Insert Data SQL Server

CREATE TABLE [dbo].[MASTER](
[BANK_CODE] [varchar](30) NULL,
[TRNX_UID] [float] NULL,
[CARD_TYPE] [varchar](30) NULL,
[DIR_FLAG] [varchar](30) NULL,
[ACQ_CODE] [varchar](30) NULL,
[ACQ_DESC] [varchar](200) NULL,
[ISS_CODE] [varchar](30) NULL,
[ISS_DESC] [varchar](200) NULL,
[TRNX_STATUS] [varchar](30) NULL,
[TRNX_TYPE] [varchar](30) NULL,
[TERMINAL_ID] [varchar](200) NULL,
[CARD_NO] [varchar](100) NULL,
[CURRENCY_CODE] [varchar](30) NULL,
[AMOUNT_FC] [numeric](20, 6) NULL,
[AMOUNT_LC] [numeric](20, 2) NULL,
[CURRENCY_RATE] [numeric](10, 6) NULL,
[TRNX_DATE] [datetime2](7) NULL,
[TRNX_TIME] [datetime2](7) NULL,
[REFERENCE_NO] [varchar](100) NULL,
[TRNX_MESSAGE] [varchar](4000) NULL,
[ACQ_COUNTRY] [varchar](30) NULL,
[REMARKS] [varchar](500) NULL,
[TIME_STAMP] [datetime2](7) NULL,
[ID$] [varbinary](16) NULL,
[IDENTITY_COL] [int] IDENTITY(1,1) NOT NULL
) ON [PRIMARY]

GO

— ======================================================================
— Author: Shadab Mohammad
— Create date: 05th May 2015
— Description: Stored Procedure to Insert Data in Master Table
— =======================================================================
CREATE Procedure [dbo].[InsertMaster]
(
@BANK_CODE varchar(30),
@TRNX_UID float,
@CARD_TYPE varchar(30),
@DIR_FLAG varchar(30),
@ACQ_CODE varchar(30),
@ACQ_DESC varchar(200),
@ISS_CODE varchar(30),
@ISS_DESC varchar(200),
@TRNX_STATUS varchar(30),
@TRNX_TYPE varchar(30),
@TERMINAL_ID varchar(200),
@CARD_NO varchar(100),
@CURRENCY_CODE varchar(30),
@AMOUNT_FC numeric(20, 6),
@AMOUNT_LC numeric(20, 2),
@CURRENCY_RATE numeric(10, 6),
@TRNX_DATE datetime2(7),
@TRNX_TIME datetime2(7),
@REFERENCE_NO varchar(100),
@TRNX_MESSAGE varchar(4000),
@ACQ_COUNTRY varchar(30),
@REMARKS varchar(500),
@TIME_STAMP datetime2(7)
)
As
Begin
Insert into MASTER (BANK_CODE,TRNX_UID,CARD_TYPE,DIR_FLAG,ACQ_CODE,ACQ_DESC,ISS_CODE,ISS_DESC,TRNX_STATUS,TRNX_TYPE,TERMINAL_ID,CARD_NO,CURRENCY_CODE,AMOUNT_FC,AMOUNT_LC,CURRENCY_RATE,TRNX_DATE,TRNX_TIME,REFERENCE_NO,TRNX_MESSAGE,ACQ_COUNTRY,REMARKS,TIME_STAMP)
Values(@BANK_CODE,@TRNX_UID,@CARD_TYPE,@DIR_FLAG,@ACQ_CODE,@ACQ_DESC,@ISS_CODE,@ISS_DESC,@TRNX_STATUS,@TRNX_TYPE,@TERMINAL_ID,@CARD_NO,@CURRENCY_CODE,@AMOUNT_FC,@AMOUNT_LC,@CURRENCY_RATE,@TRNX_DATE,@TRNX_TIME,@REFERENCE_NO,@TRNX_MESSAGE,@ACQ_COUNTRY,@REMARKS,@TIME_STAMP)
End

GO

— Execute the Stored Procedure

USE [dbname]
GO

DECLARE @return_value int

EXEC @return_value = [dbo].[InsertMaster]
@BANK_CODE = NULL,
@TRNX_UID = NULL,
@CARD_TYPE = NULL,
@DIR_FLAG = NULL,
@ACQ_CODE = NULL,
@ACQ_DESC = NULL,
@ISS_CODE = NULL,
@ISS_DESC = NULL,
@TRNX_STATUS = NULL,
@TRNX_TYPE = NULL,
@TERMINAL_ID = NULL,
@CARD_NO = NULL,
@CURRENCY_CODE = NULL,
@AMOUNT_FC = NULL,
@AMOUNT_LC = NULL,
@CURRENCY_RATE = NULL,
@TRNX_DATE = NULL,
@TRNX_TIME = NULL,
@REFERENCE_NO = NULL,
@TRNX_MESSAGE = NULL,
@ACQ_COUNTRY = NULL,
@REMARKS = NULL,
@TIME_STAMP = NULL

SELECT ‘Return Value’ = @return_value

GO

Advertisements