CREATE PROCEDURE [dbo].[fa_users_inout]
AS
DECLARE @sortfield int
DECLARE @sorttype varchar(4)
SELECT
a.LastName
, a.FirstName
, a.UserName
, a.Password
, a.ID
, a.ActiveStatus
, a.ClientDisplayable
, b.Location as location
, c.userlevel as newuserlevel
FROM
abPartnerUsers a INNER
JOIN abPartnerLocation b ON a.LocationID = b.ID
JOIN faPermissions c ON a.PermissionsID = c.ID
ORDER BY
CASE
WHEN @sortfield = 1 THEN LastName @sorttype, FirstName
WHEN @sortfield = 2 THEN FirstName @sorttype, LastName
WHEN @sortfield = 3 THEN username @sorttype, LastName,
FirstName
WHEN @sortfield = 4 THEN password @sorttype, LastName,
FirstName
WHEN @sortfield = 5 THEN location @sorttype, LastName,
FirstName
WHEN @sortfield = 6 THEN newuserlevel @sorttype, LastName,
FirstName
WHEN @sortfield = 7 THEN ActiveStatus @sorttype, LastName,
FirstName
ELSE LastName desc
END
GO
> -----Original Message-----
> From: Kelly Matthews [SMTP:[EMAIL PROTECTED]
> Sent: Friday, September 10, 2004 3:31 PM
> To: SQL
> Subject: SP IF help
>
> Why wont something like this work? is there a way to do something like
> this in a stored procedure?
>
> CREATE PROCEDURE [dbo].[fa_users_inout]
> AS
> DECLARE @sortfield int
> DECLARE @sorttype varchar(4)
> SELECT
> a.LastName
> , a.FirstName
> , a.UserName
> , a.Password
> , a.ID
> , a.ActiveStatus
> , a.ClientDisplayable
> , b.Location as location
> , c.userlevel as newuserlevel
> FROM
> abPartnerUsers a INNER
> JOIN abPartnerLocation b ON a.LocationID = b.ID
> JOIN faPermissions c ON a.PermissionsID = c.ID
> ORDER BY
> IF @sortfield = 1
> BEGIN
> LastName @sorttype, FirstName
> END
> ELSE IF @sortfield = 2
> BEGIN
> FirstName @sorttype, LastName
> END
> ELSE IF @sortfield = 3
> BEGIN
> username @sorttype, LastName, FirstName
> END
> ELSE IF @sortfield = 4
> BEGIN
> password @sorttype, LastName, FirstName
> END
> ELSE IF @sortfield = 5
> BEGIN
> location @sorttype, LastName, FirstName
> END
> ELSE IF @sortfield = 6
> BEGIN
> newuserlevel @sorttype, LastName,
> FirstName
> END
> ELSE IF @sortfield = 7
> BEGIN
> ActiveStatus @sorttype, LastName,
> FirstName
> END
> ELSE
> BEGIN
> LastName desc
> END
>
> GO
> _____
>
>
[Todays Threads] [This Message] [Subscription] [Fast Unsubscribe] [User Settings] [Donations and Support]
