I've got a 2-fold problem if anyone has the time to look at it:

[u]Problem 1</u> (use of the IF ELSE)I am trying to sort based on the value that is passed on to this stored procedure but I am getting an error stating: ERROR 156 INCORRECT SYNTAX NEAR THE KEYWORD "ORDER" for each "ORDER BY STATEMENT". Here is that code:

SELECT DISTINCT hu.hcin_user_id as 'Physician_hcin_user_id', MIL.Hcin_user_id as 'Surrogate_hcin_user_id',
        rtrim(hu2.lastname) + ', ' + rtrim(hu2.firstname) as 'Surrogate_User',
        cast(wl.Access_Date as smalldatetime) as Access_Date, wl.Patient_Last, wl.Patient_First, wl.Patient_ID,
        wl.Med_Rec_No, hlo.ods_enterprsfacilitycode, crr.Descrptn as 'Relationship_Access'
    FROM hcin_user hu
    LEFT OUTER join hosp_status_link HSL
    ON HSL.Hcin_user_ID = hu.Hcin_user_Id -- get hosp affiliations of drs.
    LEFT OUTER join Hcin_Module_Info HMI
    ON HMI.Hcin_user_id = hu.hcin_user_id -- get physician hcin_user_id of persons mf id
    AND HMI.Description = 'PHIS_ID'
    LEFT OUTER Join mainframe_ID_lookup MIL
    ON hmi.value = MIL.Mainframe_id --get hcin_user_Ids of surrogates
    LEFT OUTER join hcin_user hu2
    ON mil.hcin_user_id = hu2.hcin_user_id --get names of surrogates
    LEFT OUTER join web_log wl
    ON wl.hcin_user_id = mil.hcin_user_id --get weblog activity
    LEFT OUTER join hospital_link_ods hlo
    ON wl.hospital_id = hlo.hospital_id
    LEFT OUTER JOIN clinical_reporting_relationships crr
    ON wl.relationship_id = crr.relatnshp_id --get relationship information
    WHERE hu.isphysician = 1
    AND substring(MIL.Mainframe_ID,4,3) <> 'ALL' -- ignore "All" mainframe ID's
    AND HSL.status_ID not in (1,15) -- status is not denied
    AND hu.helpdesk_clientid <> 10900 -- eliminate pss staff
    AND hu.accountStatus not in (-1, -3, -6) -- eliminate users disabled by pss, accnt expired or disabled by phys from caregate
    AND datepart(month, wl.access_date) = @arch_month
    AND datepart(year, wl.access_date) = @arch_year
    AND hu.hcin_user_id = @hcin_user_id
    AND crr.Descrptn IS NOT NULL
-- AND wl.report_name <> 'patient selection'
-- AND wl.report_name <> 'orders list' -- eliminate the orders list, it is a list of links to result pages (lab, rad, cvs, etc.)
    AND wl.rpt_file_name = 'header request' -- will filter nearly all of the extraneous web_log entries (like... 'search request', 'patient access verified', 'add patient: SUCCESS (0)', etc.)
-- GROUP BY mil.hcin_user_id, hu.hcin_user_id, hu2.lastname, hu2.firstname, wl.access_date,
-- wl.report_name, wl.patient_last, wl.patient_first, wl.patient_id, wl.med_rec_no, hlo.ods_enterprsfacilitycode, crr.descrptn

    UNION

--Presby Physicians and their surrogates.

SELECT DISTINCT crm.hcin_user_id as 'Physician_hcin_user_id',
crm.surrogate_id as 'Surrogate_hcin_user_id', rtrim(hu.lastname) + ', ' + rtrim(hu.firstname) as 'Surrogate_User',
cast(wl.Access_Date as smalldatetime) as Access_Date, wl.Patient_Last, wl.Patient_First, wl.Patient_ID,
wl.Med_Rec_No, hlo.ods_enterprsfacilitycode, crr.Descrptn as 'Relationship_Access'

FROM clinical_reporting_main crm

INNER Join Hcin_user hu

ON hu.hciN_user_id = crm.surrogate_id

INNER join web_log wl

ON wl.hcin_user_id = crm.surrogate_id

LEFT OUTER join hospital_link_ods hlo

ON wl.hospital_id = hlo.hospital_id

LEFT OUTER JOIN clinical_reporting_relationships crr

ON wl.relationship_id = crr.relatnshp_id

WHERE accountstatus = 0 -- active accounts only
AND datepart(month,wl.access_date) = @arch_month
AND datepart(year, wl.access_date) = @arch_year
AND crm.hcin_user_id = @hcin_user_id
AND crr.Descrptn IS NOT NULL

IF @sort_by IS NULL
    ORDER BY Surrogate_User
ELSE
        IF @sort_by = 'datetime'
    ORDER BY Surrogate_User, wl.access_date
ELSE
    IF @sort_by = 'pt_name'
    ORDER BY Surrogate_User, wl.patient_last, wl.patient_first
ELSE
    IF @sort_by = 'hosp'
    ORDER BY Surrogate_user, hlo.ods_enterprsfacilitycode

Now [u]Problem 2</u>

In my CASE Statement below I am getting this error:

INCORRECT SYNTAX NEAR '='

Here is that code:

CREATE TABLE #TEMP2 (
    surrogate_user varchar(125),
    surr_user bit)

INSERT INTO #TEMP2

    SELECT
        surrogate_user =
        CASE
            WHEN surrogate_user IS NOT NULL THEN surr_user = 1
            ELSE surr_user = 0
        END

    FROM #TEMP1
    WHERE ((datepart(weekday, wl.access_date) IN (2,3,4,5,6)
            AND datepart(hour, wl.access_date) NOT BETWEEN '7' AND '17'
            OR datepart(weekday, wl.access_date) NOT IN (2,3,4,5,6)))

    SELECT *
    FROM #TEMP1 tmp1
    LEFT OUTER JOIN #TEMP2 tmp2
    ON tmp1.surrogate_user = tmp2.surrogate_user

ANY HELP OR DIRECTION WOULD BE APPRECIATED. THANK YOU!!!

SELECT is a single SQL statement, albeit a complex one. The ORDER BY clause is one of the (many) clauses which make up this single SELECT statement.

IF/ELSE is not SQL. It is part of whatever procedural language the database vendor provides to define stored procedures and/or query batches. This language provides a procedural syntax to control the execution of SQL statements.

Thus, you cannot use such statements 'in the middle' of the SQL statement of which your (attempted) ORDER BY clause is a part.

It is not possible to define a variable list of columns in the ORDER BY clause. You can fake it, though. Simply provide a constant value as a 'place holder' in those cases where no sort column is desired. Even though you are also having issues with the CASE statement, that statement is precisely the means to define the variable sort order you seek. Your ORDER BY clause should look something like:
CASE is an expression which returns a single value. I'm not sure what you are trying to do in your second issue, but your INSERT statement should probably look something like:
P.S. It looks like your 'surr_user' column violates first normal form, as it simply is a restatement of the "NULLness" of the 'surrogate_user' column.

Jeff Mason
Custom Apps, Inc.
www.custom-apps.com

What if I was to write the following statement
Select ColumnName From Tablename where ColumnName = True
What I'm trying to do is is the value is tru the to Insert it into the table if it's False then to Stop all action.
You said we can't use the The statement so what would we use if the value is true????
So right now this is what I have
Select ColumnName From Tablename where ColumnName = True
What would my next line be????
I'm useing VB.NET
Thank you

I'm sorry, I'm not sure I can parse your question.

Not all RDBMS's have a boolean data type (e.g. SQL Server), so testing for TRUE may not work.

I have no idea, as I can't tell what you are trying to accomplish. What are you trying to INSERT? Perhaps if you restate your question ...

Jeff Mason
Custom Apps, Inc.
www.custom-apps.com