Post reply

Stored Procedure Syntax Help Needed

  • May 25, 2006 at 10:55 am

    #169712

    Hi All:

    I'm having a problem with this Stored procedure. It's complaining about the IF and CONVERT statements. See source code below and thanks for your asistance

    William

    SET QUOTED_IDENTIFIER ON

    GO

    SET ANSI_NULLS ON

    GO

    CREATE procedure [qryTransByVendorByDateRange_Prm]

    @qryBeginDate DateTime, @qryEndDate DateTime, @qryVendorID nvarchar(50), @qryCategory nvarchar(50)

    AS

    DECLARE @ExtendedCost money

    DECLARE @ExtendedPrice money

    DECLARE @GrossMargin money

    DECLARE @Percentage int

    SELECT Daily_Transactions.TransactionID, Daily_Transactions.TransactionDate, Daily_Transactions.ClientID,

    CUBS_Clients.ClientName, Daily_Transactions.BranchID, Daily_Transactions.VendorID, Vendors.VendorName,

    Daily_Transactions.ServiceName, Daily_Transactions.Item, Daily_Transactions.Category,

    Daily_Transactions.Quantity, Daily_Transactions.UnitCost,

    CONVERT (money, [Quantity]*[UnitCost]) AS ExtendedCost,

    Daily_Transactions.UnitPrice,

    CONVERT (money, [Quantity]*[UnitPrice]) AS ExtendedPrice,

    CONVERT (money, [ExtendedPrice]-[ExtendedCost]) AS GrossMargin,

    IF @ExtendedPrice != '0'

    BEGIN

    CONVERT(money, [GrossMargin]/[ExtendedPrice]) AS Percentage,

    END

    Daily_Transactions.VendorTransmitID, Daily_Transactions.DateTransmitted, Daily_Transactions.DateMailed,

    Daily_Transactions.Posted, Daily_Transactions.DatePosted, Daily_Transactions.PeriodEnding

    FROM (Daily_Transactions

    LEFT JOIN CUBS_Clients ON Daily_Transactions.ClientID = CUBS_Clients.ClientID)

    LEFT JOIN Vendors ON Daily_Transactions.VendorID = Vendors.VendorID

    WHERE Daily_Transactions.TransactionDate Between @qryBeginDate AND @qryEndDate

    AND Daily_Transactions.VendorID = @qryVendorID

    AND Daily_Transactions.Category LIKE @qryCategory

    ORDER BY Daily_Transactions.TransactionDate, CUBS_Clients.ClientName;

  • May 25, 2006 at 10:59 am

    #640127

    IF is for control flow, it is not valid syntax to use it within a SELECT statement.

    Use CASE ... WHEN instead:

    CASE

    WHEN @ExtendedPrice != '0' THEN CONVERT(money, [GrossMargin]/[ExtendedPrice]) 

    -- what happens when @ExtendedPrice = '0' ? Output NULL ?

    ELSE  NULL

    END AS Percentage

  • May 25, 2006 at 11:23 am

    #640133

    That took care of it - thanks for your help!

    William

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

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