Post reply

sp_executesql parametrized & output parameter

  • 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

  • November 14, 2008 at 3:12 am

    #898462

    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

  • November 14, 2008 at 10:48 am

    #898754

    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!

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

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