troubleshooting Question

MSSQL - Stored Procedure error - Conversion failed when converting the varchar value 'Smith' to data type int.

Avatar of Cyber-Drugs
Cyber-DrugsFlag for United Kingdom of Great Britain and Northern Ireland asked on
Microsoft SQL ServerMicrosoft SQL Server 2005
3 Comments2 Solutions1351 ViewsLast Modified:
Hi guys 'n gals,

I have a bit of a problem with my Stored Procedure...

I pass in a string, and search against a varchar, and for some reason it thinks I am trying to cast from varchar to int?

Here is my execution strings:

// throws an error
EXEC sp_Search 'Surname', 'Smith'
// works fine
EXEC sp_Search 'UID', '5'

Here is my error:

Conversion failed when converting the varchar value 'Smith' to data type int.

Here is my stored procedure:

CREATE PROCEDURE sp_Search
@Column varchar(64),
@Value varchar(128)
AS
SELECT
tblClients.Firstname,
tblClients.Surname,
tblClients.Address1,
tblClients.Address2
FROM
tblClients
WHERE
CASE @Column
WHEN 'UID'
THEN tblClients.UID
WHEN 'Surname'
THEN tblClients.Surname
END = @Value


Database DataTypes:

tblClients.UID = int(4)
tblClients.Surname = varchar(128)



Anybody able to help me out please?


Cheers!
Join the community to see this answer!
Join our exclusive community to see this answer & millions of others.
Unlock 2 Answers and 3 Comments.
Join the Community
Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 2 Answers and 3 Comments.
Try for 7 days

”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.

-Mike Kapnisakis, Warner Bros