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 |
|
andrewcw
Posting Yak Master
133 Posts |
 Posted - 2009-09-08 : 18:35:27
|
I saw in ASP.NET forum the question I have been trying to resolve:Build Stored Proced from other stored procedures. In the case below both results have identical columns. exec dbo.storedProc1unionexec dbo.storedProc1The reply to this was :http://forums.asp.net/t/891536.aspx quote:
Assuming your tables are UNION compatible you can create a UNION ALL View which is like a stored proc being a query rewrite but just persisted. But as you were told earlier you cannot use UNION between store procs because they are compiled. [b]But it is implemented in ANSI SQL as CTE ( common table expressions ) virtual Views and they are coming in SQL Server 2005 using UNION ALL [/b]you can go 100 deep by default. The general rule to combine is try UNION ALL if UNION fails because UNION performs an implicit distinct by eliminating duplicate while UNION ALL leaves it. Hope this helps.
I tried creating a procedure that would call 2 procedure results I wanted to union. I used UNION ALL but that did not save. What is being suggested ? Does it work ? What other options are there to break queries into 'modular' component like pieces ?I am using VS2008. Is there a way to use stored procedures with UNION - and if so how ? I cant use CLR on the SQL server, not allowed.Thanks ! andrewcw |
|
|
khtan
In (Som, Ni, Yak)
17689 Posts |
Posted - 2009-09-08 : 22:13:04
|
use a temp table insteadinsert into #temp exec dbo.storedProc1insert into #temp exec dbo.storedProc2
KH[spoiler]Time is always against us[/spoiler] |
 |
|
|
|
|
|
modular use of stored procedures ?