How do I convert Access queries to SQL Server 7

I have a Access query with A IIF statement in the SELECT part of the query I need to convert this query to a Store procedure in SQL 7 How?
e.g.

SELECT IIf(IsNull(MoveNumber),0,MoveNumber) AS MovementNumber,
A.MDate AS MovementDate,
IIf(IsNull(MoveNumber),'** AUDIT **',
IIf(IsNull(C.CNumber),'** MISSING **',
C.CNumber & ' - ' & C.dsName)) AS CustomerFrom,
IIf(IsNull(D.CNumber),'** MISSING **',
D.CNumber & ' - ' & D.dsName) AS CustomerTo
FROM ((tblCabinetMovements AS A INNER JOIN tblCabinets AS B ON
 A.CabinetID = B.CabinetID) LEFT JOIN tblCustomers AS C ON
 A.CustomerIDFrom = C.CustomerID) LEFT JOIN tblCustomers AS D ON A.CustomerIDTo = D.CustomerID
WHERE (((B.PPNumber)=[strCriteria]))
ORDER BY A.MDate DESC
RabAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

FarewellIIArmsCommented:
Hi Rab,

Easy to do(less text), use the 'IsNull' function of SQL only problem you will have is your IIF statement appears to return a different datatype than the checked field(IIf(IsNull(MoveNumber),'** AUDIT **') You might have to caste the checked value to string like so - IsNull(cstr(MoveNumber),'** AUDIT **')

Text from Transac-SQL help below.

ISNULL Function
Replaces NULL with the specified replacement value.
Syntax

ISNULL(check_expression, replacement_value)

Arguments

check_expression

Is the expression to be checked for NULL. check_expression can be of any type.

replacement_value

Is the expression to be returned if check_expression is NULL. replacement_value must have the same type as check_expresssion.

Return Types

Returns the same type as check_expression.

Remarks

The value of check_expression is returned if it is not NULL; otherwise, replacement_value is returned.

Examples
A.    Use ISNULL with AVG

This example finds the average of the prices of all titles, substituting the value $10.00 for all NULL entries in the price column of the titles table.

USE pubs

GO

SELECT AVG(ISNULL(price, $10.00))

FROM titles

GO
0
FarewellIIArmsCommented:
Hi again Rab,

The 'cstr' in my answer should have said 'Cast' like so CAST(price AS varchar(12)).
0
RabAuthor Commented:
The problem is with the IIF statement
IS THERE A IIF STATEMENT IN SQL IF SO HOW DO I USE IT? WHAT OTHER OPTION IS THERE FOR ME TO USE.
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

OllyWCommented:
Hi Rab,

There are at least two possible options. The easiest is the ISNULL function. This is more powerful then access' version. It has the syntax:

ISNULL(check_expression, replacement_value)

Therefore you can replace all your null values with '** AUDIT **', etc.

Another option is to use the CASE statment like this:

CASE WHEN Movenumber IS NULL THEN '** AUDIT **'
ELSE Movenumber

The case function can be used inline with SELECT statments. The Transact-SQL online help should be of help and give examples.

Hope this is of help
0
FarewellIIArmsCommented:
Hi Again Rab,

Along with the Case & IsNull there is also an If/Else construct or a Coalese

If/Esle has exactly the same functionality as IIF - it just more typing, Coalese will select the first non-null value from a list of fields.
0
RabAuthor Commented:
Thanks you guys but How do I use the IIF or If/Esle with this problem of myne I have tride but no success
0
OllyWCommented:
FarewellIIArms is right, there is IF/ELSE and Coalese, but a word of caution: I am pretty sure the If/Else won't work in inline SQL, only in SQL statement blocks. Therefore, your stored procedure would be a lot more complex and less efficent.
0
Jeremy_DCommented:
Hi Rab, this is OTTOMH, so if you get errors or unexpected results, let me know and we'll review it.
This should be it I think:

=====================================
CREATE PROCEDURE sp_myproc (@criteria varchar(8000)) AS
SELECT
  COALESCE(MoveNumber, 0) AS MovementNumber,
  A.MDate AS MovementDate,
  CustomerFrom =
    CASE WHEN MoveNumber IS NULL
      THEN
        '** AUDIT **'
      ELSE
        CASE WHEN C.CNumber IS NULL
          THEN
            '** MISSING **'
          ELSE
            C.CNumber & ' - ' & C.dsName
        END
    END,
  CustomerTo =
    CASE
      WHEN D.CNumber IS NULL
        THEN
          '** MISSING **'
        ELSE
          D.CNumber & ' - ' & D.dsName
    END
FROM
  ((tblCabinetMovements A INNER JOIN tblCabinets B ON A.CabinetID = B.CabinetID)
  LEFT JOIN tblCustomers C ON
 A.CustomerIDFrom = C.CustomerID)
  LEFT JOIN tblCustomers D ON A.CustomerIDTo = D.CustomerID
WHERE B.PPNumber = @criteria
ORDER BY A.MDate DESC

GO
=====================================



0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
RabAuthor Commented:
Thanks
0
Jeremy_DCommented:
Welcome
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Visual Basic Classic

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.