USE Master
--Look at the source of a system stored procedure 
sp_helptext [master.dbo.sp_who]

sp_who

sp_helptext [sp_helptext]

select * from syscomments

USE Northwind

--Look at the tables a stored procedure depends on
sp_depends custorderhist

--Execute existing procedure with one parameter in Northwind database
EXEC [northwind].[dbo].[custorderhist]@CustomerID = 'thecr'

--Execute existing procedure then print return value
DECLARE @RC INT
EXEC @RC = [Northwind].[dbo].[CustOrderHist] @CustomerID = 'thecr'
PRINT @RC

--Create new procedure with one parameter
DROP PROCEDURE dbo.CustOrderHistRep 
GO
CREATE PROCEDURE dbo.CustOrderHistRep 
@CustomerID char(5)
AS	--return a customers name and title
	SELECT ContactName, ContactTitle 
	FROM Customers WHERE CustomerID = @CustomerID
	--return products with total ordered by that customer
	SELECT ProductName, Total=SUM(Quantity)
	FROM Products P, [Order Details] OD, Orders O, Customers C
	WHERE 
	  C.CustomerID = @CustomerID AND C.CustomerID = O.CustomerID
	  AND O.OrderID = OD.OrderID AND OD.ProductID = P.ProductID
	GROUP BY ProductName
GO

--Exececute procedure supplying a Customer ID value to parameter
EXEC [northwind].[dbo].[custorderhistrep]@CustomerID = 'thecr'
GO

--Modify procedure -Whats the difference?
ALTER PROCEDURE dbo.CustOrderHistRep
@CustomerID char(5)
AS
	SELECT ContactName, ContactTitle
	FROM Customers WHERE CustomerID = @CustomerID

	SELECT ProductName, Total=SUM(Quantity)
	FROM (((Products P INNER JOIN [Order Details] OD
	  ON P.ProductID = OD.ProductID) JOIN Orders O
	  ON OD.OrderID = O.OrderID) JOIN Customers C
	  ON O.CustomerID = C.CustomerID)
	WHERE C.CustomerID = @CustomerID
	GROUP BY ProductName
	ORDER BY Total DESC
GO
--Exececute modified procedure
EXEC [northwind].[dbo].[custorderhistrep]@CustomerID = 'thecr'

sp_helptext custorderhistrep

--Delete procedure
DROP PROCEDURE dbo.custorderhistrep


SELECT OBJECTPROPERTY(object_id('[master].[dbo].[xp_msver]'), 'IsExtendedProc')