vendredi 22 mai 2015

Stored Procedure in SQL 2008 Not Working in 2005

Hi I'm Trying to Create Stored Procedured in SQL2005 below is my syntax

CREATE PROCEDURE [dbo].[POBalance]  @PONumber nvarchar(50)
AS BEGIN

declare @status bit = (Select status from tblPOHeader where PONo = @PONumber  )
if @status = 'False'

                Select 
                    A.Description,
                    C.qty as POqty,
                    B.QtyDelivered as PDQty, 
                    case when A.partialflag ='false' 
                    then '0'
                    else
                    A.qty  end as Balance,
                    A.Unit,
                    A.Unitprice,
                    A.Partialflag 
            from tblPOdetails as A

            Inner Join  ( SELECT  id, SUM(Qty) AS QtyDelivered
                                                    FROM             dbo.tblPDdetails
                                                    WHERE     (PONo = @PONumber)
                                                    GROUP BY id)as B On A.id = B.id   
            Inner Join tblpodetailshistory as C on A.id =C.id

            where A.PONo = @PONumber
ELSE

            Select 
                    A.Description,
                    C.qty as POqty,
                    B.QtyDelivered as PDQty, 
                    '0' as Balance,
                    A.Unit,
                    A.Unitprice,
                    A.Partialflag 
            from tblPOdetails as A

            Inner Join  ( SELECT  id, SUM(Qty) AS QtyDelivered
                                                    FROM          dbo.tblPDdetails
                                                    WHERE     (PONo =  @PONumber)
                                                    GROUP BY id)as B On A.id  = B.id   
            Inner Join tblpodetailshistory as C on A.id =C.id

            where A.PONo = @PONumber
END

When i execute i execute i got this error.

Msg 102, Level 15, State 1, Procedure POBalance, Line 13
Incorrect syntax near '('.
Msg 137, Level 15, State 2, Procedure POBalance, Line 14
Must declare the scalar variable "@status".
Msg 156, Level 15, State 1, Procedure POBalance, Line 36
Incorrect syntax near the keyword 'ELSE'.

But that syntax is already working in SQL2008 and no problem. im wondering if there is different syntax between 2005 and 2008..

Could anyone have idea about this.

Thank you!

Aucun commentaire:

Enregistrer un commentaire