sp_executesql parametrized & output parameter

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

sp_executesql parametrized & output parameter

Author

Topic

ingewitt
Starting Member

3 Posts

Posted - 2008-11-13 : 12:18:35

Hello,
I hope someone can help me on this. Probably the solution is quite simple but I can't make this statement to work.
I have simplified my whole stored procedure.
In the follwing example I have created 2 fields which have fixed values: @VFIELD_SP & @VLKL_LSD_ID

DECLARE @VFIELD_SP VARCHAR(300)
DECLARE @VLKL_LSD_ID INT
SET @VFIELD_SP = 'LKL_DOCUMENT_NR' --fixed field 1 which actually contains the name of a field of which I want to get the value from.

SET @VLKL_LSD_ID = 45464 --fixed field 2 containing the ID of the table which will make the search unique

DECLARE @PRESULT VARCHAR(300)

EXEC sp_executesql

N'SELECT @PRESULT_INTERNAL = @PFIELD_INTERNAL FROM DBO.INDEX_SP WHERE LKL_LSD_ID = @PLSD_ID_INTERNAL',
N'@PFIELD_INTERNAL VARCHAR(300), @PLSD_ID_INTERNAL INT, @PRESULT_INTERNAL VARCHAR(3000) OUTPUT',
@PFIELD_INTERNAL = @VFIELD_SP, @PLSD_ID_INTERNAL = @VLKL_LSD_ID,
@PRESULT_INTERNAL = @PRESULT OUTPUT

SELECT @PRESULT

If I execute this procedure the result is simply: 'LKL_DOCUMENT_NR' instead of its actual value in the table. LKL_DOCUMENT_NR is a field in the table INDEX_SP and I am interesting in getting back it's value not the name of the field.

Can someone help me on this?

I have tried to change the select clause but i always get the message that a NVARCHAR/NTEXT is expected...

I hope someone can help me.

Kind regards,
Inge

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-11-13 : 12:29:02

shouldnt statement be this?

SELECT @PRESULT_INTERNAL = @PFIELD_INTERNAL FROM DBO.INDEX_SP WHERE LKL_LSD_ID = @PLSD_ID_INTERNAL

ingewitt
Starting Member

3 Posts

Posted - 2008-11-13 : 12:41:05

Hello,

Thank you for your quick response, but the '@' is written there in my statement...

quote:
Originally posted by visakh16

shouldnt statement be this?

SELECT @PRESULT_INTERNAL = @PFIELD_INTERNAL FROM DBO.INDEX_SP WHERE LKL_LSD_ID = @PLSD_ID_INTERNAL

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-11-13 : 23:20:43

i think thats wrong. it should be fieldname that's specified there.that doesnt need @ sign

malaytech2008
Yak Posting Veteran

95 Posts

Posted - 2008-11-13 : 23:36:57

Visakh is right, that should be fieldname,

EXEC sp_executesql

N'SELECT @PRESULT_INTERNAL =FIELDNAME FROM DBO.INDEX_SP WHERE LKL_LSD_ID = @PLSD_ID_INTERNAL AND FIELDNAME=@PFIELD_INTERNAL',
N'@PFIELD_INTERNAL VARCHAR(300), @PLSD_ID_INTERNAL INT, @PRESULT_INTERNAL VARCHAR(3000) OUTPUT',
@PFIELD_INTERNAL = @VFIELD_SP, @PLSD_ID_INTERNAL = @VLKL_LSD_ID,
@PRESULT_INTERNAL = @PRESULT OUTPUT

malay

ingewitt
Starting Member

3 Posts

Posted - 2008-11-14 : 12:43:45

actually I want the select to be:

select LKL_DOCUMENT_NR from DBO.INDEX_SP where LKL_LSD_ID = 45464

I want to return the stored value of the field LKL_DOCUMENT_NR in this case. But as this fieldname changes because I due to a cursor I enumarate all fields of the table INDEX_SP and I want to check its value. So the variabel @VFIELD_SP can change.
In my example the fieldname was set to 'LKL_DOCUMENT_NR' but this variabel can change due to the enumaration of all fields in the table INDEX_SP.

Please I need some help or maybe I don't quite understand the solution, but it is not working... :-(

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-11-14 : 12:46:09

what's the purpose of calling sp_executesql inside a cursor?

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources