在分类表中插入一条新的记录，主键是取到当前的最大值+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 @[email protected]+'select @maxid=max([email protected]+') from [email protected];
SELECT @[email protected]+' select @maxid '
EXEC sys.sp_executesql
@sql,
[email protected] as int output',
@numrows OUTPUT;
SET @rows=(@numrows+1);
--插入一条新的记录
SET @sql2='insert into [email protected]+'([email protected]+',[email protected]+',state,btime,etime) values(@sp_idvalue,
@sp_descvalue,@sp_statevalue,@sp_btimevalue,@sp_etimevalue)';
EXEC sys.sp_executesql
@sql2,
-----参数声明部分
[email protected]_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