动态sql 传递多参 多变量的例子
在分类表中插入一条新的记录，主键是取到当前的最大值+1
USE [TKInfDB]
GO
/****** Object: StoredProcedure [MSG].[P_CreateNewClass] Script Date: 05/22/2012 12:00:13 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER procedure [MSG].[P_CreateNewClass]
@tbName nvarchar(50),
@id NVARCHAR(50),
@desc nvarchar(255),
@desc_value nvarchar(255),
@state_value as int=0,
@BTime_value as datetime='1970-01-01',
@ETime_value as datetime='2038-01-01'
as
BEGIN
DECLARE @sql nvarchar(max);
DECLARE @sql2 NVARCHAR(max);
DECLARE @numrows AS INT;
DECLARE @rows AS INT;
SELECT @sql=''
--SELECT @sql='declare @maxid int ;'
SELECT @sql=@sql+'select @maxid=max('+@id+') from '+@tbName;
SELECT @sql=@sql+' select @maxid '
EXEC sys.sp_executesql
@sql,
N'@maxid as int output',
@numrows OUTPUT;
SET @rows=(@numrows+1);
--插入一条新的记录
SET @sql2='insert into '+@tbName+'('+@id+','+@desc+',state,btime,etime) values(@sp_idvalue,
@sp_descvalue,@sp_statevalue,@sp_btimevalue,@sp_etimevalue)';
EXEC sys.sp_executesql
@sql2,
-----参数声明部分
N'@sp_idvalue int,@sp_descvalue varchar(255),@sp_statevalue int,@sp_btimevalue datetime,@sp_etimevalue datetime',
----参数赋值部分
@rows,
@desc_value,
@state_value,
@BTime_value,
@ETime_value
END
