SqlServer通用存储过程

创建新的存储过程

@TableCondition 表名

@WhereCondition 条件

@ValueCondition 值

@SetCondition 要修改的列与值

@OrderCondition 排序

@SQL 执行的T-SQL语句

 

CREATE PROCEDURE dbo.spDelete（删）
(
@TableCondition nvarchar(255),
@WhereCondition nvarchar(255) = null
)
AS
DECLARE @SQL nvarchar(255)
SET @SQL = 'DELETE FROM [' + @TableCondition + ']'

IF @WhereCondition IS NOT NULL AND LEN(@WhereCondition) > 0
BEGIN
SET @SQL = @SQL + ' WHERE ' + @WhereCondition
END
  
EXEC sp_executesql @SQL
RETURN

 

 

 

 

CREATE PROCEDURE dbo.spInsert（增）
(
@TableCondition nvarchar(255),
@ValueCondition nvarchar(255) = null
)
AS
DECLARE @SQL nvarchar(255)
SET @SQL = 'INSERT INTO [' + @TableCondition + ']'

IF @ValueCondition IS NOT NULL AND LEN(@ValueCondition) > 0
BEGIN
SET @SQL = @SQL + ' VALUES( ' + @ValueCondition + ')'
END

SET @SQL = @SQL + ' SELECT SCOPE_IDENTITY()'

EXEC sp_executesql @SQL
RETURN

 

CREATE PROCEDURE dbo.spSelect （查）
(
@TableCondition nvarchar(255),
@WhereCondition nvarchar(255) = null,
@OrderCondition nvarchar(255) = null
)
AS
DECLARE @SQL nvarchar(255)
SET @SQL = 'SELECT * FROM [' + @TableCondition + ']'

IF @WhereCondition IS NOT NULL AND LEN(@WhereCondition) > 0
BEGIN
SET @SQL = @SQL + ' WHERE ' + @WhereCondition
END

IF @OrderCondition IS NOT NULL AND LEN(@OrderCondition) > 0
BEGIN
SET @SQL = @SQL + ' ORDER BY ' + @OrderCondition
END

EXEC sp_executesql @SQL
RETURN

 

 

CREATE PROCEDURE dbo.spUpdate（改）
(
@TableCondition nvarchar(255),
@SetCondition nvarchar(255) = null,
@WhereCondition nvarchar(255) = null
)
AS
DECLARE @SQL nvarchar(255)
SET @SQL = 'UPDATE [' + @TableCondition + ']'

IF @SetCondition IS NOT NULL AND LEN(@SetCondition) > 0
BEGIN
SET @SQL = @SQL + ' SET ' + @SetCondition
END

IF @WhereCondition IS NOT NULL AND LEN(@WhereCondition) > 0
BEGIN
SET @SQL = @SQL + ' WHERE ' + @WhereCondition
END
  
EXEC sp_executesql @SQL
RETURN