CREATE PROCEDURE SeniorAnalystStep1_sp CREATE PROCEDURE spSalesAnalytics @CustomerID int, @intProductCategory AS int @Year int AS AS --SET @intProductCategory = 1 BEGIN SELECT Query Goes here BEGIN … SQL CODE goes here END END Example Turning a query into a stored procedure this is just for reference skip down to make your own stored procedure in your database. – – – USE [Featherman_Analytics]; This query is useful in that you can learn how to do a group by query, DECLARE @CustomerID int = 3 and you can see that using variables is helpful. You can change the value in the declare line. SELECT [CustomerID], COUNT([Sale_ID]) AS [Total #TA], SUM([Total_Sale]) as Now the query needs to be made more useful. If you turn it into a [Total Revenue] stored procedure that accepts different values for the customerID, then , FORMAT(AVG([Total_Sale]), 'N0') as [Customers Average Invoice Amount ] the query becomes useful for analytics. FROM [featherman].[sales] WHERE [CustomerID] = @CustomerID GROUP BY [CustomerID] USE [Featherman_Analytics]; The author of this document has read rights to the cited database so GO they can add the stored procedure and receive the well-loved following CREATE PROCEDURE spSalesAnalytics1Customer message. You can make a stored procedure in your own database. Command(s) completed successfully. @CustomerID int AS BEGIN SELECT [CustomerID], COUNT([Sale_ID]) AS [Total #TA] , SUM([Total_Sale]) as [Total Revenue] , FORMAT(AVG([Total_Sale]), 'N0') as [Customers Average Invoice Amount ]
CREATE PROCEDURE SeniorAnalystStep1_sp @intProductCategory AS int AS --SET @intProductCategory = 1 BEGIN SQL CODE goes here END CREATE PROCEDURE spSalesAnalytics @CustomerID int, @Year int AS BEGIN SELECT Query Goes here … END Example – Turning a query into a stored procedure – this is just for reference – skip down to make your own stored procedure in your database. USE [Featherman_Analytics]; DECLARE @CustomerID int = 3 SELECT [CustomerID], COUNT([Sale_ID]) AS [Total #TA], SUM([Total_Sale]) as [Total Revenue] , FORMAT(AVG([Total_Sale]), 'N0') as [Customers Average Invoice Amount ] FROM [featherman].[sales] WHERE [CustomerID] = @CustomerID GROUP BY [CustomerID] This query is useful in that you can learn how to do a group by query, and you can see that using variables is helpful. You can change the value in the declare line. Now the query needs to be made more useful. If you turn it into a stored procedure that accepts different values for the customerID, then the query becomes useful for analytics. USE [Featherman_Analytics]; GO CREATE PROCEDURE spSalesAnalytics1Customer @CustomerID int AS BEGIN SELECT [CustomerID], COUNT([Sale_ID]) AS [Total #TA] , SUM([Total_Sale]) as [Total Revenue] , FORMAT(AVG([Total_Sale]), 'N0') as [Customers Average Invoice Amount ] The author of this document has read rights to the cited database so they can add the stored procedure and receive the well-loved following message. You can make a stored procedure in your own database. Command(s) completed successfully.
Turning-your-Query-into-a-Stored-Procedure-final.docx