动态插入并返回@identity值 [英] insert dynamically and return @identity value

问题描述

亲爱的所有人，

这是我的编码，它将一些值插入User_log表并返回@identity值。

请帮助我。

感谢adv。

Dear All,
this is my coding which inserts some value into User_log table and returns @identity value .
please help me.
thanks in adv.

Declare @NewDB varchar(max);
SET @NewDB='ta.dbo.user_log ( CLIENT_ID,USER_ID,USER_NAME,LOGIN_TIME)'							
DECLARE @sqlQueryUserLogId nvarchar(MAX),@UserLogId bigint
DECLARE @ParmDefinitionUserLogId nvarchar(500);
SET @sqlQueryUserLogId='insert into  '+ @NewDB+'  values( User_Name=''' + @User_Name + ''' ,User_ID=''' + @User_Id + ''' , Client_Id='''+cast(@Client_Id as varchar(20))+''',LOGIN_TIME=GetDate() )';
SET @ParmDefinitionUserLogId= N'@UserLogId bigint OUTPUT'
EXEC sp_executesql @sqlQueryUserLogId,@ParmDefinitionUserLogId, @UserLogId=@@IDENTITY OUTPUT
			SET @UserLogId=@@IDENTITY;

推荐答案

有一个读到这个，输出值是存储的procudure参数而不是动态查询



使用OUTPUT参数返回数据 [ ^ ]

have a read of this, the output value is a stored procudure argument rather than a dynamic query

Returning Data by Using OUTPUT Parameters[^]

程序

改变程序TestFirst

（

@UserLogId bigint out）

as

DECLARE @User_Name varchar （50）;

DECLARE @User_Id int;

DECLARE @Client_Id int;

DECLARE @NewDB nvarchar（max）;

DECLARE @ParmUserLogId nvarchar（500）;

set @ User_Name ='mks';

set @ User_Id = 438;

set @ Client_Id = 4

SET @ NewDB ='ta.dbo.user_log（CLIENT_ID，USER_ID，USER_NAME，LOGIN_TIME）'

DECLARE @sqlQueryUserLogId nvarchar（ MAX）

SET @ sqlQueryUserLogId ='insert into'+ @ NewDB +'values（'+ cast（@Client_Id as varchar（20））+'，'+ cast（@User_Id as varchar（20） ）+'，'''+ @User_Name +'''，GetDate（））SET @ UserLogId = SCOPE_IDENTITY（）';

SET @ ParmUserLogId = N'@ UserLogId BIGINT OUTPUT'

EXEC sp_executesql @sqlQueryUserLogId，@ ParmUserLogId，@ UserLogId OUTPUT

set @ UserLogId = @ UserLogId;







现在执行



声明@UserLogId bigint;

执行TestFirst



@ UserLogId = @ UserLogId输出



打印'@ UserLogId ='+ cast（ @UserLogId as varchar）;



成功完全返回UserLogID

Procedure
alter procedure TestFirst
(
@UserLogId bigint out)
as
DECLARE @User_Name varchar(50);
DECLARE @User_Id int;
DECLARE @Client_Id int;
DECLARE @NewDB nvarchar(max);
DECLARE @ParmUserLogId nvarchar(500);
set @User_Name='mks';
set @User_Id=438;
set @Client_Id=4
SET @NewDB='ta.dbo.user_log ( CLIENT_ID,USER_ID,USER_NAME,LOGIN_TIME)'
DECLARE @sqlQueryUserLogId nvarchar(MAX)
SET @sqlQueryUserLogId='insert into '+ @NewDB+' values( '+cast(@Client_Id as varchar(20))+','+cast(@User_Id as varchar(20))+','''+ @User_Name +''', GetDate()) SET @UserLogId=SCOPE_IDENTITY()';
SET @ParmUserLogId= N'@UserLogId BIGINT OUTPUT'
EXEC sp_executesql @sqlQueryUserLogId,@ParmUserLogId, @UserLogId OUTPUT
set @UserLogId=@UserLogId;



Now Execute

declare @UserLogId bigint;
exec TestFirst

@UserLogId=@UserLogId output

print '@UserLogId= '+cast(@UserLogId as varchar);

succefully returns UserLogID

这篇关于动态插入并返回@identity值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！