Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
m3jim
Starting Member
1 Post |
 Posted - 2006-10-12 : 14:18:05
|
| 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 as flags to populate a string variable.In pseudo code, it looks something like this:q=""select * from tbl_Users where UserID=123if isStaff then q=q + "..."if isManager then q=q + "..."if isAdmin then q=q + "..."return qI'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 |
|
|
khtan
In (Som, Ni, Yak)
17689 Posts |
Posted - 2006-10-12 : 14:35:19
|
[code]declare @q varchar(8000)select @q = case when isStaff = 1 then '...' when isManager = 1 then '...' when isAdmin = 1 then '...' endfrom tbl_Userswhere UserID = 123[/code]
KH |
 |
|
|
Page47
Master Smack Fu Yak Hacker
2878 Posts |
Posted - 2006-10-12 : 15:19:48
|
| I don't see any constraint in the DDL (see that's a joke, because you didn't provide any DDL or complete business rules) that says a User can't be both Staff and Manager at the same time. Based on your q=q+"..." psuedocode, I assume they can and you want to append multiple times when multiple roles are involved.There are probably 10 ways to write this but simply put:select @q = @q + case isStaff when 1 then '...' else '' end + case isManager when 1 then '...' else '' end + case isAdmin when 1 then '...' else '' endfrom tbl_Userswhere UserID = @UserIDJay White |
|
|
|
|
|
|
|
|
Stored Procedure and playing with fields