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 |
|
Vivaldi
Constraint Violating Yak Guru
298 Posts |
 Posted - 2005-04-15 : 10:36:34
|
| Happy US Tax day,I have a table of permissions (will call it opp)opp has 4 key columns (oppID [pk], Userid,GroupID,Fileid) all are bigints the record can have any combinationof user/group/file so nulls are allowed.I have sproc that returns a record based on the criteria I pass in.declare @objPermissionsID bigint ,@userid bigint ,@GroupID Bigint ,@FileID Bigintset @groupid = 3 -- (3 is a groupid marking a record in db) Select oppID , UserID,GroupID,FileID From opp WHERE oppid = Coalesce(@oppID,oppID) AND UserID = Coalesce(@userid,useriD) AND groupid = coalesce(@groupid,groupid) and Fileid = Coalesce(@FileID,FileID)If I comment out everything but groupID it returns the record but when I add any of the others, it returns no records?any ideas________________________________________________As the only Republican that likes the Green Party. I am a contradiction. |
|
|
DonAtWork
Master Smack Fu Yak Hacker
2167 Posts |
Posted - 2005-04-15 : 11:07:05
|
| If you are setting @GroupID, why bother passing it in.Perhaps you want WHERE (oppid = @oppid OR oppid is null)AND (UserID = @userid OR UserID is null)etc. |
 |
|
|
Vivaldi
Constraint Violating Yak Guru
298 Posts |
Posted - 2005-04-15 : 11:17:16
|
quote:
If you are setting @GroupID, why bother passing it in.
Setting it was for testing in QA.As my hangover wears off, I remembered that NULL cannot = NULL so I either would need to set a dummy default for the columns or do fileid=fileid OR fileid is null________________________________________________Bier is a privelege of the working man (or woman). |
|
|
|
DonAtWork
Master Smack Fu Yak Hacker
2167 Posts |
Posted - 2005-04-15 : 11:23:18
|
| Heh. Hangover + T-SQL = bad, BAD mojo.:) |
|
|
|
|
|
|
|
|
fairly basic select with coalesce