samedi 11 juin 2016

Creating a stored procedure that will do INSERT, UPDATE or DELETE statements in a database

I know that this will be very vague to most of you, but after my first question on stackoverflow I've decided to ask you for help in creating a stored procedure that will modify this table(sorry for generalized format):

Table1(
     ColumnID int not null,  --autoincremented
     ForeignKey1 int,
     ForeignKey2 int,
     ForeignKey3 int,
     ForeignKey4 int,
     Column1 varchar,
     Column2 varchar,
     Column3 nvarchar,
     PRIMARY KEY (ColumnID)
)

The stored procedure I had in mind was something like this:

CREATE PROCEDURE usp_ModifyTable
     @statementType varchar, --auxilliary variable used only in procedure
     @paramFK1 int,
     @paramFK2 int,
     @paramFK3 int,
     @paramFK4 int,
     @paramCol1 varchar, -- has UNIQUE constraint
     @paramCol2 varchar, -- has UNIQUE constraint
     @paramCol3 nvarchar
AS
BEGIN
     IF @statementType = 'Insert'
     BEGIN
          INSERT INTO Table1(
ForeignKey1,ForeignKey2,ForeignKey3,ForeignKey4,Column1,Column2,Column3)
          VALUES(
@paramFK1,@paramFK2,@paramFK3,@paramFK4,@paramCol1,@paramCol2,@paramCol3)
     END

     IF @statementType = 'Update'
     BEGIN
          UPDATE Table1
          SET ForeignKey1 = @paramFK1, ForeignKey2 = @paramFK2,
ForeignKey3 = @paramFK3,ForeignKey4 = @paramFK4,Column1 = @paramCol1,
Column2 = @paramCol2,Column3 = @paramCol3
          WHERE Column1 = @paramCol1 OR Column2 = @paramCol2
     END
     IF @statementType = 'Delete'
     BEGIN
          DELETE FROM Table1
          WHERE Column1 = @paramCol1 OR Column2 = @paramCol2
     END

Now for my problems: 1. Should I add parameter for ColumnID? If so, will it change something in procedure(like requesting a ID when Insert even if it's Identity)?
2. Will there be complications if I place some parameters in IFs(like DECLARE @param)?

Aucun commentaire:

Enregistrer un commentaire