executing a dymamically built string with an OUTPUT parameter

Hi friends,

I have a dynamically built string that i need to execute and set a parameter to. I have come accross help in the books online but I need to set a variable to the answer of the dynamically executed string. It looks as follows:

DECLARE @.sRCDQueryString nvarchar(1000)
DECLARE @.sActivityName varchar(100)
DECLARE @.sRCDParmDefinition nvarchar(500)
DECLARE @.lGLCodeID int
DECLARE @.rRCDUnitValue real

SET @.lGLCOdeID = 1--391
SET @.sActivityName = 'REPPLA'

SET @.sRCDQueryString = 'SELECT ' + @.sActivityName +
' FROM ABCRCDMatrix WHERE GLCodeID = @.GL'
SET @.sRCDParmDefinition = '@.GL int, @.Value real OUTPUT';
EXEC sp_executesql @.sRCDQueryString,@.sRCDParmDefinition,@.GL = @.lGLCodeID, @.Value = @.rRCDUnitValue OUTPUT

select @.rRCDUnitValue

When I execute this code, I get a value by the line in red but it doesn't seem to be allocating a the result correctly to @.rRCDUnitValue since this variable is null

How can i get the result correctly allocated to the variable?

Regards

Use the following statement, you missed the value assignment,

Code Snippet

DECLARE @.sRCDQueryString nvarchar(1000)

DECLARE @.sActivityName varchar(100)

DECLARE @.sRCDParmDefinition nvarchar(500)

DECLARE @.lGLCodeID int

DECLARE @.rRCDUnitValue real

SET @.lGLCOdeID =1--391

SET @.sActivityName = 'REPPLA'

SET @.sRCDQueryString = 'SELECT @.Value=' + @.sActivityName +

' FROM ABCRCDMatrix WHERE GLCodeID = @.GL'

SET @.sRCDParmDefinition = '@.GL int, @.Value real OUTPUT';

EXEC sp_executesql @.sRCDQueryString,@.sRCDParmDefinition,@.GL = @.lGLCodeID, @.Value = @.rRCDUnitValue OUTPUT

select @.rRCDUnitValue

|||

You are declaring a parameter, but that parameter is not being used at all inside the dynamic statement. See this example:

Code Snippet

use northwind

go

declare @.sql nvarchar(4000)

declare @.orderid int

declare @.order_total money

set @.sql = N'select @.order_total = sum(quantity * unitprice * (1.00 - discount)) from dbo.[order details] where orderid = @.orderid'

set @.orderid = 10250

exec dbo.sp_executesql @.sql, N'@.orderid int, @.order_total money OUTPUT', @.orderid, @.order_total OUTPUT

select @.order_total

go

AMB

|||

You got the answers for how to fix yoru code. But why do you need dynamic SQL in the first place? Are you aware of the risks and performance problems (benefits in some cases) with dynamic SQL code?

|||Hi Manivannan,

The @.sActivityName is actually the name of an unknown column that is looked up on another table, wich has been pivoted on the current table i'm trying to do a lookup on.

Thanks for the response
Mike
|||Hi Umachandar,

Yes I am aware of the performance knock but I have no choice in this matter to use a dynamic string since the columns I need to lookup/use are unknown at the point of execution since they are looked up from another table..

Essentially they are the result of a table that had been pivoted.

Regards
Mike
|||Ahhh, Thank you indeed sir, you have showed me the mistake and I appreciate it coz my variable now returns a value.

Kind Regards
Mike
sql

ExecuteScalar returns 0 (null) but INSERT is successful.

I have code that has worked just fine for some time, and now all of the sudden I am having an issue. I have a simple INSERT statement built and then make the following call:

RecordID = cmd.ExecuteScalar

I have never had a problem with this before. The RecordID of the newly inserted record is returned into the RecordID Integer varibale. All of the sudden, the varibale has a value of 0 (null I assume is being returned), but yet the INSERT worked just fine. I can check the table in SQL and it is populated with no issues.

No exception is thrown of any type or anything. Does anybody know what may be happening?

Can you give us a peek at the procedure?

|||

Need to check the SP, you should have the Select statement which returns the newly inserted value..

|||Yes, thanks for the quick response. I must of had a brain fart or something because I did not have the

SELECT @.@.IDENTITY

at the end of the SQL. I am not sure how it was working before, but I know I needed to add this at the end of the INSERT statement.

The only thing I can figure is that I copy and paste so much code to use as a template, that when I wrote this SQL from scratch I forgot to add it, and never really paid attention when I was copying the INSERT statements before.

Thank you!

|||

You may have a lingering problem; you probably should be using SCOPE_IDENTITY() function instead of @.@.IDENTITY.

You might want to give a look at a couple of previous posts related to SCOPE_IDENTITY() versus @.@.IDENTITY here and here.

|||

Its better to use the SCOPE_IDENTITY function..

Select Scope_Identity()

|||

Sounds good to me; I actually saw that as well in the MSDN example.

Could you tell me why it is better (performance, etc.)?

Thanks,

|||

@.@.IDENTITY hold the global value (across the scope)

Scope_Identity hold the current Scope value.

When there is a concurrency user try to insert the value on your table, the @.@.identity has the very latest data, which may not be inserted by your current scope, but the scope_identity always have the value whichever your current scope inserted.

ExecuteScalar returns 0 (null) but INSERT is successful.

I have code that has worked just fine for some time, and now all of the sudden I am having an issue. I have a simple INSERT statement built and then make the following call:

RecordID = cmd.ExecuteScalar

I have never had a problem with this before. The RecordID of the newly inserted record is returned into the RecordID Integer varibale. All of the sudden, the varibale has a value of 0 (null I assume is being returned), but yet the INSERT worked just fine. I can check the table in SQL and it is populated with no issues.

No exception is thrown of any type or anything. Does anybody know what may be happening?

Can you give us a peek at the procedure?

|||

Need to check the SP, you should have the Select statement which returns the newly inserted value..

|||Yes, thanks for the quick response. I must of had a brain fart or something because I did not have the

SELECT @.@.IDENTITY

at the end of the SQL. I am not sure how it was working before, but I know I needed to add this at the end of the INSERT statement.

The only thing I can figure is that I copy and paste so much code to use as a template, that when I wrote this SQL from scratch I forgot to add it, and never really paid attention when I was copying the INSERT statements before.

Thank you!

|||

You may have a lingering problem; you probably should be using SCOPE_IDENTITY() function instead of @.@.IDENTITY.

You might want to give a look at a couple of previous posts related to SCOPE_IDENTITY() versus @.@.IDENTITY here and here.

|||

Its better to use the SCOPE_IDENTITY function..

Select Scope_Identity()

|||

Sounds good to me; I actually saw that as well in the MSDN example.

Could you tell me why it is better (performance, etc.)?

Thanks,

|||

@.@.IDENTITY hold the global value (across the scope)

Scope_Identity hold the current Scope value.

When there is a concurrency user try to insert the value on your table, the @.@.identity has the very latest data, which may not be inserted by your current scope, but the scope_identity always have the value whichever your current scope inserted.