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