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.

 All Forums
 SQL Server 2012 Forums
 Transact-SQL (2012)
 using variables and temp tables inside view?

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 Kizer
SQL Server MVP since 2007
http://weblogs.sqlteam.com/tarad/
Go to Top of Page

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?
Go to Top of Page

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 Kizer
SQL Server MVP since 2007
http://weblogs.sqlteam.com/tarad/
Go to Top of Page

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?
Go to Top of Page

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2015-04-23 : 15:03:14
CREATE TABLE #t...

INSERT INTO #t...
EXEC YourStoredProc...

SELECT * FROM #t

Voila

Tara Kizer
SQL Server MVP since 2007
http://weblogs.sqlteam.com/tarad/
Go to Top of Page

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!
Go to Top of Page

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2015-04-24 : 13:00:04


Tara Kizer
SQL Server MVP since 2007
http://weblogs.sqlteam.com/tarad/
Go to Top of Page

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.
Go to Top of Page

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.
Go to Top of Page

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.
Go to Top of Page

ipisors
Starting Member

39 Posts

Posted - 2015-04-28 : 10:32:49
OK thanks - off I go to do some study & practice.
Go to Top of Page

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.
Go to Top of Page

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.
Go to Top of Page
   

- Advertisement -