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!meendar (askjavaprogrammers@.gmail.com) writes:

Quote:

Originally Posted by

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, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||On May 2, 12:10 pm, Erland Sommarskog <esq...@.sommarskog.sewrote:

Quote:

Originally Posted by

meendar (askjavaprogramm...@.gmail.com) writes:

Quote:

Originally Posted by

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

>

Quote:

Originally Posted by

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

>

Quote:

Originally Posted by

...
Declare r1 cursor
local Scroll Keyset Optimistic
For
select z1 from employee a1 where z2= @.z1 + 45 ....

>

Quote:

Originally Posted by

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

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!You cannot execute the statement dynamically in this fashion.
Rather, try this...
-- For storing Unicode SQL statements to be executed on the fly.
DECLARE @.sql_statement_string nvarchar(1024)
-- Construct SQL statement to select
SET @.sql_statement_string =3D 'select z1 from employee a1 where z2=3D'
+ @.z1
+ ' + 45 ...'
-- Execute the SQL & insert activity details.
EXECUTE sp_executesql @.sql_statement_string
--Seenu
On May 2, 1:04=C2=A0am, meendar <askjavaprogramm...@.gmail.com> wrote:
> 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:
> CREATEPROCEDUREx1 (@.y1 AS numeric=3DNULL )AS
> declare @.z1 Varchar(200)
> =C2=A0 =C2=A0 =C2=A0 =C2=A0 begin
> =C2=A0 =C2=A0 =C2=A0 =C2=A0 set @.z1=3D 'and a1.id=3D'
> =C2=A0 =C2=A0 =C2=A0 =C2=A0 print @.y1
> =C2=A0 =C2=A0 =C2=A0 =C2=A0 print @.z1
> =C2=A0 =C2=A0 =C2=A0 =C2=A0 end
> Declare r1 cursor
> local Scroll Keyset Optimistic
> For
> select z1 =C2=A0from =C2=A0employee a1 where =C2=A0z2=3D @.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!|||On May 2, 7:42=C2=A0pm, =E0=AE=9A=E0=AF=80=E0=AE=A9=E0=AF=81 <srinivasan...=
@.gmail.com> wrote:
> You cannot execute the statement dynamically in this fashion.
> Rather, try this...
> =C2=A0 -- For storing Unicode SQL statements to be executed on the fly.
> =C2=A0 DECLARE @.sql_statement_string nvarchar(1024)
> =C2=A0 =C2=A0 -- Construct SQL statement to select
> =C2=A0 =C2=A0 =C2=A0 =C2=A0 SET @.sql_statement_string =3D 'select z1 =C2=
=A0from =C2=A0employee a1 where =C2=A0z2=3D'
> =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=
=A0 =C2=A0 =C2=A0+ @.z1
> =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=
=A0 =C2=A0 =C2=A0+ ' + 45 ...'
> =C2=A0 =C2=A0 -- Execute the SQL & insert activity details.
> =C2=A0 =C2=A0 EXECUTE sp_executesql @.sql_statement_string
> --Seenu
> On May 2, 1:04=C2=A0am, meendar <askjavaprogramm...@.gmail.com> wrote:
>
>
>
>
>
>
>
>
>
>
> - Show quoted text -
Thanks to All

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!
You cannot execute the statement dynamically in this fashion.
Rather, try this...
-- For storing Unicode SQL statements to be executed on the fly.
DECLARE @.sql_statement_string nvarchar(1024)
-- Construct SQL statement to select
SET @.sql_statement_string = 'select z1 from employee a1 where z2='
+ @.z1
+ ' + 45 ...'
-- Execute the SQL & insert activity details.
EXECUTE sp_executesql @.sql_statement_string
--Seenu
On May 2, 1:04Xam, meendar <askjavaprogramm...@.gmail.com> wrote:
> 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:
> CREATEPROCEDUREx1 (@.y1 AS numeric=NULL )AS
> declare @.z1 Varchar(200)
> X X X X begin
> X X X X set @.z1= 'and a1.id='
> X X X X print @.y1
> X X X X print @.z1
> X X X X end
> Declare r1 cursor
> local Scroll Keyset Optimistic
> For
> select z1 Xfrom Xemployee a1 where Xz2= @.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!
|||On May 2, 7:42Xpm, ???? <srinivasan...@.gmail.com> wrote:
> You cannot execute the statement dynamically in this fashion.
> Rather, try this...
> X -- For storing Unicode SQL statements to be executed on the fly.
> X DECLARE @.sql_statement_string nvarchar(1024)
> X X -- Construct SQL statement to select
> X X X X SET @.sql_statement_string = 'select z1 Xfrom Xemployee a1 where Xz2='
> X X X X X X X X X X X X X+ @.z1
> X X X X X X X X X X X X X+ ' + 45 ...'
> X X -- Execute the SQL & insert activity details.
> X X EXECUTE sp_executesql @.sql_statement_string
> --Seenu
> On May 2, 1:04Xam, meendar <askjavaprogramm...@.gmail.com> wrote:
>
>
>
>
>
>
> - Show quoted text -
Thanks to All