whats wrong with this stored procedure

Question

Post reply

whats wrong with this stored procedure

sukhoi971

SSCarpal Tunnel

Points: 4833
More actions
May 31, 2004 at 3:33 am

#108219

it is giving me error, but just now it was working and now it is not, i dont know what happend
i am calling this from my asp page it is showing this error
-----------Error message----------
Microsoft OLE DB Provider for ODBC Drivers error '80040e07'
[Microsoft][ODBC SQL Server Driver][SQL Server]Error converting data type varchar to numeric.
/tour/other_app.asp, line 37
---------------------Err---------------------------
my code in ASP page is -------
dbrec is a recordset
sql=" spFor_other_appasp '" & Session("usr") & "'"
dbrec.Open sql,con,1,3
sql=" spFor_other_appasp '" & Session("usr") & "'"
dbrec.Open sql,con,1,3
---stored procedure is-----------------
create procedure spFor_other_appasp
@usr as varchar(5)
as
select 'itemno'='1',RMCPL_TRANSNO,RMCPL_DATE,i1.RMCPL_ITEMNAME1 AS ITEM,RMCPL_REMARKS1 AS REMARKS,
i1.RMCPL_QTY_REQUIRED1 AS QTY,RMCPL_EMP_NAME
from RMCPL_OTHER_REQUISITION i1 inner join RMCPL_EMP_MASTER emp
on i1.RMCPL_EMP_CD=emp.RMCPL_EMP_CD and emp.RMCPL_REPORTING_TO=@usr
where Item1Approved is null and ExpectedDate1 is null AND i1.RMCPL_ITEMNAME1 <>''
UNION
select 'itemno'='2',RMCPL_TRANSNO,RMCPL_DATE,i1.RMCPL_ITEMNAME2 AS ITEM,RMCPL_REMARKS2 AS REMARKS,
i1.RMCPL_QTY_REQUIRED2 AS QTY,RMCPL_EMP_NAME
from RMCPL_OTHER_REQUISITION i1 inner join RMCPL_EMP_MASTER emp
on i1.RMCPL_EMP_CD=emp.RMCPL_EMP_CD and emp.RMCPL_REPORTING_TO=@usr
where Item2Approved is null and ExpectedDate2 is null AND i1.RMCPL_ITEMNAME2 <>''
union
select 'itemno'='3',RMCPL_TRANSNO,RMCPL_DATE,i1.RMCPL_ITEMNAME3 AS ITEM,i1.RMCPL_QTY_REQUIRED3 AS QTY,RMCPL_EMP_NAME ,RMCPL_REMARKS3 AS REMARKS
from RMCPL_OTHER_REQUISITION i1 inner join RMCPL_EMP_MASTER emp
on i1.RMCPL_EMP_CD=emp.RMCPL_EMP_CD and emp.RMCPL_REPORTING_TO=@usr
where Item3Approved is null and ExpectedDate3 is null AND i1.RMCPL_ITEMNAME3 <>''
union
select 'itemno'='4',RMCPL_TRANSNO,RMCPL_DATE,i1.RMCPL_ITEMNAME4 AS ITEM,i1.RMCPL_QTY_REQUIRED4 AS QTY,RMCPL_EMP_NAME ,RMCPL_REMARKS4 AS REMARKS
from RMCPL_OTHER_REQUISITION i1 inner join RMCPL_EMP_MASTER emp
on i1.RMCPL_EMP_CD=emp.RMCPL_EMP_CD and emp.RMCPL_REPORTING_TO=@usr
where Item4Approved is null and ExpectedDate4 is null AND i1.RMCPL_ITEMNAME4 <>''
<a href="http://www.websolsoftware.com"> For IT jobs click here</a>
*Sukhoi*[font="Arial Narrow"][/font]

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

sukhoi971 SSCarpal Tunnel Points: 4833 More actions · Answer 1

i tried it with numeric int, but it doesnt work.

<a href="http://www.websolsoftware.com"> For IT jobs click here</a>

*Sukhoi*[font="Arial Narrow"][/font]

Rohit @ SQL SSC Eights! Points: 938 More actions · Answer 2

This Error is Not Coming because of the parameter you are passing but the Prcessing you are doing in your procedure do one thing take the query from web page like

Response.write strSQL

Response.End

Now take this query to Query Anlyzer you will find what is the reason of error and where (line No)

Rohit

sukhoi971 SSCarpal Tunnel Points: 4833 More actions · Answer 3

i got the solution, basically it was not the parameter, but sql was giving message of datatype error, actually the error was in union

we need to have same order of columns in all the queries got it ?

<a href="http://www.websolsoftware.com"> For IT jobs click here</a>

*Sukhoi*[font="Arial Narrow"][/font]

AJ Ahrens SSC-Insane Points: 20676 More actions · Answer 4

Correct, The order of all the fields for a UNION have to be same same. If they are different you will wind up with the problem you just had

Good Hunting!

AJ Ahrens

webmaster@kritter.net