sql server varchar to int conversion error

maqskywalker
maqskywalker used Ask the Experts™
on
i'm using sql server 2008

I have this query

DECLARE @EmployeeLastName AS VARCHAR(50)

-- Test Case 1 - Multiple Employees
--SET @EmployeeLastName = 'Johnson,Jackson,Roberts'

-- Test Case 2 - All Employees
--SET @EmployeeLastName = -1

-- Test Case 3 - Single Employee
SET @EmployeeLastName = 'Jackson'

-------------------

IF @EmployeeLastName LIKE '%,%'

	BEGIN  

	-- Test Case 1
	SELECT 'Multiple Employees Selected' AS EmployeesSelectedText 

	END  

ELSE IF @EmployeeLastName = -1

	BEGIN  

	-- Test Case 2
	SELECT 'All Employees' AS EmployeesSelectedText 

	END

--ELSE
ELSE 

	BEGIN  

	SELECT 'Single Employee Selected' AS EmployeesSelectedText 

	END;

Open in new window


When I use Test Case 1 Parameter  like this it works fine:

TC1.PNG
When I use Test Case 2 Parameter  like this it works fine:

TC2.PNG
When I use Test Case 3 Parameter  like this I get an error

TC3.PNG

Any idea of why I'm getting an error when I use this ?

SET @EmployeeLastName = 'Jackson'
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
SQL Server Data Dude
Most Valuable Expert 2013
Author of the Year 2015
Commented:
In the below line @EmployeeLastName is a varchar(50) and -1 is numeric, hence the error.   '-1' would work though.
ELSE IF @EmployeeLastName = -1

Open in new window


Also just for kicks and giggles double-click on the error message, watch the cursor jump to the offending line, and tell us what line that is.

The reason the error wasn't thrown until Test Case 3 is that code execution didn't get that far in 1, and  -1 = -1 worked via implicit conversion for Test Case 2.

Author

Commented:
Thanks setting it to this @EmployeeLastName = '-1'  worked.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial