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