SQL Server Performance

Select * from stored Procedure

Discussion in 'General Developer Questions' started by stefanoale, Apr 20, 2006.

  1. stefanoale New Member

    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

  2. Luis Martin Moderator

    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.



  3. Madhivanan Moderator

    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
  4. FrankKalis Moderator

  5. stefanoale New Member

    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.
  6. Adriaan New Member

    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.
  7. Madhivanan Moderator

    >>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
  8. FrankKalis Moderator

  9. Adriaan New Member

    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.
  10. FrankKalis Moderator

    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>)
  11. Madhivanan Moderator

    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
  12. Adriaan New Member

    Frank,

    Do you mean that you can pass the CTE object as a procedure parameter within regular T-SQL?
  13. FrankKalis Moderator

    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>)
  14. Adriaan New Member

    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.
  15. FrankKalis Moderator

  16. Adriaan New Member

    Frankly, no I haven't![<img src='/community/emoticons/emotion-2.gif' alt=':D' />]
  17. FrankKalis Moderator

    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>)
  18. Madhivanan Moderator

    Thanks for the code [<img src='/community/emoticons/emotion-1.gif' alt=':)' />]<br /><br />Madhivanan<br /><br />Failing to plan is Planning to fail
  19. RyanRandall New Member

  20. Madhivanan Moderator

    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

Share This Page