title: Converting code containing views to stored procedure
body: <p>I've pieced together this code to create a pivot on a sum of invoice subtotals per a dealership. It works, and produces the correct results. However, when I try to convert it to a stored procedure the infamous squiggly red line appears under "CREATE VIEW Revenues" in SQL Server management studio and the error says:</p>
  
  <p>'CREATE VIEW must be the only statement in the batch' and I don't know enough T-SQL to proceed as I need to be able to execute it from a ASP.NET/C# web app.</p>
  
  <p>I'm trying create an SP by putting:</p>
  
  <p>1) </p>
  
  <pre><code>USE [Sherwood]
  GO
  
  SET ANSI_NULLS ON
  GO
  SET QUOTED_IDENTIFIER ON
  GO
  
  CREATE PROC [dbo].[usp_GetRevenues] 
  
  @StartDate AS DateTime = '20120301',
  @EndDate AS DateTime = '20120401'
  AS
  SET NOCOUNT ON;
  
  BEGIN
  </code></pre>
  
  <p>in front, with an END statement at the end. </p>
  
  <p>2) The parameters are necessary to be able to filter the data for a period of months, but I appear not to be able to pass parameters or use variables with the code as is.</p>
  
  <p>Thanks in advance.</p>
  
  <p>Code as is below - showing where I need to filter with variables. The code works only with dates in the format '1/1/2012' in the where clause, but need to be able to pass variables as shown.</p>
  
  <p>--================================</p>
  
  <pre><code>CREATE VIEW Revenues
  AS
  SELECT  
  i.DateOrdered
  ,LTRIM(STR(DATEPART(MONTH,i.DateOrdered))) AS [Month]  
  ,LTRIM(STR(YEAR(i.Dateordered))) AS [Year]  
  ,c.CustomerCode
  ,SUM(i.Jobprice) AS Subtotal 
  FROM Invoices i
  JOIN
  Customers c
  ON i.CustomerID = c.ID
  WHERE i.DateOrdered &gt;= @StartDate 
  AND i.DateOrdered &lt;= @EndDate **&lt;-- and here.**  
  GROUP BY ROLLUP (YEAR(i.DateOrdered), 
  MONTH(i.DateOrdered), i.DateOrdered, c.CustomerCode);
  GO
  
  IF OBJECT_ID('Revenues2') IS NOT NULL
  DROP VIEW Revenues2
  GO
  
  CREATE VIEW Revenues2
  AS
  SELECT 
   LTRIM(STR([YEAR])) + '-' + STUFF([Month],1,0, REPLICATE('0', 2 - LEN([Month]))) 
   AS [Date] 
  ,Subtotal
  ,CustomerCode
  FROM Revenues
  WHERE CustomerCode IS NOT NULL
  GO
  
  DECLARE @query VARCHAR(4000)  
  DECLARE @years VARCHAR(2000)  
  SELECT  @years = STUFF(( SELECT DISTINCT 
                      '],[' + [Date]  
                      FROM  Revenues2 
                      ORDER BY '],[' + [Date]  
                      FOR XML PATH('')  
                      ), 1, 2, '') + ']' 
  
  SET @query =  
  'SELECT * FROM  
  (  
    SELECT Subtotal,[Date],CustomerCode  
     FROM Revenues2  
  )t  
  PIVOT (SUM(Subtotal) FOR [Date] 
  IN ('+ @years +')) AS pvt' 
  
  EXECUTE (@query) 
  </code></pre>
  
  <p>--==========================================</p>
  
  <p>Regards</p>
  
  <p>Cliff</p>