Post reply

Dynamic query problem

  • October 13, 2011 at 1:18 pm

    #244577

    I have a query like :

    Select @param = sum(scale) From table1

    This works fine but if I have the following it does not work:

    DECLARE @sql varchar(800)

    DECLARE @param FLOAT

    set @sql = N'

    Select ' + CAST(@param AS VARCHAR) + ' = sum(scale) From table1'

    EXEC (@sql)

    How to fix this?

    Thanks

  • October 13, 2011 at 1:20 pm

    #1394312

    You need to read about sp_executesql in Books Online. This will show you how to return a value from a dynamic query.

  • October 13, 2011 at 1:28 pm

    #1394323

    aren't you equating varchar to float in the dynamic query ?

    Is data conversion the actual error ?

    ______________________________________________________________________

    Ankit
    MCITP Database developer SQL 2008,MCTS Database Administration SQL 2008
    "Remembering that you are going to die is the best way I know to avoid the trap of thinking you have something to lose.
    You are already naked. There is no reason not to follow your heart.”

  • October 13, 2011 at 1:43 pm

    #1394332

    The error is from referencing a parameter that does not exist (out of scope). The parameter does not exist in the dynamic SQL and cannot be global. You must do as the last replier said and research OUTPUT parameters with sp_executesql.

    Thanks,

    Jared

    Jared
    CE - Microsoft

  • October 13, 2011 at 1:47 pm

    #1394336

    I found an example on BOL as Lynn stated and is working now. The first parameter to sp_executesql has to be Nvarchar and not varchar.

    Thanks.

  • October 13, 2011 at 1:55 pm

    #1394340

    ramadesai108 (10/13/2011)

    I found an example on BOL as Lynn stated and is working now. The first parameter to sp_executesql has to be Nvarchar and not varchar.

    Thanks.

    Okay, but simplify it. This is what it should look like:

    DECLARE @sql nvarchar(250)

    DECLARE @param FLOAT

    DECLARE @paramOUT FLOAT

    set @sql = N'SELECT @paramOUT = sum(scale) From table1'

    EXEC sp_executesql @sql, N'@paramOUT FLOAT OUT',@paramOUT=@param OUT

    SELECT @param

    Thanks,

    Jared

    Jared
    CE - Microsoft

  • October 14, 2011 at 12:29 pm

    #1394817

    Why are you casting Float to Varchar. Why dont you set @param as varchar initially.

Viewing 7 posts - 1 through 6 (of 6 total)

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