Stored Proc Error converting data type varchar to column int 2

[matthewking]

This sproc is supposed to build a query dynamically (in order to add filtering) then page records, it worked fine until I added the employeesTo filter, now I get this error

=============================================

Server: Msg 245, Level 16, State 1, Procedure sp_getAllRecords, Line 105
Syntax error converting the varchar value '
SELECT
ID
, Title
, FName
, Surname
, Position
, Email
, Organisation
, Phone
, City
, Industry
, County
, NoEmp

FROM
SME
WHERE NoEmp <= ' to a column of data type int.



=====================================================
And here is the sproc. (Ive clearly marked out where the error is with ############)

CREATE PROCEDURE sp_getAllRecords
(
@StartPosition Integer = 0
, @ResultSize Integer = 20
, @City Varchar(250) = NULL
, @County Varchar(250) = NULL
, @Company Varchar(250) = NULL
, @EmployeesFrom Integer = NULL
, @EmployeesTo Integer = NULL
)

AS

BEGIN

SET NOCOUNT ON

DECLARE @query varchar(2500)

SET @query =
'
SELECT
ID
, Title
, FName
, Surname
, Position
, Email
, Organisation
, Phone
, City
, Industry
, County
, NoEmp

FROM
SME
'

DECLARE @filterOn int

SET @filterOn = 0

-- We use the filterOn variable to determine if any filter querys have been added, this allows us to use the correct, where or and in the query.

-- ### CITY FILTER ###
IF (@City != NULL)
BEGIN
IF (@filterOn = 1)
BEGIN
SET @query = @query + ' AND City = ''' + @City + ''''
END

IF (@filterOn != 1)
BEGIN
SET @query = @query + ' WHERE City = ''' + @City + ''''
SET @filterOn = 1
END
END

-- #######################################################
-- #######################################################

-- ### EMPLOYEES TO FILTER ### THIS IS WHERE THE ERROR IS
IF (@EmployeesTo != NULL)
BEGIN
IF (@filterOn = 1)
BEGIN
SET @query = @query + ' AND NoEmp <= ' + @EmployeesTo
END

IF (@filterOn != 1)
BEGIN
SET @query = @query + ' WHERE NoEmp <= ' + @EmployeesTo
SET @filterOn = 1
END
END

... extracted some code for paging ...

EXEC (@query)

SET NOCOUNT OFF

END
GO

===============
Any help would be greatly appreciated, thanks in advance.

Matthew

 

[NoCoolHandle]

I am having a problem seeing exactly where your proc is bombing but if I was you I would use the &quot;Debug&quot; option in query analyzer to step through the proc. You would then be able to see all the variables and what their value is.


Rob

 

[JohnDTampaBay]

@EmployeesTo is an integer. To include it in the dynamic sql, you will need to cast it as varchar.

IF (@EmployeesTo != NULL)
BEGIN
IF (@filterOn = 1)
BEGIN
SET @query = @query + ' AND NoEmp <= ' + CAST(@EmployeesTo AS varchar)
END

IF (@filterOn != 1)
BEGIN
SET @query = @query + ' WHERE NoEmp <= ' + CAST(@EmployeesTo AS varchar)
SET @filterOn = 1
END
END


--Angel
-----------------------------------
Every time I lose my mind, I wonder
if it's really worth finding.

 

[matthewking]

Thanks NoCoolHandle for some useful information, and many thanks to AngelWPB for solving my problem

Matt.

 

[JohnDTampaBay]

Glad to be of service.

--Angel
-----------------------------------
Every time I lose my mind, I wonder
if it's really worth finding.