Select * from stored Procedure | SQL Server Performance Forums

SQL Server Performance Forum – Threads Archive

Select * from stored Procedure

Can it be done? I thought so… In other words if you have a stored procedure stoProc1 with NOCount set to ON that returns a table dataset for exa. 3 rows 4 columns, what is the syntax to sue this as a table? This works:
Select * from ( select * from t1 ) a Why won’t this?
Select * from ( exec stoProc1 ) a
or this?
Select * from ( stoProc1 ) a Thx
Because from (xxxx), xxxx is or other select or table or variable or dynamic, but no sp.
Luis Martin
Moderator
SQL-Server-Performance.com Although nature commences with reason and ends in experience it is necessary for us to do the opposite, that is to commence with experience and from this to proceed to investigate the reason.
Leonardo Da Vinci Nunca esperes el reconocimiento de tus hijos, eso ocurrirá luego de tu muerte
All postings are provided “AS IS” with no warranties for accuracy.
Create table #temp(col1,….) — same structure as that of sp Insert into #temp EXEC stoProc1 Select * from #temp Madhivanan Failing to plan is Planning to fail
That syntax is simply not supported. You have to use Madhivanan’s workaround. —
Frank Kalis
Microsoft SQL Server MVP
http://www.insidesql.de
Heute schon gebloggt?http://www.insidesql.de/blogs
Ich unterstuetze PASS Deutschland e.V. http://www.sqlpass.de)
Ok seems like it’s not possible to use an sp as a table even thought it returns a table dataset…OH well it sounded reasonable at least to me <img src=’/community/emoticons/emotion-5.gif’ alt=’;-)’ /><br /><br />I’ll get around it but it won’t be as clean.<br /><br />Temp tables it is I guess…<br /><br />Thanks for the prompt response.
Or create a UDF that returns a table. You can use most of the syntax that you can use in a sproc, though not all. If you need to use the same data more than once, remember that a UDF is not a recordset object that holds data. In SQL 2005 you will have the Common Table Expression, which roughly corresponds to a recordset object – in SQL 7 or 2000, you’ll need to use a (temp) table to hold the data.
>>In SQL 2005 you will have the Common Table Expression, which roughly corresponds to a recordset object Does it mean this type of query is possible?
Select t1.col1, t2.col2 from
mytable t1 inner join (exec mySP) t2
on t1.keycol=t2.keycol
Madhivanan Failing to plan is Planning to fail
No, that’s still not supported. —
Frank Kalis
Microsoft SQL Server MVP
http://www.insidesql.de
Heute schon gebloggt?http://www.insidesql.de/blogs
Ich unterstuetze PASS Deutschland e.V. http://www.sqlpass.de)
In SQL 2005, a CTE is like a cursor – you define the SELECT statement for the rowset, and you can then use the declared named of the CTE in subsequent query statements as if it was a table. To be honest, it doesn’t add up to much more than a syntax shortcut to creating a temp table. But it does save you the hassle to declare the temp table.
It’s quite good for recursive queries. [<img src=’/community/emoticons/emotion-1.gif’ alt=’:)‘ />]<br /><br />–<br />Frank Kalis<br />Microsoft SQL Server MVP<br /<a target="_blank" href=http://www.insidesql.de>http://www.insidesql.de</a><br />Heute schon gebloggt?<a target="_blank" href=http://www.insidesql.de/blogs>http://www.insidesql.de/blogs</a><br />Ich unterstuetze PASS Deutschland e.V. <a target="_blank" href=http://www.sqlpass.de>http://www.sqlpass.de</a>)
Well. I dont use SQL Server 2005. If you give an example query, that would be easy to understand [<img src=’/community/emoticons/emotion-1.gif’ alt=’:)‘ />]<br /><br />Madhivanan<br /><br />Failing to plan is Planning to fail
Frank, Do you mean that you can pass the CTE object as a procedure parameter within regular T-SQL?
Here are some from a PASS presentation. I guess they are taken from the 2005 BOL<br /><pre id="code"><font face="courier" size="2" id="code"><br /># 25<br />WITH s_cte<br />AS<br />(<br />SELECT <br />p.ContactID<br />,(p.FirstName + ‘, ‘ + p.LastName + ‘ (‘ + cast(p.ContactID AS varchar(10)) +’)’) AS p_n<br />FROM Person.Contact AS p<br />)<br />SELECT <br />s.ContactID<br />,s.p_n<br />FROMs_cte AS s<br />;<br /><br /><br /><br /><br />—————————————————————————<br />USE AdventureWorks;<br />GO<br />WITH Sales_CTE (SalesPersonID, NumberOfOrders, MaxDate)<br />AS<br />(<br /> SELECT SalesPersonID, COUNT(*), MAX(OrderDate)<br /> FROM Sales.SalesOrderHeader<br /> GROUP BY SalesPersonID<br />)<br />SELECT E.EmployeeID, OS.NumberOfOrders, OS.MaxDate,<br /> E.ManagerID, OM.NumberOfOrders, OM.MaxDate<br />FROM HumanResources.Employee AS E<br /> JOIN Sales_CTE AS OS<br /> ON E.EmployeeID = OS.SalesPersonID<br /> LEFT OUTER JOIN Sales_CTE AS OM<br /> ON E.ManagerID = OM.SalesPersonID<br />ORDER BY E.EmployeeID;<br />GO<br /><br /><br /><br /><br /><br /># 26<br />USE AdventureWorks;<br />GO<br />WITH DirectReports(ManagerID, EmployeeID, EmployeeLevel) AS <br />(<br /> SELECT ManagerID, EmployeeID, 0 AS EmployeeLevel<br /> FROM HumanResources.Employee<br /> WHERE ManagerID IS NULL<br /> UNION ALL<br /> SELECT e.ManagerID, e.EmployeeID, EmployeeLevel + 1<br /> FROM HumanResources.Employee e<br /> INNER JOIN DirectReports d<br /> ON e.ManagerID = d.EmployeeID <br />)<br />SELECT ManagerID, EmployeeID, EmployeeLevel <br />FROM DirectReports <br />– OPTION (MAXRECURSION 2); <br />;<br />GO<br /><br />——————————————————————————————————————–<br />USE AdventureWorks;<br />GO<br />WITH DirectReports(Name, Title, EmployeeID, EmployeeLevel, Sort)<br />AS (SELECT CONVERT(varchar(255), c.FirstName + ‘ ‘ + c.LastName),<br /> e.Title,<br /> e.EmployeeID,<br /> 1,<br /> CONVERT(varchar(255), c.FirstName + ‘ ‘ + c.LastName)<br /> FROM HumanResources.Employee AS e<br /> JOIN Person.Contact AS c ON e.ContactID = c.ContactID <br /> WHERE e.ManagerID IS NULL<br /> UNION ALL<br /> SELECT CONVERT(varchar(255), REPLICATE (‘- ‘ , EmployeeLevel) +<br /> c.FirstName + ‘ ‘ + c.LastName),<br /> e.Title,<br /> e.EmployeeID,<br /> EmployeeLevel + 1,<br /> CONVERT (varchar(255), RTRIM(Sort) + ‘- ‘ + FirstName + ‘ ‘ + LastName)<br /> FROM HumanResources.Employee as e<br /> JOIN Person.Contact AS c ON e.ContactID = c.ContactID<br /> JOIN DirectReports AS d ON e.ManagerID = d.EmployeeID<br /> )<br />SELECT EmployeeID, Name, Title, EmployeeLevel<br />FROM DirectReports <br />ORDER BY Sort;<br />GO<br /><br /><br /><br /><br /># 27<br />–Create Employees table and insert values<br />CREATE TABLE Employees<br />(<br /> empid int NOT NULL,<br /> mgrid int NULL,<br /> empname varchar(25) NOT NULL,<br /> salary money NOT NULL,<br /> CONSTRAINT PK_Employees PRIMARY KEY(empid),<br />)<br />GO<br />INSERT INTO Employees VALUES(1 , NULL, ‘Nancy’ , $10000.00)<br />INSERT INTO Employees VALUES(2 , 1 , ‘Andrew’ , $5000.00)<br />INSERT INTO Employees VALUES(3 , 1 , ‘Janet’ , $5000.00)<br />INSERT INTO Employees VALUES(4 , 1 , ‘Margaret’, $5000.00) <br />INSERT INTO Employees VALUES(5 , 2 , ‘Steven’ , $2500.00)<br />INSERT INTO Employees VALUES(6 , 2 , ‘Michael’ , $2500.00)<br />INSERT INTO Employees VALUES(7 , 3 , ‘Robert’ , $2500.00)<br />INSERT INTO Employees VALUES(8 , 3 , ‘Laura’ , $2500.00)<br />INSERT INTO Employees VALUES(9 , 3 , ‘Ann’ , $2500.00)<br />INSERT INTO Employees VALUES(10, 4 , ‘Ina’ , $2500.00)<br />INSERT INTO Employees VALUES(11, 7 , ‘David’ , $2000.00)<br />INSERT INTO Employees VALUES(12, 7 , ‘Ron’ , $2000.00)<br />INSERT INTO Employees VALUES(13, 7 , ‘Dan’ , $2000.00)<br />INSERT INTO Employees VALUES(14, 11 , ‘James’ , $1500.00)<br />GO<br />–Create Departments table and insert values<br />CREATE TABLE Departments<br />(<br /> deptid INT NOT NULL PRIMARY KEY,<br /> deptname VARCHAR(25) NOT NULL,<br /> deptmgrid INT NULL REFERENCES Employees<br />)<br />GO<br />INSERT INTO Departments VALUES(1, ‘HR’, 2)<br />INSERT INTO Departments VALUES(2, ‘Marketing’, 7)<br />INSERT INTO Departments VALUES(3, ‘Finance’, <img src=’/community/emoticons/emotion-11.gif’ alt=’8)’ /><br />INSERT INTO Departments VALUES(4, ‘R&D’, 9)<br />INSERT INTO Departments VALUES(5, ‘Training’, 4)<br />INSERT INTO Departments VALUES(6, ‘Gardening’, NULL)<br />———————————————————————————–<br />SELECT *<br />FROM Departments INNER JOIN<br /> Employees ON Departments.deptmgrid = Employees.empid<br />Go<br /><br />———————————————————————————–<br />CREATE FUNCTION dbo.fn_getsubtree(@empid AS INT) RETURNS @TREE TABLE<br />(<br /> empid INT NOT NULL,<br /> empname VARCHAR(25) NOT NULL,<br /> mgrid INT NULL,<br /> lvl INT NOT NULL<br />)<br />AS<br />BEGIN<br /> WITH Employees_Subtree(empid, empname, mgrid, lvl)<br /> AS<br /> ( <br /> — Anchor Member (AM)<br /> SELECT empid, empname, mgrid, 0<br /> FROM employees<br /> WHERE empid = @empid<br /><br /> UNION all<br /> <br /> — Recursive Member (RM)<br /> SELECT e.empid, e.empname, e.mgrid, es.lvl+1<br /> FROM employees AS e<br /> JOIN employees_subtree AS es<br /> ON e.mgrid = es.empid<br /> )<br /> INSERT INTO @TREE<br /> SELECT * FROM Employees_Subtree<br /><br /> RETURN<br />END<br />GO<br />———————————————————————————–<br />SELECT *<br />FROM Departments AS D<br /> CROSS APPLY fn_getsubtree(D.deptmgrid) AS ST<br /> <br />SELECT *<br />FROM Departments AS D<br /> OUTER APPLY fn_getsubtree(D.deptmgrid) AS ST<br />———————————————————————————–<br /></font id="code"></pre id="code"><br /><br />–<br />Frank Kalis<br />Microsoft SQL Server MVP<br /<a target="_blank" href=http://www.insidesql.de>http://www.insidesql.de</a><br />Heute schon gebloggt?<a target="_blank" href=http://www.insidesql.de/blogs>http://www.insidesql.de/blogs</a><br />Ich unterstuetze PASS Deutschland e.V. <a target="_blank" href=http://www.sqlpass.de>http://www.sqlpass.de</a>)
I’m intrigued! Really like the APPLY variation on joins – just not sure that I understand the difference with the old way of calling UDFs.
Have you ever tried to pass a column of the SELECT list to a UDF that returns a table in one statement?

Frank Kalis
Microsoft SQL Server MVP
http://www.insidesql.de
Heute schon gebloggt?http://www.insidesql.de/blogs
Ich unterstuetze PASS Deutschland e.V. http://www.sqlpass.de)
Frankly, no I haven’t![<img src=’/community/emoticons/emotion-2.gif’ alt=’:D‘ />]
Well, then you have saved yourself a lot of frustration. With APPLY() now you can. [<img src=’/community/emoticons/emotion-1.gif’ alt=’:)‘ />]<br /><br />–<br />Frank Kalis<br />Microsoft SQL Server MVP<br /<a target="_blank" href=http://www.insidesql.de>http://www.insidesql.de</a><br />Heute schon gebloggt?<a target="_blank" href=http://www.insidesql.de/blogs>http://www.insidesql.de/blogs</a><br />Ich unterstuetze PASS Deutschland e.V. <a target="_blank" href=http://www.sqlpass.de>http://www.sqlpass.de</a>)
Thanks for the code [<img src=’/community/emoticons/emotion-1.gif’ alt=’:)‘ />]<br /><br />Madhivanan<br /><br />Failing to plan is Planning to fail
Here’s a great article on (among other things) using a CTE to perform recursive queries… http://www.sqlservercentral.com/columnists/fBROUARD/recursivequeriesinsql1999andsqlserver2005.asp
Ryan Randall
www.monsoonmalabar.com London-based IT consultancy Solutions are easy. Understanding the problem, now, that’s the hard part.
Ryan, Good to see you here [<img src=’/community/emoticons/emotion-1.gif’ alt=’:)‘ />]<br /><br />Madhivanan<br /><br />Failing to plan is Planning to fail
]]>