使用 sql server 添加刪除修改查詢儲存過程

字號:


    --添加
    create procedure usp_add
    (
    @table nvarchar(255),
    @values nvarchar(max)=null
    )
    as
    declare @sql nvarchar(max)
    set @sql='insert into>
    if @values is not null
    set @sql='insert into values(
    exec sp_executesql @sql
    select @@identity
    go
    exec usp_add '金山股份' ,'''abc'',20,300'
    go
    --刪除
    create procedure usp_delete
    (
    @table nvarchar(255),
    @where nvarchar(max)=null
    )
    as
    declare @sql nvarchar(max)
    set @sql='delete
    if @where is not null
    set @sql+=' where
    exec sp_executesql @sql
    go
    exec usp_delete '金山股分','id=1'
    go
    --修改
    create procedure usp_update
    (
    @table nvarchar(255),
    @set nvarchar(max),
    @where nvarchar(max)=null
    )
    as
    declare @sql nvarchar(max)
    set @sql='update set
    if @where is not null
    set @sql+=' where
    exec sp_executesql @sql
    go
    exec usp_update '金山股份','stockname=''騰訊股分''','id=2'
    go
    --查找
    create procedure usp_select
    (
    @table nvarchar(255),
    @where nvarchar(max)=null
    )
    as
    declare @sql nvarchar(max)
    set @sql='select * from
    if @where is not null
    set @sql=@sql+' where
    exec sp_executesql @sql
    go
    exec usp_select 'stock','id=1'
    go