store procedure problem

  • i have one stored procedure

    spfor_requisition_approvallist

    there are 2 parameters to pass

    one is the employee code and another is the sample name

    '28','%%'

    stored procedure code is

    -----------------------------------

    create procedure spfor_requisition_approvallist

    @usr as varchar(5),

    @sam-3 as varchar(100)

    as

    SElECT DISTINCT(RMCPL_REQUISITION.RMCPL_Tid),RMCPL_SAMPLE_NAME,RMCPL_EXPECTED_DT,

    Rmcpl_approved_date,AprRemarks,RMCPL_REQUISITION.RMCPL_EMP_CD,RMCPL_EMP_MASTER.RMCPL_EMP_CD,

    RMCPL_EMP_MASTER.RMCPL_EMP_NAME,RMCPL_REQUISITION.RMCPL_DATE,Remarks,RMCPL_REQUISITION.RMCPL_QTY_APPROVED

    FROM RMCPL_REQUISITION,RMCPL_EMP_MASTER,RMCPL_SAMPLE_MASTER

    WHERE RMCPL_REQUISITION.RMCPL_EMP_CD=RMCPL_EMP_MASTER.RMCPL_EMP_CD

    and RMCPL_REQUISITION.RMCPL_SAMPLE_CD=RMCPL_SAMPLE_MASTER.RMCPL_SAMPLE_CD

    and RMCPL_EMP_MASTER.RMCPL_EMP_CD =29 and RMCPL_SAMPLE_NAME like @sam-3

    ORDER BY RMCPL_EMP_MASTER.RMCPL_EMP_NAME

    when i pass parameters

    when i fire this query or command in

    query analyzer

    spfor_requisition_approvallist '28','%%'

    it shows wrong results

    when i copy and paste this query in sql query analyzer it gives correct result but

    SElECT DISTINCT(RMCPL_REQUISITION.RMCPL_Tid),RMCPL_SAMPLE_NAME,RMCPL_EXPECTED_DT,

    Rmcpl_approved_date,AprRemarks,RMCPL_REQUISITION.RMCPL_EMP_CD,RMCPL_EMP_MASTER.RMCPL_EMP_CD,

    RMCPL_EMP_MASTER.RMCPL_EMP_NAME,RMCPL_REQUISITION.RMCPL_DATE,

    Remarks,RMCPL_REQUISITION.RMCPL_QTY_APPROVED

    FROM RMCPL_REQUISITION,RMCPL_EMP_MASTER,RMCPL_SAMPLE_MASTER

    WHERE RMCPL_REQUISITION.RMCPL_EMP_CD=RMCPL_EMP_MASTER.RMCPL_EMP_CD

    and RMCPL_REQUISITION.RMCPL_SAMPLE_CD=RMCPL_SAMPLE_MASTER.RMCPL_SAMPLE_CD

    and RMCPL_EMP_MASTER.RMCPL_EMP_CD =28 and RMCPL_SAMPLE_NAME like '%%'

    GO

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

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

  • In Procedure u Hotcoded the employee code 29 .but in ur query employee code 28.this will give the wrong result set.Please check the employee code.U not used the @usr in procedure.please check that.

     

     

     

  • Lol,might be so, but i am right now using

    @usr

    and + an if condition to check the lenght

    of @usr variable for 2

    if len(@usr)=2 then

    else

    that way iam using 2 queries now.

    and its working, thanks anway for the solution

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

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

Viewing 3 posts - 1 through 3 (of 3 total)

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