sp_executesql parametrized & output parameter

Question

Post reply

sp_executesql parametrized & output parameter

inge wittendorp

SSC Rookie

Points: 25
More actions
November 13, 2008 at 9:50 am

#103625

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

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply

Ken McKelvey SSCoach Points: 18843 More actions · Answer 1

The variable @VField_SP needs to be incorporated into the dynamic select.

It will not work as a parameter. Something like:

DECLARE @VField_SP nvarchar(300)

&nbsp&nbsp&nbsp&nbsp,@VLKL_LSD_ID int

&nbsp&nbsp&nbsp&nbsp,@PResult varchar(300)

&nbsp&nbsp&nbsp&nbsp,@SQLString nvarchar(4000)

SELECT @VField_SP = 'LKL_DOCUMENT_NR'

&nbsp&nbsp&nbsp&nbsp,@VLKL_LSD_ID = 45464

SET @SQLString =

&nbsp&nbsp&nbsp&nbspN'SELECT @PResult_Internal = ' + @VField_SP

+N' FROM DBO.Index_SP '

+N'WHERE LKL_LSD_ID = @PLSD_ID_Internal'

EXEC sp_executesql

&nbsp&nbsp&nbsp&nbsp@SQLString

&nbsp&nbsp&nbsp&nbsp,N'@PLSD_ID_Internal int, @PResult_Internal varchar(3000) OUTPUT'

&nbsp&nbsp&nbsp&nbsp,@PLSD_ID_Internal = @VLKL_LSD_ID

&nbsp&nbsp&nbsp&nbsp,@PResult_Internal = @PResult OUTPUT

SELECT @PResult

inge wittendorp SSC Rookie Points: 25 More actions · Answer 2

Thank you very much. That did the trick!

I worked with the '+' but hten i always got an error message about the fact that the @statement needed to be a NVARCHAR or NTEXT but anyway thanks a lot!