?
Solved

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

Posted on 2007-07-25
3
Medium Priority
?
1,285 Views
Last Modified: 2011-04-14
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!
0
Comment
Question by:Cyber-Drugs
3 Comments
 
LVL 15

Accepted Solution

by:
derekkromm earned 600 total points
ID: 19569329
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 cast(tblClients.UID as varchar(100))
WHEN 'Surname'
THEN tblClients.Surname
END = @Value
0
 
LVL 143

Assisted Solution

by:Guy Hengel [angelIII / a3]
Guy Hengel [angelIII / a3] earned 400 total points
ID: 19569337
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 cast(tblClients.UID as varchar(128))
WHEN 'Surname'
THEN tblClients.Surname
END = @Value

0
 
LVL 4

Author Comment

by:Cyber-Drugs
ID: 19569543
Aha!!

I tried casting the Surname, but that never worked, I never thought of casting the UID.

Cheers guys!
0

Featured Post

Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Ready to get certified? Check out some courses that help you prepare for third-party exams.
One of the most important things in an application is the query performance. This article intends to give you good tips to improve the performance of your queries.
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…
Suggested Courses

864 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question