473,320 Members | 1,828 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,320 software developers and data experts.

Problem with procedure

Hi,

I have selected a field name and declared it as varchar, since it is
varchar in table and performed some numeric operation with numbers,

even after i cast the sql in below code, it throws an exception as
"Error converting data type varchar to numeric."

code:

CREATE PROCEDURE x1 (@y1 AS numeric=NULL )AS
declare @z1 Varchar(200)

begin
set @z1= 'and a1.id='
print @y1
print @z1
end

Declare r1 cursor
local Scroll Keyset Optimistic
For
select z1 from employee a1 where z2= @z1 + 45 .....
I want to clear that how can we cast the field with varchar for
numeric operations, i have also tried cast and convert to change it
but all in vain.

Thanks in Advance!

May 2 '07 #1
2 1959
meendar (as****************@gmail.com) writes:
I have selected a field name and declared it as varchar, since it is
varchar in table and performed some numeric operation with numbers,

even after i cast the sql in below code, it throws an exception as
"Error converting data type varchar to numeric."

...
Declare r1 cursor
local Scroll Keyset Optimistic
For
select z1 from employee a1 where z2= @z1 + 45 .....
I want to clear that how can we cast the field with varchar for
numeric operations, i have also tried cast and convert to change it
but all in vain.
SQL Server tries to convert all values in employee.z2 to numeric, and
when this fails for some value, the query fails.

You need to use the CASE expression:

WHERE CASE WHEN ltrim(rtrim(z2)) NOT LIKE '%[^0-9]%'
THEN convert(numeric, z2)
END = @z1 + 45

Now it will only attempt to convert z2 which it consists of digits only.

--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx
May 2 '07 #2
On May 2, 12:10 pm, Erland Sommarskog <esq...@sommarskog.sewrote:
meendar (askjavaprogramm...@gmail.com) writes:
I have selected a field name and declared it as varchar, since it is
varchar in table and performed some numeric operation with numbers,
even after i cast the sql in below code, it throws an exception as
"Error converting data type varchar to numeric."
...
Declare r1 cursor
local Scroll Keyset Optimistic
For
select z1 from employee a1 where z2= @z1 + 45 .....
I want to clear that how can we cast the field with varchar for
numeric operations, i have also tried cast and convert to change it
but all in vain.

SQL Server tries to convert all values in employee.z2 to numeric, and
when this fails for some value, the query fails.

You need to use the CASE expression:

WHERE CASE WHEN ltrim(rtrim(z2)) NOT LIKE '%[^0-9]%'
THEN convert(numeric, z2)
END = @z1 + 45

Now it will only attempt to convert z2 which it consists of digits only.

--
Erland Sommarskog, SQL Server MVP, esq...@sommarskog.se

Books Online for SQL Server 2005 athttp://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books...
Books Online for SQL Server 2000 athttp://www.microsoft.com/sql/prodinfo/previousversions/books.mspx- Hide quoted text -

- Show quoted text -
Thanks to All

May 4 '07 #3

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

2
by: Kent Lewandowski | last post by:
hi all, Recently I wrote some stored procedures using java jdbc code (admittedly my first stab) and then tried to implement the same within java packages (for code reuse). I encountered...
6
by: Not4u | last post by:
Hello Config : SQL 2000 on WIN 2000 (IIS 5.0) In my ASP page for some queries i have this error : Microsoft OLE DB Provider for SQL Server error '80040e31' Timeout expired
4
by: Jeff User | last post by:
Hi I tryed to solve this problem over in the framework.asp group, but still am having trouble. Hope someone here can help. using .net 1.1, VS 2003 and C# I have an asp.DataGrid control with a...
7
by: Dabbler | last post by:
I'm using an ObjectDataSource with a stored procedure and am getting the following error when trying to update (ExecuteNonQuery): System.Data.SqlClient.SqlException: Procedure or Function...
8
by: billmiami2 | last post by:
I'm experiencing a strange problem that I believe is related to ADO.NET but I can't say for sure. I have a simple ASP.NET reporting interface to a SQL Server 2000 database. One report that we...
4
by: nishi57 | last post by:
I hope I can get some help regarding this issue, which has been going on for a while. I have a desktop user who is having problem running "Stored Procedures". The DB2 Connect application works fine...
12
by: Light | last post by:
Hi all, I posted this question in the sqlserver.newusers group but I am not getting any response there so I am going to try it on the fine folks here:). I inherited some legacy ASP codes in my...
1
by: amgupta8 | last post by:
Note: This problem occurred when I updated the JDK from 1.3.1 to 1.4.1 or 1.4.2. Nothing else was changed in the code, other than updating the JDK on the database server (dbm cfg parm jdk_path) and...
10
by: trakal | last post by:
Hello everybody, i have a problem that i don't know how to solve it. I created a procedure like this: create or replace PROCEDURE Employee_LoadById ( p_Id NVARCHAR2 ) AS
13
nabh4u
by: nabh4u | last post by:
hi, I am trying to insert multiple rows in a table using PL/SQL. I have two procedures and two tables. the first procedure gets the data from the source table, manipulates it and then sends the...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
0
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
0
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.