條件判斷insert ,update,delete.

CREATE PROCEDURE dbo.up_UpdatePublisherName
(
@pub_id char(4),
@pub_name varchar(40),
@Original_pub_name varchar(40)
)
AS
if exists(select pub_id
　from publishers
where (pub_id = @pub_id) AND (pub_name = @Original_pub_name))
Begin
　UPDATE publishers SET pub_name = @pub_name
　WHERE (pub_id = @pub_id)
End
RETURN

alter PROCEDURE vendor_info_delete
@Vendor_Code varchar(50)
AS
IF EXISTS (SELECT * from vendor where Vendor_Code=@Vendor_Code)
delete from Vendor where (Vendor_Code=@Vendor_Code)         

return

set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go

 

ALTER PROCEDURE [dbo].[vendor_info_insert]
@Vendor_Class_ID int=null,
@Vendor_Code varchar(50),
@Vendor_Name varchar(50),
@Simple_Spell varchar(50)
AS

IF not EXISTS (SELECT * from vendor where Vendor_Code=@Vendor_Code)
INSERT INTO Vendor
          (Vendor_Class_ID
           ,Vendor_Code
           ,Vendor_Name
           ,Simple_Spell)
     VALUES
           (@Vendor_Class_ID
           ,@Vendor_Code
           ,@Vendor_Name
           ,@Simple_Spell)
return


Dim myda As System.Data.SqlClient.SqlDataAdapter
        Dim ds As System.Data.DataSet
        Try
            mycomm = New System.Data.SqlClient.SqlCommand
            With mycomm
                .Connection = myconn
                .CommandType = .CommandType.StoredProcedure
                .CommandText = "up_GetPublisherInfo"
            End With
            myda = New System.Data.SqlClient.SqlDataAdapter(mycomm)
            ds = New Data.DataSet
            ds.Tables.Clear()
            sqlconn_Open()
            myda.Fill(ds, "temp")
            Me.GridView1.DataSource = ds.Tables("temp").DefaultView
            Me.GridView1.DataBind()
        Catch ex As Exception

        End Try


Sub delete_procedure()
        Dim myda As System.Data.SqlClient.SqlDataAdapter
        Dim ds As System.Data.DataSet
        Try
            mycomm = New System.Data.SqlClient.SqlCommand
            With mycomm
                .Connection = myconn
                .CommandType = .CommandType.StoredProcedure
                .CommandText = "vendor_info_delete"

                .Parameters.AddWithValue("@Vendor_Code", 17)
            End With
            sqlconn_Open()
            mycomm.ExecuteNonQuery()
            sqlconn_Close()
        Catch ex As Exception

        End Try

    End Sub