SQL Server Performance

Why sp_executesql error

Discussion in 'General DBA Questions' started by albanello, Oct 29, 2006.

  1. albanello New Member

    Hi

    Can anyone tell me why I am getting this error, in query analyzer (MS SQL 2000) when I run the query listed below ?

    Server: Msg 245, Level 16, State 1, Line 1
    Syntax error converting the nvarchar value 'Col_2' to a column of data type smallint.

    ???????????????????????
    DECLARE @BallNumber int
    SET @BallNumber = 5

    DECLARE @DrawCount smallint
    SET @DrawCount = 2

    DECLARE @AggDrawCount smallint

    DECLARE @MMSkipHitColName nvarchar(32)
    SET @MMSkipHitColName = 'Col_' + CONVERT( nvarchar, @DrawCount )

    DECLARE @ParmDefinition nvarchar(255)
    SET @ParmDefinition = N'@ParmSkipHitColName nvarchar(32), @ParmRIndex smallint, @ParmRtnValue smallint OUTPUT'

    DECLARE @SQLString nvarchar(255)
    SET @SQLString = N'SELECT @ParmRtnValue = (SELECT @ParmSkipHitColName FROM MMWhiteBallSkipHitTbl WHERE MMWhiteBallSkipHitTbl.RowIndex = ParmRIndex)'

    EXEC sp_executesql @SQLString,
    @ParmDefinition,
    @ParmSkipHitColName = @MMSkipHitColName,
    @ParmRIndex = @BallNumber,
    @ParmRtnValue = @AggDrawCount OUTPUT
    ???????????????????????

    Thanks
    albanello
  2. khtan New Member

    Change to


    SET @SQLString = N'SELECT @ParmRtnValue = (SELECT ' + @MMSkipHitColName + ' FROM MMWhiteBallSkipHitTbl WHERE MMWhiteBallSkipHitTbl.RowIndex = ParmRIndex)'


    "Syntax error converting the nvarchar value 'Col_2' to a column of data type smallint."
    If you use @ParmSkipHitColName as the parameter it is interpreted by SQLServer as

    SELECT @ParmRtnValue = (SELECT 'Col_2' FROM MMWhiteBallSkipHitTbl...)
    which will gives the erro when assign to the smallint variable @ParmRtnValue.




    KH
  3. albanello New Member

    Hi khtan

    Thank You for your reply.

    I am trying to pass @MMSkipHitColName to the sp_executesql. The example I gave is simplified.

    The more complicated but still simplified is shown below

    ???????????????????????


    DECLARE @BallNumber int
    SET @BallNumber = 5

    DECLARE @DrawCount smallint
    SET @DrawCount = 2

    DECLARE @AggDrawCount smallint
    DECLARE @MMSkipHitColName nvarchar(32)
    DECLARE @ParmDefinition nvarchar(255)
    DECLARE @SQLString nvarchar(255)

    SET @ParmDefinition = N'@ParmSkipHitColName nvarchar(32), @ParmRIndex smallint, @ParmRtnValue smallint OUTPUT'

    SET @SQLString = N'SELECT @ParmRtnValue = (SELECT @ParmSkipHitColName FROM MMWhiteBallSkipHitTbl WHERE MMWhiteBallSkipHitTbl.RowIndex = ParmRIndex)'

    WHILE @BallNumber > 0
    BEGIN

    SET @MMSkipHitColName = 'Col_' + CONVERT( nvarchar, @DrawCount )

    EXEC sp_executesql @SQLString,
    @ParmDefinition,
    @ParmSkipHitColName = @MMSkipHitColName,
    @ParmRIndex = @BallNumber,
    @ParmRtnValue = @AggDrawCount OUTPUT

    SET @DrawCount = @DrawCount + 1
    SET @BallNumber = @BallNumber - 1
    END

    ???????????????????????

    Thanks Again I hope you can still help
    albanello
  4. jezemine New Member

    You can't parameterize the select list like you are trying to do. You can only parameterize the stuff in the where/having clauses. That means you'll have to follow khtan's advice in his post.<br /><br />Basically, when using sp_executesql to parameterize a dynamic sql statement, the same rules apply as when you are writign a sproc. Namely, you can't specify the select list in a sproc with the params passed to it. You can, however, reference sproc params in the where clause of selects in a sproc.<br /><br />does that make sense? [<img src='/community/emoticons/emotion-1.gif' alt=':)' />]<br /><br /><hr noshade size="1">SqlSpec - a fast, cheap, and comprehensive data dictionary generator <br />for SQL Server 2000 and 2005 and Analysis Server 2005 - www.elsasoft.org <br />
  5. albanello New Member

    jezemine

    Thanks for your responce.

    My experience is limited so I will have to take your word for it. I'll have to do somemore research. I'm trying to optimize a Stored Procedure and it is my understanding by doing it the way you are suggesting there will be a recompile every time the EXEC sp_executesql is called and that if I pass the parameters it will not recompile every pass. As I said this is a simplified version there will be alot more passes than 5 in the real code.

    Thanks again
    albanello
  6. Adriaan New Member

    First point is that indeed you can pass a column name to a stored procedure through a parameter, but you have to concatenate the name from that parameter into the column list of the query that you are spelling out.

    SET @SQLString =
    N'SELECT @ParmRtnValue = (SELECT '
    + @ParmSkipHitColName
    + N' FROM MMWhiteBallSkipHitTbl WHERE MMWhiteBallSkipHitTbl.RowIndex = ParmRIndex)'

    Next step is that you can do this SELECT in a single query - not a subquery:
    SET @SQLString =
    N'SELECT @ParmRtnValue = ' + @ParmSkipHitColName +
    N' FROM MMWhiteBallSkipHitTbl WHERE MMWhiteBallSkipHitTbl.RowIndex = ParmRIndex'

    Last step is that you have to use the @ParmRIndex parameter inside the query statement, otherwise you get an "unknown column" error for ParmRIndex:

    SET @SQLString =
    N'SELECT @ParmRtnValue = ' + @ParmSkipHitColName +
    ' FROM MMWhiteBallSkipHitTbl WHERE MMWhiteBallSkipHitTbl.RowIndex = @ParmRIndex'

    You do have the grasp of how to use OUTPUT here - that's usually the biggest obstacle when figuring out sp_ExecuteSQL.

    One final point is that after this -

    EXEC sp_executesql @SQLString,
    @ParmDefinition,

    - you should only list the parameters declared in the stored procedure, in corresponding order to the variables that you have declared in the @ParmDefinition list.

    Not:
    @ParmSkipHitColName = @MMSkipHitColName,
    @ParmRIndex = @BallNumber,
    @ParmRtnValue = @AggDrawCount OUTPUT

    But:
    @MMSkipHitColName,
    @BallNumber,
    @AggDrawCount OUTPUT


    Well, except that @MMSkipHitColName is not a parameter that you need to pass to sp_ExecuteSQL - you're already resolving it before the statement is executed.

    So drop @ParmSkipHitColName from the string for @ParmDefinition, and drop @MMSkipHitColName from the parameters listed after @ParmDefinition.
  7. albanello New Member

    Hi Adriaan

    Thanks for your responce.

    You had me till the last two sentences in your post.

    "Well, except that @MMSkipHitColName is not a parameter that you need to pass to sp_ExecuteSQL - you're already resolving it before the statement is executed.

    So drop @ParmSkipHitColName from the string for @ParmDefinition, and drop @MMSkipHitColName from the parameters listed after @ParmDefinition."

    If I do this "@ParmSkipHitColName" becomes a local variable and we are back to what khtan and jezemine said. Did I miss something in your post?

    Thanks again
    albanello
  8. jezemine New Member

    I'm sure Adriaan knows better than I. [<img src='/community/emoticons/emotion-1.gif' alt=':)' />]<br /><br /><hr noshade size="1">SqlSpec - a fast, cheap, and comprehensive data dictionary generator <br />for SQL Server 2000 and 2005 and Analysis Server 2005 - www.elsasoft.org <br />
  9. Adriaan New Member

    Huh?

    You concatenate the literal column name from the variable into the query statement before passing the statement to sp_ExecuteSQL.

    Look at the SET @SQLString = part.
  10. albanello New Member

    Hi Adriaan

    Is this what you are suggesting ?

    ????????
    DECLARE @AggDrawCount smallint
    DECLARE @MMSkipHitColName nvarchar(32)

    DECLARE @BallNumber int
    SET @BallNumber = 5

    DECLARE @DrawCount smallint
    SET @DrawCount = 2

    DECLARE @ParmDefinition nvarchar(255)
    SET @ParmDefinition = N'@ParmRIndex smallint, @ParmRtnValue smallint OUTPUT'

    DECLARE @SQLString nvarchar(255)
    SET @SQLString = N'SELECT @ParmRtnValue = ' + @MMSkipHitColName + ' FROM MMWhiteBallSkipHitTbl WHERE MMWhiteBallSkipHitTbl.RowIndex = ParmRIndex'

    While @DrawCount < 50
    BEGIN
    SET @MMSkipHitColName = 'Col_' + CONVERT( nvarchar, @DrawCount )

    EXEC sp_executesql @SQLString,
    @ParmDefinition,
    @BallNumber,
    @AggDrawCount OUTPUT

    Do something with @AggDrawCount
    SET @DrawCount = @DrawCount + 1

    END
    ???????????????????????

    I won't be able to try this till tonight 18:00 hours CST USA, because I'm at work now. If there is a problem with what I have please let me know. Otherwise I'll let you know how it works tonight.

    Thanks
    albanello
  11. khtan New Member

    the SQLString should be inside the while loop



    DECLARE @SQLString nvarchar(255)
    SET @SQLString = N'SELECT @ParmRtnValue = ' + @MMSkipHitColName + ' FROM MMWhiteBallSkipHitTbl WHERE MMWhiteBallSkipHitTbl.RowIndex = ParmRIndex'

    While @DrawCount < 50
    BEGIN
    SET @MMSkipHitColName = 'Col_' + CONVERT( nvarchar, @DrawCount )

    SET @SQLString = N'SELECT @ParmRtnValue = ' + @MMSkipHitColName + ' FROM MMWhiteBallSkipHitTbl WHERE MMWhiteBallSkipHitTbl.RowIndex = ParmRIndex'

    EXEC sp_executesql @SQLString,
    @ParmDefinition,
    @BallNumber,
    @AggDrawCount OUTPUT



    KH
  12. khtan New Member

    Here it is. With changes in bold


    DECLARE @BallNumber int
    SET @BallNumber = 5

    DECLARE @DrawCount smallint
    SET @DrawCount = 2

    DECLARE @AggDrawCount smallint
    DECLARE @MMSkipHitColName nvarchar(32)
    DECLARE @ParmDefinition nvarchar(255)
    DECLARE @SQLString nvarchar(255)

    SET @ParmDefinition = N'@ParmSkipHitColName nvarchar(32), @ParmRIndex smallint, @ParmRtnValue smallint OUTPUT'

    WHILE @BallNumber > 0
    BEGIN
    SET @MMSkipHitColName = 'Col_' + CONVERT( nvarchar, @DrawCount )

    SET @SQLString = N'SELECT @ParmRtnValue = (SELECT ' + @ParmSkipHitColName + ' FROM MMWhiteBallSkipHitTbl WHERE MMWhiteBallSkipHitTbl.RowIndex = @ParmRIndex)'

    EXEC sp_executesql @SQLString, @ParmDefinition,
    @ParmSkipHitColName = @MMSkipHitColName,
    @ParmRIndex = @BallNumber,
    @ParmRtnValue = @AggDrawCount OUTPUT

    SET @DrawCount = @DrawCount + 1
    SET @BallNumber = @BallNumber - 1
    END



    KH
  13. albanello New Member

    khtan, Adriaan and jezemine

    Thanks for your help.

    Works fine !

    albanello
  14. jezemine New Member

    that's what I thought originally - you can't parameterize the select list.

    as far as sp_executesql is concerned, the column you are selecting is not a parameter, its value is concatenated directly into the statement pass to it.

    SqlSpec - a fast, cheap, and comprehensive data dictionary generator
    for SQL Server 2000 and 2005 and Analysis Server 2005 - www.elsasoft.org
  15. albanello New Member

    Hi khtan

    What you say may be true but I can't find anyplace that says this. Everything I read implies you can pass and use the parameters for anything in the select statement (Column and Table names in the Select and From clause respectivley). I had this originally but I was trying to constuct a statement that did not recompile every time it is executed. It is my understanding that if the string is fixed the sp_executesql will not recompile everytime it is used. Since the value of "@MMSkipHitColName" changes every pass (Col_1, Col_2.....Col_50) it is going to recompile everytime (I think ?).

    My experience is limited and everything is a learning experience for me.

    Thanks for your help
    albanello
  16. Adriaan New Member

    Albanello,

    Do not confuse object-oriented programming with T-SQL. T-SQL can only use the column name to refer to the column, that's that.

    You can pass the column name to a procedure, but then the procedure must prepare the query statement by concatenating a string, before executing the string.
  17. albanello New Member

    Adriaan

    Am I correct that everytime the sp_executesql is executed it will recompile and that if the column name was not changed it would not recompile even when the row index is changed ?

    thanks
    albanello
  18. Adriaan New Member

    Compare these two:

    EXEC dbo.sp_ExecuteSQL '<query_statement>'
    EXEC dbo.sp_ExecuteSQL '<query_statement_with_parameters>', 'parameter list', <local variables>

    For each query statement that it is about to execute, SQL Server will check if there is an execution plan in cache. If it finds one, then the execution plan can be re-used, and there is no recompilation. This is what you want to see: no recompilations.

    But it all depends on the literal query statement:
    * if you include the criteria in the literal query statement, you are less likely to find a cached plan,
    * if you parameterize the criteria, the literal query statement stays the same for different criteria, and you improve your chances of finding a cached plan.

    Like we've seen, you cannot parameterize column names. This means that the literal query statement does change. However, as long as the criteria are parameterized, each time you execute for the same column name, SQL Server has a good chance of finding the proper execution plan in cache.

    Note that sp_ExecuteSQL is a kind of a virtual stored procedure - you don't store the definition, but at least SQL Server can benefit from cached plans.
  19. albanello New Member

    Thanks Adriaan

    Sometimes it's good to verify my understanding of something.

    Based on your help I see now that I need to change my execution sequence to execute up the @BallNumber (This is the RowIndex and is passed) that will keep the @MMSkipHitColName the same for more passes. The way I had it the @MMSkipHitColName was changing every pass so it was recompiling every pass.

    Thanks you have been a BIG help !

    albanello

Share This Page