表名tbl_gainer,字段:
gnr_id int identity(1,1)
fsEmpId varchar(36)
gnr_name varchar(255)
gnr_time datetime
prm_typeid int
gnr_note varchar(255)sql如何创建带参数存储过程(除了gnr_id外,其它字段都为参数),insert一条记录并返回该条记录的自动编号(gnr_id)?谢谢!!
gnr_id int identity(1,1)
fsEmpId varchar(36)
gnr_name varchar(255)
gnr_time datetime
prm_typeid int
gnr_note varchar(255)sql如何创建带参数存储过程(除了gnr_id外,其它字段都为参数),insert一条记录并返回该条记录的自动编号(gnr_id)?谢谢!!
create proc p_1(@arg1 varchar(200),@arg2 int,......)
create procedure Pr_Test(
@fsEmpId varchar(36) ,
@gnr_name varchar(255),
@gnr_time datetime,
@prm_typeid int,
@gnr_note varchar(255)
)as
begin
declare @r int
insert into tbl_gainer(fsEmpId,gnr_name,gnr_time,prm_typeid,gnr_note) values
(@fsEmpId,@gnr_name,@gnr_time,@prm_typeid,@gnr_note) set @r = @@identity
return @rend
@fsEmpId varchar(36)
@gnr_name varchar(255)
@gnr_time datetime
@prm_typeid int
@gnr_note varchar(255)
)
as
begin
insert into tbl_gainer(fsEmpId,gnr_name,gnr_time,prm_typeid,gnr_note) values(@fsEmpId,@gnr_name,@gnr_time,@prm_typeid,@gnr_note) end
@fsEmpId varchar(36) ,
@gnr_name varchar(255),
@gnr_time datetime,
@prm_typeid int,
@gnr_note varchar(255) ,
@gnr_id int output
)as
begin
insert into tbl_gainer(fsEmpId,gnr_name,gnr_time,prm_typeid,gnr_note) values
(@fsEmpId,@gnr_name,@gnr_time,@prm_typeid,@gnr_note) set @gnr_id = @@identity return 0end--返回参数
@fsEmpId varchar(36),
@gnr_name varchar(255),
@gnr_time datetime,
@prm_typeid int,
@gnr_note varchar(255),
@gnr_id int output
as
insert tbl_gainer(fsEmpId,gnr_name,gnr_time,prm_typeid,gnr_note) select @fsEmpId,@gnr_name,@gnr_time,@prm_typeid,@gnr_note
set @gnr_id=@@IDENTITY
return @gnr_id[/code]
go
create table tb(ID int identity(1,1),[name] varchar(10))
go
if object_id('P_insert')is not null drop proc P_insert
go
create proc P_insert
@Name varchar(10),
@ID int output
as
insert tb([name]) select @name
set @id=@@Identity
go
declare @id int
exec P_insert '张三', @id output
exec P_insert 'wang', @id output
select @id
/*
-----------
2(所影响的行数为 1 行)
*/
(
@cTableName varchar(8000) ,
@bInsertFlag int , -- 如果需要新增语句为 0 否则为 1
@bUpdateFlag int , -- 如果需要修改语句为 0 否则为 1
@bDeleteFlag int -- 如果需要删除语句为 0 否则为 1
)
as
declare @iTableId int
declare @cPro varchar(8000)
declare @cProHead varchar(8000)
declare @cProAfter varchar(8000)
declare @cProInsertSql varchar(8000)
declare @cProUpdateSql varchar(8000)
declare @cProDeleteSql varchar(8000)
declare @cProColumn varchar(8000)
select @iTableId = id from sysobjects where name = @cTableName
set @cProHead = 'CREATE PROCEDURE ' + @cTableName + ' _Add _Update
( '
set @cPro = ''
set @cProColumn = ''
set @cProInsertSql = ''
set @cProUpdateSql = ''
set @cProDeleteSql = ''
set @cProAfter = ' IF @@error = 0 BEGIN
COMMIT TRANSACTION
SELECT 0 , ''保存成功''
RETURN
END
ELSE BEGIN
ROLLBACK TRANSACTION
SELECT 1 , ''保存失败''
RETURN
END
RETURN
'
select
@cProInsertSql = @cProInsertSql + ' ,
@' + a.name ,
@cProUpdateSql = @cProUpdateSql + ' ,
' + a.name + ' = @' + a.name ,
@cProColumn = @cProColumn + ' ,
@' + a.name + ' ' + b.name +
case when a.xtype = 167 then
'(' + convert(varchar(20),a.length) + ')'
when a.xtype = 106 then
'(' + convert(varchar(20),a.xprec) + ',' + convert(varchar(20),a.xscale) + ')'
else ''
end
from syscolumns as a
left join systypes as b on a.xtype = b.xtype
where a.id = @iTableId
set @cProInsertSql = right(@cProInsertSql , len(@cProInsertSql) - 5)
set @cProUpdateSql = right(@cProUpdateSql , len(@cProUpdateSql) - 5)
set @cProColumn = right(@cProColumn , len(@cProColumn) - 5)
set @cProHead =
@cProHead +
'
'
+@cProColumn +
'
)
AS
SET NOCOUNT ON
BEGIN TRANSACTION'
set @cProInsertSql = ' INSERT INTO ' + @cTableName + '(
' + REPLACE(@cProInsertSql , '@' , '' ) +
'
)' + '
VALUES (
' +
@cProInsertSql +
'
)'
set @cProUpdateSql = ' UPDATE ' + @cTableName + ' SET
' + @cProUpdateSql + '
WHERE '
set @cProDeleteSql = 'DELETE FROM ' + @cTableName + ' WHERE '
set @cPro = @cProHead
--增加
if @bInsertFlag = 0
set @cPro = @cPro + '
' + @cProInsertSql
--修改
if @bUpdateFlag = 0
set @cPro = @cPro + '
' + @cProUpdateSql
--删除
if @bDeleteFlag = 0 begin
set @cProHead = 'CREATE PROCEDURE ' + @cTableName + '_Del
(
@iSysCodes VARCHAR(8000)
)
AS
SET NOCOUNT ON
BEGIN TRANSACTION
DECLARE @iStart INT
DECLARE @iLen INT
DECLARE @iSysCode INT
WHILE LEN(@iSysCodes)>0 BEGIN
SET @iStart=CHARINDEX('','',@iSysCodes)
IF @iStart=0 BEGIN
SET @iSysCode=LTRIM(@iSysCodes)
SET @iSysCodes=''''
END
ELSE BEGIN
SET @iLen=LEN(@iSysCodes)
SET @iSysCode=LEFT(@iSysCodes,@iStart-1)
SET @iSysCodes=LTRIM(RIGHT(@iSysCodes,@iLen-@iStart))
END'
set @cPro = @cProHead
set @cPro = @cPro + '
' + @cProDeleteSql + '
END'
end
set @cPro = @cPro + '
' + @cProAfter
print @cPro
GO