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 |
|
Grofit
Starting Member
11 Posts |
 Posted - 2009-06-14 : 07:00:24
|
| Hey,Im currently converting an older MySQL DB over to MSSQL, and im trying to make things a bit easier to update going forward... and im trying to create a view on a fairly complex set of data...The view in question contains an associated_type_id, which specifies what type of association the link should be, so 1 would be lets say a product link, 2 would be a user link or something... so basically it would be something like:CASE associated_type_idWHEN 1 THEN (SELECT name FROM products WHERE id = associated_id)WHEN 2 THEN (SELECT name FROM users WHERE is = association_id)END AS associated_nameNow that there is a very simple version of what happens, and there are quite a few statements, and a few tables use an association type method like this. So i was thinking rather than have this same code in about 4-5 views i could put this logic into a stored proc and pass in the type_id and id as vars then get that to do the lookup and return it out. However im not sure as to:A) If you can call a stored proc within a view as a columnB) If this would cause major slowdown having to call a stored proc for each rowAny help would be great, hopefully this wouldnt be called LOADS within the system just at certain points, as i know its not going to be the fastest of queries... |
|
|
GilaMonster
Master Smack Fu Yak Hacker
4507 Posts |
|
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
Posted - 2009-06-14 : 09:12:16
|
You can call a stored procedure from a view, but only with none or static parameters.However, why would you do this?If you persist in doing this, create a linked server to same machine as your sql server (aka self link) and then check out OPENROWSET function, which accepts a stored procedure as datasource.
E 12°55'05.63"N 56°04'39.26" |
 |
|
|
Grofit
Starting Member
11 Posts |
Posted - 2009-06-14 : 10:13:40
|
| i dont *have* to do it this way, its just i would end up having 4 views, which share about 90% of the same SQL, so i thought (coming from a programming background) that there would be some way for me to make that bit of SQL a bit modular so i could reuse it within the other views, also that way when i need to update that bit of sql (Which happens when people add new types) i only need to change it in one place... So its just something that i would have prefered to do if it was possible without any performance penalties, but if its going to bog it down and tricky to implement i will just put the SQL in by itself... |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2009-06-14 : 14:43:49
|
quote:
Originally posted by Grofit
i dont *have* to do it this way, its just i would end up having 4 views, which share about 90% of the same SQL, so i thought (coming from a programming background) that there would be some way for me to make that bit of SQL a bit modular so i could reuse it within the other views, also that way when i need to update that bit of sql (Which happens when people add new types) i only need to change it in one place... So its just something that i would have prefered to do if it was possible without any performance penalties, but if its going to bog it down and tricky to implement i will just put the SQL in by itself...
sounds like what you need is a user defined function where you put common repeated logic and call it everywhere you want it.see below for more detailshttp://www.sqlteam.com/article/user-defined-functions |
|
|
|
GilaMonster
Master Smack Fu Yak Hacker
4507 Posts |
Posted - 2009-06-14 : 16:10:16
|
| Try a user-defined function, but be sure to test the performance on large resultsets (at least as large as you expect to ever get in production)--Gail ShawSQL Server MVP |
|
|
|
|
|
|
|
|
Calling Stored Proc as column within view