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:
>
> > 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!- Hide quoted text -
> - Show quoted text -
Thanks to All