June 27, 2008 at 1:09 pm
Hi All
I got stuck building a stored procedure that I'm using a on a web site. I essentially have two tables. One has all the data that i want to return. The second is a table that my users can store what information they would like to get out of this table. For example table1 has UserId, FirstName, LastName, DOB then the second table has the same number of columns with a BIT datatype; true if they want the to see the data, false if they don't.
How do i write my query that if UserId in table 2 is true then select UserId from table1 etc.. through all the columns I am allowing them to query on?
Here is how i would I would need to write it but i know it's not quite right:
DECLARE @userid AS BIT
DECLARE @firstname AS BIT
DECALRE @lastname AS BIT
SELECT @userid = UserId, @firstname = FirstName, @lastname = LastName
FROM Table2
SELECT IF(@userId == true) UserId, IF(@firstname == true) FirstName, IF(@lastname == true) LastName
FROM Table 1
If there's not a real clean way of doing this I know i can always handle it on the web page but I'd prefer to do on the database just so i'm not having to pass a whole bunch of information unnecessarily.
Thanks a bunch!
--Mike
June 27, 2008 at 1:41 pm
You are not going to be able to change the number of columns returned based on conditional logic like this, but you could easily NULL out the values or change them to empty strings.
[font="Courier New"]SELECT
T1.UserId
, CASE WHEN T2.FirstName = 1 THEN T1.FirstName ELSE NULL END AS FirstName
, CASE WHEN T2.LastName = 1 THEN T1.LastName ELSE NULL END AS LastName
, CASE WHEN T2.DOB = 1 THEN T1.DOB ELSE NULL END AS DOB
FROM
Table1 T1
INNER JOIN Table2 T2 ON T1.UserId = T2.UserId[/font]
June 27, 2008 at 2:54 pm
Thanks for the help. I think that's probably the course of action I'm going to have to take.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply