conversion failed when exec stored procedure

Question

4.00/5 (1 vote)

See more:

SQL-Server-2005

, +

I write a stored procedure:-such as

SQL

alter proc SP_Useraddress(@us_id int)
as
begin
DECLARE @Res varchar(5000)
SET @Res=(SELECT  US_Hno + ', ' + US_Street+ CHAR(13) + CHAR(10) + US_Area+ CHAR(13) + CHAR(10)+
 US_City+ CHAR(13) + CHAR(10)+US_Country+ CHAR(13) + CHAR(10)+US_Mobile AS Address  FROM TBL_User where US_ID=@us_id)
return @Res
end

It execute successfullyl.But when i execute this procedure then give error such as:

Msg 245, Level 16, State 1, Procedure SP_Useraddress, Line 11
Conversion failed when converting the varchar value '#21, M.Palya
Malleshwaram
Bangalore
India
8050552756' to data type int.
I am not able to solve this. I am trying to get out put like this

VB

#21, M.Palya
Malleshwaram
Bangalore
India
8050552756

When I am using print then it is not giving error.But it is not get store in dataset in forented.

Posted 2-Aug-12 2:59am

Raju2049

Add a Solution

3 solutions

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Sebastian T Xavier · Answer 1 · 2012-08-02T07:01:00

Solution 1

I guess that you are assigning the output of stored procedure to an integer variable. If that's true please change the variable to a string. Please post your result.

Regards
Sebastian

Posted 2-Aug-12 7:01am

Sebastian T Xavier

devsuja · Answer 2 · 2012-08-02T23:26:00

Solution 2

hi,

u should not return value int as u have declared @res as varchar

Posted 2-Aug-12 23:26pm

devsuja

_Amy · Answer 3 · 2012-08-02T23:40:00

Try converting all your integer values to VARCHAR. Try this:

SQL

ALTER PROC SP_Useraddress(@us_id int)
as
begin
DECLARE @Res varchar(5000)
SET @Res=(SELECT  CONVERT(VARCHAR(20),US_Hno) + ', ' + CONVERT(VARCHAR(20),US_Street)+ CHAR(13) + CHAR(10) +
    CONVERT(VARCHAR(20),US_Area)+ CHAR(13) + CHAR(10)+
 US_City+ CHAR(13) + CHAR(10)+US_Country+ CHAR(13) + CHAR(10)+CONVERT(VARCHAR(20),US_Mobile) AS Address  FROM TBL_User where US_ID=@us_id)
return @Res
end

--Amit