--通用的存储过程
------------------------------------------------
--添加
create procedure usp_add
(
@table nvarchar(255),
@values nvarchar(max)=null
)
as
declare @sql nvarchar(max)
set @sql='insert into '+@table
if @values is not null
set @sql='insert into '+@table+' values('+@values+')'
exec sp_executesql @sql
select @@IDENTITY
go
exec usp_Add 'Stock' ,'''农业'',50,200'
go
--删除
create procedure usp_delete
(
@table nvarchar(255),
@where nvarchar(max)=null
)
as
declare @sql nvarchar(max)
set @sql='delete '+@table
if @where is not null
set @sql+=' where '+@where
exec sp_executesql @sql
go
--调用添加
exec usp_delete 'Stock','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 '+@table+' set '+@set
if @where is not null
set @sql+=' where '+@where
exec sp_executesql @sql
go
--调用修改
exec usp_update 'Stock','StockName=''工商''','id=2'
go
--查找
create procedure usp_select
(
@table nvarchar(255),
@where nvarchar(max)=null
)
as
declare @sql nvarchar(max)
set @sql='select * from '+@table
if @where is not null
set @sql=@sql+' where '+@where
exec sp_executesql @sql
go
--调用查找
exec usp_select 'Stock'
go
exec usp_select 'Stock','id=2'
go
------------------------------------------------
--添加
create procedure usp_add
(
@table nvarchar(255),
@values nvarchar(max)=null
)
as
declare @sql nvarchar(max)
set @sql='insert into '+@table
if @values is not null
set @sql='insert into '+@table+' values('+@values+')'
exec sp_executesql @sql
select @@IDENTITY
go
exec usp_Add 'Stock' ,'''农业'',50,200'
go
--删除
create procedure usp_delete
(
@table nvarchar(255),
@where nvarchar(max)=null
)
as
declare @sql nvarchar(max)
set @sql='delete '+@table
if @where is not null
set @sql+=' where '+@where
exec sp_executesql @sql
go
--调用添加
exec usp_delete 'Stock','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 '+@table+' set '+@set
if @where is not null
set @sql+=' where '+@where
exec sp_executesql @sql
go
--调用修改
exec usp_update 'Stock','StockName=''工商''','id=2'
go
--查找
create procedure usp_select
(
@table nvarchar(255),
@where nvarchar(max)=null
)
as
declare @sql nvarchar(max)
set @sql='select * from '+@table
if @where is not null
set @sql=@sql+' where '+@where
exec sp_executesql @sql
go
--调用查找
exec usp_select 'Stock'
go
exec usp_select 'Stock','id=2'
go