Hi All

I have written one stored procedure and getting syntax error. I m posting just
a part of my stored procedure

set @strNonSwipeDts= ''''+REPLACE(@strNonSwipeDts,',',''',''')+''''

set @str = 'update leave_application
         set
              leave_code=current1.leave_code,leave_ampm=current1 .leave_ampm,
              status=current1.status,
              from_date = current1.from_date,
              to_date = current1.to_date,
              duration=cast(current1.duration as varchar),
              report_to=cast(current1.ReportingID as varchar)
        from openxml('+ cast(@hDoc as varchar) +',''/NewDataSet/current1'',2)
        with(Empno varchar(10),leave_refno varchar(10),leave_code varchar(10),leave_ampm varchar(10),
        status char(2),from_date varchar(50),to_date varchar(50),duration varchar(50),Reporting_To varchar(10),ReportingID varchar(10))current1
        WHERE
        leave_application.empno ='''+ @empno +''' and
        leave_date in ('+ @strNonSwipeDts +')' + ''
        print(@str)
        exec(@str)


i have declared everything. This is just part of my stored procedure where the error lies(syntax error)

error is : Incorrect syntax near '185'

185 is the @hDoc here. Please advice where syntactically i went wrong.

I don't think you can cast a number to a varchar the way you are doing it. I would use the STR() function instead. You'll need to trim it after the conversion or you'll have extra spaces in the result.

-Peter
compiledthoughts.com

Hi Peter

I used STR() as you advised. This is what i changed----
from openxml(' +ltrim(cast(str(@hDoc) as varchar)) +',''/NewDataSet/current1'',2)

but it is still throwing the same error.
What else can be done.

Are you sure 185 is the value for @hDoc and not @empno? Assuming that leave_application.empno is a numeric field, this line doesn't look right:

  leave_application.empno ='''+ @empno +''' and

You are wrapping what I assume is a numeric value in quotes. Try removing the quotes:

  leave_application.empno ='+ @empno +' and

-Peter
compiledthoughts.com

Hi Peter

yeah, it is the value for @hDoc only. I removed the quotes but still again the same error. This is what i get when i execute (@str)

------------- this is generated due to print(@str)-------------------------------
update leave_application
         set
              leave_code=current1.leave_code,leave_ampm=current1 .leave_ampm,
              status=current1.status,
              from_date = current1.from_date,
              to_date = current1.to_date,
              duration=cast(current1.duration as varchar),
              report_to=cast(current1.ReportingID as varchar)
        from openxml(265,'/NewDataSet/current1',2)

        with(Empno varchar(10),leave_refno varchar(10),leave_code varchar(10),leave_ampm varchar(10),
        status char(2),from_date varchar(50),to_date varchar(50),duration varchar(50),Reporting_To varchar(10),ReportingID varchar(10))current1
        WHERE
        leave_application.empno ='1214' and
        leave_date in ('08/09/2004','08/24/2004')
Msg 102, Level 15, State 1, Line 9
Incorrect syntax near '265'.

(0 row(s) affected)