欢迎您访问程序员文章站本站旨在为大家提供分享程序员计算机编程知识!
您现在的位置是: 首页  >  数据库

SQLServer通用的CRUD存储过程

程序员文章站 2022-04-09 15:06:06
...

欢迎进入Windows社区论坛,与300万技术人员互动交流 >>进入 --通用的增加存储过程 if exists (select * from sysobjects where name = 'usp_insert') drop proc usp_insert go create proc usp_insert ( @table nvarchar(255), @values nvarchar(max

欢迎进入Windows社区论坛,与300万技术人员互动交流 >>进入

--通用的增加存储过程

if exists (select * from sysobjects where name = 'usp_insert')

drop proc usp_insert

go

create proc usp_insert

@table nvarchar(255),

@values nvarchar(max)

as

declare @sql nvarchar(max)

set @sql = 'insert into ['+@table+'] values('+@values+')'

exec sp_executesql @sql

go

exec usp_insert 'customer','''Tom'',''132342434243'''

go

--通用的删除存储过程

if exists (select * from sysobjects where name = 'usp_delete')

drop proc usp_delete

go

create proc usp_delete

@table nvarchar(255),

@where nvarchar(max)

as

declare @sql nvarchar(max)

set @sql = 'delete from ['+@table +']'

if(@where is not null and len(@where)>0)

set @sql += ' where '+@where

exec sp_executesql @sql

go

exec usp_delete 'customer','id = 1'

go

-- 通用的修改存储过程

if exists (select * from sysobjects where name = 'usp_update')

drop proc usp_update

go

create proc usp_update

@table nvarchar(255),

@set nvarchar(max),

@where nvarchar(max)

as

declare @sql nvarchar(max)

set @sql = 'update ['+@table+'] set '+@set

if(@where is not null and len(@where)>0)

set @sql += ' where '+@where

exec sp_executesql @sql

go

exec usp_update 'customer','name = ''Smile''','id = 1'

go

--通用的查询存储过程

if exists (select * from sysobjects where name = 'usp_select')

drop proc usp_select

go

create proc usp_select

@table nvarchar(255),

@where nvarchar(max)

as

declare @sql nvarchar(max)

set @sql = 'select * from ['+@table +']'

if(@where is not null and len(@where)>0)

set @sql += ' where '+@where

exec sp_executesql @sql

go

exec usp_select 'customer','id = 2'

go

SQLServer通用的CRUD存储过程