October 12, 2006 at 11:11 am
I'm new to Stored procedures, so please excuse me if this is a dumb question. I have searched, but haven't found anything.
I have a table like this:
UserID, isStaff, isManager, isAdmin ....
The 'isXXX' fields are booleans which I want to use to populate a menu structure.
In pseudo code, it looks something like this:
q=""
select * from tbl_Users where UserID=123
if isStaff then q=q + "..."
if isManager then q=q + "..."
if isAdmin then q=q + "..."
return q
I've written the basic stored procedure, accepting the UserID and returning a string, but I can't figure out how to play with the individual fields that a select statement returns. If seen cursor examples but they play with multiple records. I want to play with the fields when there is just one record.
Any ideas? Thanks - -Jim
October 12, 2006 at 12:43 pm
Well first things first. Can you change the design of the table so that you have a column TypeEmployeID?
That would normalize the design and make your job much easier.
October 12, 2006 at 12:44 pm
Try looking into a case statement within your select clause.
Tom
October 13, 2006 at 3:54 am
If the user is only ever one of staff, manager, admin etc , case statements or something like a single employee type id would be the best solutions. If the user can be a combination of things, then it gets a bit more complex.
I think the information you're after, about accessing field information, is something like this though:
declare @ismanager bit
declare @isStaff bit
etc
select @ismanager = isManager, @isStaff = isStaff
from tbl_Users
where UserId = 123
--have to use 'if @ismanager = 1' etc in SQL2K and earlier
if @ismanager = 'True' then q = etc
if @isStaff = 'True' then q = etc
etc
October 13, 2006 at 8:03 am
Assuming bit columns in SQL Server 2000, you could try either a stored procedure or a user-defined function, something along these lines:
CREATE PROCEDURE uspGetMenu
(
@userid int
, @q varchar(8000) OUTPUT
)
AS
SET @q = ''
SELECT @q = CASE isStaff
WHEN 1 THEN '...'
ELSE ''
END
+ CASE isManager
WHEN 1 THEN '...'
ELSE ''
END
+ CASE isAdmin
WHEN 1 THEN '...'
ELSE ''
END
FROM tbl_Users
WHERE UserID = @userid
GO
CREATE FUNCTION dbo.fGetMenu
(
@userid int
)
RETURNS varchar(8000)
AS
BEGIN
DECLARE varchar(8000)
SET @q = ''
SELECT @q = CASE isStaff
WHEN 1 THEN '...'
ELSE ''
END
+ CASE isManager
WHEN 1 THEN '...'
ELSE ''
END
+ CASE isAdmin
WHEN 1 THEN '...'
ELSE ''
END
FROM tbl_Users
WHERE UserID = @userid
RETURN @q
GO
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply