Results 1 to 3 of 3
  1. #1
    Join Date
    Mar 2002
    Posts
    192

    Unanswered: IF statements in a stored procedure.

    I have a Stored procedure that runs an Action Query.

    Here is a fragment of the query that i'm interested in. SQL Server says the there is something wrong here i.e. ERROR NEAR IF STATEMENT. All the examples that i've seen use variables instead of field names in the Condition part. Is that what's screwing up the syntax?

    IF SubString([dtm].[dbo].[sop30200].[CUSTNMBR],7,1) <> " "
    SubString([dtm].[dbo].[sop30200].[CUSTNMBR],1,6) & ' ' & SubString([dtm].[dbo].[SOP30200].[CUSTNMBR],8,Len([dtm].[dbo].[SOP30200].[CUSTNMBR])) AS CUSTNMBR,
    ELSE
    [SOP30200].[CUSTNMBR]) AS CUSTNMBR,


    The full store procedure is below if you need more info.

    CREATE PROCEDURE spMakTblClientPurchases

    AS
    SELECT
    [dtm].[dbo].[SOP30200].[DOCDATE],
    [dtm].[dbo].[SOP30200].[SOPNUMBE],
    IF SubString([dtm].[dbo].[sop30200].[CUSTNMBR],7,1) <> " "
    SubString([dtm].[dbo].[sop30200].[CUSTNMBR],1,6) & ' ' & SubString([dtm].[dbo].[SOP30200].[CUSTNMBR],8,Len([dtm].[dbo].[SOP30200].[CUSTNMBR]))
    ELSE
    [SOP30200].[CUSTNMBR]) AS CUSTNMBR,
    [dtm].[dbo].[SOP30200].[CNTCPRSN],
    [dtm].[dbo].[SOP30300].[ITEMNMBR],
    [dtm].[dbo].[SOP30300].[ITEMDESC],
    [dtm].[dbo].[SOP10201].[SERLTNUM],
    IF [dtm].[dbo].[SOP30300].[SOPTYPE]=4
    [dtm].[dbo].[SOP30300].[QUANTITY]* -1
    ELSE
    [dtm].[dbo].[SOP30300].[QTYTOINV] AS QUANTITY,
    [dtm].[dbo].[IV00101].[CURRCOST],
    [dtm].[dbo].[SOP30300].[UNITPRCE],
    [dtm].[dbo].[SOP30200].[CSTPONBR]
    INTO tblClientPurchases
    FROM [dtm].[dbo].[IV00101] INNER JOIN
    (([dtm].[dbo].[SOP30200] INNER JOIN [dtm].[dbo].[SOP30300]
    ON ([dtm].[dbo].[SOP30200].[SOPNUMBE] = [dtm].[dbo].[SOP30300].[SOPNUMBE])
    AND ([dtm].[dbo].[SOP30200].[SOPTYPE] = [dtm].[dbo].[SOP30300].[SOPTYPE]))
    LEFT JOIN [dtm].[dbo].[SOP10201]
    ON ([dtm].[dbo].[SOP30300].[CMPNTSEQ] = [dtm].[dbo].[SOP10201].[CMPNTSEQ])
    AND ([dtm].[dbo].[SOP30300].[LNITMSEQ] = [dtm].[dbo].[SOP10201].[LNITMSEQ])
    AND ([dtm].[dbo].[SOP30300].[SOPNUMBE] = [dtm].[dbo].[SOP10201].[SOPNUMBE])
    AND ([dtm].[dbo].[SOP30300].[SOPTYPE] = [dtm].[dbo].[SOP10201].[SOPTYPE]))
    ON [dtm].[dbo].[IV00101].[ITEMNMBR] = [dtm].[dbo].[SOP30300].[ITEMNMBR]
    WHERE
    ((([dtm].[dbo].[SOP30300].[SOPTYPE]) = 3 Or ([dtm].[dbo].[SOP30300].[SOPTYPE])=4) AND (([dtm].[dbo].[SOP30200].[VOIDSTTS])=0));
    GO

  2. #2
    Join Date
    Feb 2002
    Location
    Houston, TX
    Posts
    809
    use CASE instead:

    Syntax
    Simple CASE function:

    CASE input_expression
    WHEN when_expression THEN result_expression
    [ ...n ]
    [
    ELSE else_result_expression
    ]
    END

    Searched CASE function:

    CASE
    WHEN Boolean_expression THEN result_expression
    [ ...n ]
    [
    ELSE else_result_expression
    ]
    END
    Paul Young
    (Knowledge is power! Get some!)

  3. #3
    Join Date
    Jan 2003
    Location
    London, England
    Posts
    106
    You should remove the commas after each "method" (in lack of a better word) in the if-statement:

    Code:
    IF SubString([dtm].[dbo].[sop30200].[CUSTNMBR],7,1) <> " " 
      SubString([dtm].[dbo].[sop30200].[CUSTNMBR],1,6) & ' ' & SubString([dtm].[dbo].[SOP30200].[CUSTNMBR],8,Len([dtm].[dbo].[SOP30200].[CUSTNMBR])) AS CUSTNMBR, 
    ELSE 
      [SOP30200].[CUSTNMBR]) AS CUSTNMBR,
    should be something like
    Code:
    IF SubString([dtm].[dbo].[sop30200].[CUSTNMBR],7,1) <> " " 
      SELECT SubString([dtm].[dbo].[sop30200].[CUSTNMBR],1,6) & ' ' & SubString([dtm].[dbo].[SOP30200].[CUSTNMBR],8,Len([dtm].[dbo].[SOP30200].[CUSTNMBR])) AS CUSTNMBR
    ELSE 
      SELECT [SOP30200].[CUSTNMBR]) AS CUSTNMBR
    Frettmaestro
    "Real programmers don't document, if it was hard to write it should be hard to understand!"

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •