insert dynamically and return @identity value

Question

1.00/5 (1 vote)

See more:

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

C#

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;

Posted 8-Aug-13 23:59pm

connect2manas

Updated 9-Aug-13 0:22am

Thanks7872

v2

Add a Solution

Comments

[no name] 9-Aug-13 6:18am

Put your above code in a stored procedure and then return the identity value from the stored procedure.

2 solutions

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Simon_Whale · Answer 1 · 2013-08-09T00:26:00

Solution 1

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

Returning Data by Using OUTPUT Parameters[^]

Posted 9-Aug-13 0:26am

Simon_Whale

Comments

[no name] 9-Aug-13 6:45am

+5

connect2manas 9-Aug-13 7:10am

Hi All,
this is the stored procedure.
After Execution error:

Msg 128, Level 15, State 1, Line 1
The name "User_Name" is not permitted in this context. Valid expressions are constants, constant expressions, and (in some contexts) variables. Column names are not permitted.

alter procedure TestFirst
as
DECLARE @User_Name varchar(50);
DECLARE @User_Id int;
DECLARE @Client_Id int;
DECLARE @UserLogId bigint
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( User_Name=''' + @User_Name + ''' ,User_ID=''' + cast(@User_Id as varchar(20)) + ''' , Client_Id='''+cast(@Client_Id as varchar(20))+''',LOGIN_TIME=GetDate()) SELECT @UserLogId = SCOPE_IDENTITY() ';
SET @ParmUserLogId= N'@UserLogId INTEGER OUTPUT'
EXEC sp_executesql @sqlQueryUserLogId,@ParmUserLogId, @UserLogId OUTPUT
set @UserLogId=@UserLogId;

exec TestFirst

Simon_Whale 9-Aug-13 7:15am

you haven't declared which variable is your output variable in the stored procedure parameter declarations.

connect2manas · Answer 2 · 2013-08-11T23:21:00

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