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 |
|
nic
Posting Yak Master
209 Posts |
 Posted - 2002-11-12 : 12:59:44
|
| Hi I have a series of columns in a table. When they all contain a certain set of data (i.e. one column is set to true, another is false etc), then that record's status is "enabled". I have a number of stored procedures that need to check if the record is "enabled" before doing some process. Previously I have been checking each column's values (in each stored procedure) inorder to see if the record is "enabled". This is becoming necessary in more and more stored procedures, which is causing sustainabilty issues. Is a good use of a view?Instead of checking the values of each column each time, I was thinking I would create a view that determines if the record is enabled. That way I only have to join to the view in the sp to determine if the record is valid. In the long run this sounds like a MUCH BETTER solution. (Having to check independently in each sp etc can cause problems if I need to change something.)I just want to make sure this sounds like a good plan before proceding (I know I probably should have realized this in the beginning but you know how scope changes)ThanksNic |
|
|
M.E.
Aged Yak Warrior
539 Posts |
Posted - 2002-11-12 : 13:13:40
|
| Sounds like a decent use of a view to me. The only other option would be to throw all of that code to check for the enabled vs disabled into a user defined function and use it to checkieassume you have primary key in table of integercreate function Check_Enable (@keyfield int)returns bitas----Do your checking on the table here to ensure all fields are there--if all fields are fullreturn 1elsereturn 0-----------------------SQL isn't just a hobby, It's an addiction |
 |
|
|
nr
SQLTeam MVY
12543 Posts |
Posted - 2002-11-12 : 13:52:22
|
| could you do this with a computed field?==========================================Cursors are useful if you don't know sql.DTS can be used in a similar way.Beer is not cold and it isn't fizzy. |
|
|
|
nic
Posting Yak Master
209 Posts |
Posted - 2002-11-12 : 14:12:27
|
| Sorry I'm not sure what a computed field is (or how you create one). I looked in sql help and could only find a compute clause. My view returns true/false for the PK. Many times I need to return this value to the appliation. It seems to be working very well. Now if the parameters change for what makes InternetUpdate = True, I only have one place to change it.CREATE VIEW vw_multUserEnabled ASSELECT a.us_key ,CASE WHEN a.us_enabled = 1 AND b.mult_multClient= 0 AND b.mult_ship IN ('F','D') AND (b.mult_addDt < getDate() or b.mult_addDt is null) AND (b.mult_remDt > getDate() or b.mult_remDT is null) THEN 'True' ELSE 'False' END AS [InternetUpdate]FROM tblUser a INNER JOIN tblUserMult b ON a.us_key = b.us_key Nic |
|
|
|
|
|
|
good use of a view?