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
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
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
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 />
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
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.
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
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 />
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.
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
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
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
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
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
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.
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
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.
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
