| Author |
Topic |
|
ipisors
Starting Member
39 Posts |
 Posted - 2015-04-22 : 20:16:24
|
| I have a procedure that utilizes both temp tables, variables, and a cursor. Without getting into a long thing about why I shouldn't be using a cursor (etc), I was wondering if someone can let me know what my options are? What I essentially want is a view-like item to result from this procedure, so I can select from it in other places. I've created a useful procedure that ends with a single Select statement. I want to be able to select from this procedure in other places......How? |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2015-04-22 : 20:24:57
|
| Store the result set into a temporary table: INSERT INTO #t EXEC ...You'll need to do the CREATE TABLE #t for it first.To answer your question in the subject of the post though, you can't have variables/temp tables/etc in a view. You can use a function though, but I wouldn't recommend it for performance reasons. I avoid user-defined functions like the plague.Tara KizerSQL Server MVP since 2007http://weblogs.sqlteam.com/tarad/ |
 |
|
|
ipisors
Starting Member
39 Posts |
Posted - 2015-04-22 : 20:31:36
|
| I may not have stated my situation as well as I should have. I do in fact store the results in a table, (and the last line of my code is, Select * from Table). What I mean though is, I want to somehow USE this code in a packageable way to select from at will. In this specific case, I wanted to be able to pass it around to other colleagues - i..e, "here is a useful view for such-and-such a purpose". I also wanted to be able to select from it in OTHER code that's inside an existing SSIS package.Since I can't create a view with it, because it uses temp tables variables etc., and you don't recommend a function....is my only other option (for the SSIS situation) to create it as a stored procedure, use another SSIS task to execute it, and then be able to select from the table that the proc populates? Guess I was just really hoping for a view-like artifact from all of this. As for performance you can see I have already lowered my standards a bit by using a cursor...But at least I recognize it, and if I had been granted more time to finish this project, I would be diligently trying to find other ways to do it. So technically a table valued function is one way to be able to incorporate all of this code into an 'artifact' that I can then select from? |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2015-04-22 : 23:38:03
|
| Why can't your colleagues and the package run the stored procedure to get the data?Tara KizerSQL Server MVP since 2007http://weblogs.sqlteam.com/tarad/ |
|
|
|
ipisors
Starting Member
39 Posts |
Posted - 2015-04-23 : 14:56:47
|
| What is comes down to is a problematic table that has to do with claims which in some cases show multiple records which are identical except for some positive or negative numbers, for a given claim number (key, basically). My procedure gets the "final action" claims, that is, the ones where either the positives outweigh the negatives (take one of the positives as the final action claim). Or, there is only one record and it is positive. The thing is that there are already a number of reports and processes which select various things from the underlying, problematic table....the one that has all the dups. So for example, we have processes (including a long procedure in the ssis package) that selects from the "problematic" table. I thought it might be convenient, after I finished the code for the corrected data set (based on that problematic table), to simply change the FROM clause, where appropriate, to select FROM my new table. Since it has the exact same datatypes, etc. I guess instead, they would need to be sure to execute this stored procedure first in order to get the final table populated, and then select from the table that gets populated. Since I can't make this a view, nor can they select from a stored proc. Does that at least make more sense what I was trying? |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2015-04-23 : 15:03:14
|
| CREATE TABLE #t...INSERT INTO #t...EXEC YourStoredProc...SELECT * FROM #tVoilaTara KizerSQL Server MVP since 2007http://weblogs.sqlteam.com/tarad/ |
|
|
|
ipisors
Starting Member
39 Posts |
Posted - 2015-04-24 : 11:35:23
|
| Thank you - Yes I am going to just add that type of code into the ssis package. I won't be able to give colleagues a single name of a new artifact they can select from, but at least they can execute those simple lines and then select from it. Fair enough thanks again! |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
ScottPletcher
Aged Yak Warrior
550 Posts |
Posted - 2015-04-27 : 13:54:54
|
| Another possibility would be to create a table-valued function rather than a stored proc. Then they could select from that table-based function just like it was view or any other table. |
|
|
|
ipisors
Starting Member
39 Posts |
Posted - 2015-04-28 : 00:09:42
|
| Thank you Scott. That is also what I understood on Tara's first reply, but then subsequent there was the mention of avoid using functions so I wasn't sure.I think I should at least add table-valued functions to my toolbelt, though, (if cautiously), so thanks for this reminder.. I think I am going to try it, it may be helpful for some colleagues. |
|
|
|
ScottPletcher
Aged Yak Warrior
550 Posts |
Posted - 2015-04-28 : 03:26:22
|
| In-line table-valued functions are great performance-wise. But, yes, you should avoid multi-statement tvfs. |
|
|
|
ipisors
Starting Member
39 Posts |
Posted - 2015-04-28 : 10:32:49
|
| OK thanks - off I go to do some study & practice. |
|
|
|
ipisors
Starting Member
39 Posts |
Posted - 2015-04-28 : 14:05:52
|
| OK just following up. But it seems like I COULDN'T create an inline table valued function, because one of the rules is, nothing but a select statement inside the RETURN clause (if I could have done this with nothing but a select statement, I would have created a view already basically).Since mine needs to execute a stored procedure and then do a select statement, it seems like the only table function I'd be capable of creating is a multi statement one anyway. Just checking my understanding of the difference between the 2 in this context. |
|
|
|
ScottPletcher
Aged Yak Warrior
550 Posts |
Posted - 2015-04-29 : 10:10:42
|
| If you can post the code, I'd be willing to help you try to convert it into a single SELECT statement. Using CROSS APPLY and other techniques, you can often do everything you need to do in one SELECT. |
|
|
|
|
using variables and temp tables inside view?
Tara Kizer