Using INT Variables in Stored Proc (EXEC) 2

[ljwilson]

I am having trouble with this one. I am trying to do pagination (JQGrid) and am dynamically created a sql statement to be executed. Right now, I am getting an error message:

Msg 245, Level 16, State 1, Procedure pEreq_Get_RequestSummaryByUID, Line 52
Conversion failed when converting the varchar value 'SELECT TOP ' to data type int.

Here is my SQL:
@LIMIT, @START and @END are all INT.

Any help would be greatly appreciated, this works without pagination and this is the last step toward using JQGrid with classic ASP and ajax.

SET @SQL = 'SELECT TOP ' + @LIMIT + ' FROM 
			(SELECT row_number() OVER (ORDER by ereq) as resultNum, r.ereq AS Ereq,
		   r.CreatorFirstName + '' '' + r.CreatorLastName AS Creator,
		   r.ChargeDepartment AS Dept,
		   COALESCE(r.ChargeEOC,NULL,0) AS ChargeEOC,
		   r.RequestType AS [Type],
		   COALESCE(r.RequestCost,NULL,0.00) AS RequestCost
		  FROM dbo.Request r
		  INNER JOIN dbo.[Status] s ON s.ereq = r.ereq
		  WHERE s.[Status] = ''Created'' AND r.creator = ''' + @UID + '''
		  ORDER BY ' + @SIDX + ' ' + @SORD + ') AS numberResults  
		  WHERE resultnum BETWEEN ' + @START + ' AND ' + @END

 

[PWise]

try

SET @SQL = 'SELECT TOP ' + convert(varchar(10),@LIMIT) + ' FROM....

 

[gmmastros]

Based on data type precedence, SQL is trying to convert the string to an int. You need to explicitly convert the ints to varchar.

SET @SQL = 'SELECT TOP ' + [!]Convert(VarChar(20),[/!] @LIMIT[!])[/!] + ' FROM
            (SELECT row_number() OVER (ORDER by ereq) as resultNum, r.ereq AS Ereq,
           r.CreatorFirstName + '' '' + r.CreatorLastName AS Creator,
           r.ChargeDepartment AS Dept,
           COALESCE(r.ChargeEOC,NULL,0) AS ChargeEOC,
           r.RequestType AS [Type],
           COALESCE(r.RequestCost,NULL,0.00) AS RequestCost
          FROM dbo.Request r
          INNER JOIN dbo.[Status] s ON s.ereq = r.ereq
          WHERE s.[Status] = ''Created'' AND r.creator = ''' + @UID + '''
          ORDER BY ' + @SIDX + ' ' + @SORD + ') AS numberResults  
          WHERE resultnum BETWEEN ' + [!]Convert(VarChar(20),[/!] @START[!])[/!] + ' AND ' + [!]Convert(VarChar(20),[/!] @END[!])[/!]

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[gmmastros]

For a full list of data type precedence:

http://msdn.microsoft.com/en-us/library/ms190309.aspx

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[markros]

There is no need to go with Dynamic SQL in your case, I believe.

Starting from SQL Server 2005 you can use parameter in TOP clause, so

select top (@myParam) ...

 

[markros]

Here is link demonstrating it

http://www.databasejournal.com/features/mssql/article.php/3532226/TOP-Clause-in-SQL-Server-2005.htm

 

[ljwilson]

Thanks! That was it, I wasn't even thinking about needing to convert.

 

[markros]

Please see my suggestion - you don't need to use dynamic SQL at all in your case.

 

[ljwilson]

markros,

How does that address getting rows 10-20 or 20-40? I don't just need the top XX rows, I need rows 20-40, etc.

With MySQL - it is a piece of cake:

SELECT * from tblMyTable
ORDER BY id
LIMIT 20,40

I realize MySQL is lacking in other areas and I really like SQL Server. I just need to get the pagination thingy working properly and I will be a happier camper.

 

[markros]

;with cte as (Select my columns.., row_number() over (order by ...) as RowNum from myTable where my criteria)

select * from cte where RowNum between @StartPos and @EndPos

 

[ljwilson]

I think that is what I am doing.

 

[markros]

Why you're creating your SQL as a string? Can you explain me that.

 

[ljwilson]

So I can easily use declared variables for the ORDER BY inside the SQL statement. Then I just pass it to an EXEC command. There might be easier ways, but this works great.

 

[markros]

At least try to use parameters instead of string concatenation. Check

http://blogs.lessthandot.com/index.php/DataMgmt/DataDesign/changing-exec-to-sp_executesql-doesn-t-p

sp_executeSQL

and google for "Curse and blessing of Dynamic SQL"