INT Conversion Error (Wierd)

[ljwilson]

This is wierd. I am trying to use a variable for the where clause in an SP. I am using EXEC (for now) to execute a built string of SQL. This part breaks it:

DECLARE @WHERE_SQL AS VARCHAR(500)


IF @STATUS = 'ALL'
		SET @WHERE_SQL = ' r.creator = ''' + @UID + ''' '
	ELSE
		SET @WHERE_SQL = ' WHERE s.[Status] = ''' + @STATUS + ''' AND r.creator = ''' + @UID + ''''
		
	SET @COUNT = ( SELECT  COUNT(r.ereq)
					FROM    dbo.Request r
							INNER JOIN dbo.[Status] s ON s.ereq = r.ereq + ' ' + @WHERE_SQL
				  )

@UID is a parameter - VARCHAR(10)

Here is the error I am getting:
Conversion failed when converting the varchar value ' r.creator = 'SYS.LJW' ' to data type int.

I don't understand why SQL Server is trying to convert that to an INT.

Thanks!

 

[gmmastros]

Here's a similar (and recent) question that may help you.

-George

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

 

[gmmastros]

Sorry... the link: thread183-1584109


-George

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

 

[markros]

Read this excellent article for in depth discussion of the problem

http://www.sommarskog.se/dyn-search-2008.html

 

[ljwilson]

George,

In that case, the issue involved using a variable declared as an INT (@LIMIT, @START, and @END). In this case the variables I am using are declared as VARCHAR.

Am I missing something?

 

[markros]

In your case you're trying to mix dynamic SQL with non-dynamic. You can not do this.

Try instead:

IF @STATUS = 'ALL'        
    SET @COUNT = ( SELECT  COUNT(r.ereq)                    FROM    dbo.Request r  
INNER JOIN dbo.[Status] s ON s.ereq = r.ereq 
where r_Creator = @UID

ELSE

    SET @COUNT = ( SELECT  COUNT(r.ereq)                    FROM    dbo.Request r  
INNER JOIN dbo.[Status] s ON s.ereq = r.ereq 
where r_Creator = @UID and s.Status = @Status

 

[ljwilson]

I am also using the @WHERE_SQL for the main SQL statement. Is there no way to append one variable value to another in T-SQL?

SET @SQL = 'SELECT TOP ' + CONVERT(VARCHAR(10),@LIMIT) + ' * FROM 
			(SELECT TOP ' + CONVERT(VARCHAR(10),@COUNT) + ' 
			row_number() OVER (ORDER by r.ereq) as resultNum, ' + 
			CONVERT(VARCHAR(10),@TOTAL_OUT) + ' AS [COUNT],			
			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_SQL +
		  ' ORDER BY ' + @SIDX + ' ' + @SORD + ') AS numberResults  
		  WHERE resultnum BETWEEN ' + CONVERT(VARCHAR(10),@START) + ' 
		  AND ' + CONVERT(VARCHAR(10),@END)

Thanks for any help with this. If I need to be tackling this in a differnet way, I am willing to change. I am used to working with PHP and MySQL and with that architecture, this sort of stuff is easy

 

[ljwilson]

BTW - This works:

SET @COUNT = ( SELECT  COUNT(r.ereq)
					FROM    dbo.Request r
							INNER JOIN dbo.[Status] s ON s.ereq = r.ereq 
					WHERE   s.[Status] = @STATUS
							AND r.creator = @UID
				  )
						
	IF @STATUS = 'ALL'
	BEGIN
		SET @COUNT = ( SELECT  COUNT(r.ereq)
					FROM    dbo.Request r
							INNER JOIN dbo.[Status] s ON s.ereq = r.ereq 
					WHERE   r.creator = @UID
				  )
		
	END

And this:

SET @SQL = 'SELECT TOP ' + CONVERT(VARCHAR(10),@LIMIT) + ' * FROM 
			(SELECT TOP ' + CONVERT(VARCHAR(10),@COUNT) + ' 
			row_number() OVER (ORDER by r.ereq) as resultNum, ' + 
			CONVERT(VARCHAR(10),@TOTAL_OUT) + ' AS [COUNT],			
			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,
		   s.status AS [Status]
		  FROM dbo.Request r
		  INNER JOIN dbo.[Status] s ON s.ereq = r.ereq '
	
	IF @STATUS = 'ALL'
		SET @SQL = @SQL + ' WHERE r.creator = ''' + @UID + ''' '
	ELSE
		SET @SQL = @SQL + ' WHERE s.[Status] = ''' + @STATUS + ''' AND r.creator = ''' + @UID + ''''
		
	SET @SQL = @SQL + ' ORDER BY ' + @SIDX + ' ' + @SORD + ') AS numberResults  
		  WHERE resultnum BETWEEN ' + CONVERT(VARCHAR(10),@START) + ' 
		  AND ' + CONVERT(VARCHAR(10),@END)

Now why can't I use that varchar variable @WHERE_SQL instead of this way?

Puzzled and in need of enlightenment

 

[markros]

In the last case, I believe, you can use @Where_SQL directly, e.g.

SET @SQL = @SQL + @WHERE_SQL

Are you sure @UID is a character? Could it be that @UID is int ?

In any case, there is lots of situations when dynamic SQL is not needed. I believe that yours could be one of them.

 

[ljwilson]

@UID is Varchar(10). I can't use @WHERE_SQL cause I can SET it.

 

[markros]

Can you please re-state your problem again? Post the whole SP code or at least a little bit more than you posted originally.