SQL column true or false - use a view?
Simple question, I have an application and dependant on whether you are in the administration system or on the public website I want to show different results.
Example: in the database for a news story in the administration I may set the column value 'showonsite' to false. So I would like this to show in the administration panel only and not on the live site.
My question is, because I require the same information just with the only one column change, live site to only show true values and administration to show both. What is the most effective way of achieving this without copy paste of code?
Depends on where you write your code - if you use stored procedures, then just a parameter like @liveonly should be sufficient. Sameway if you use an inline sql query directly in a C# method, then a parameter live_only should be good enough, to determine whether the extra where condition will be added or not.
For eg. in the SP
SELECT * FROM news WHERE ((@live_only=true AND showonsite=true) or (@live_only=false))
This way it can be called with @live_only = false for admin panel, but with value true for the actual site.
Please see the second query.
I may be missing something. For non-adminstrative users, could you simply not SELECT news stories with the showonsite column set to false?
SELECT * FROM dbo.NewsStory WHERE showOnSite = 1
Oh! I see. You're talking about copying and pasting the query. It's late. >.<
If you have a stored procedure, you can pass a value to indicate whether the given user is an administrator:
SELECT * FROM dbo.NewsStory WHERE showOnSite = 1 OR @isAdmin = 1