title: SQL working as query, but failing when calling as a stored procedure
body: <p>I have this SQL, that is working:</p>
  
  <pre><code>WITH MyTable AS
  (
      SELECT RegistrationID, ID,
      (ROW_NUMBER() OVER ( ORDER BY RegistrationID )) AS 'RowNumber'
      FROM tblData_Visit
              WHERE RegistrationID = @baseid
  ) 
  SELECT ' ' + CAST(RowNumber AS nvarchar) AS 'Row#'     
  FROM MyTable 
  WHERE RegistrationID = @baseid AND ID = @objectid
  </code></pre>
  
  <p>When I create a stored procedure,</p>
  
  <pre><code>CREATE PROC [dbo].[upMeta_VisitIndex]
  (
  @baseid int,
  @objectid int
  )
  AS
  WITH MyTable AS
  (
      SELECT RegistrationID, ID,
          (ROW_NUMBER() OVER ( ORDER BY RegistrationID )) AS 'RowNumber'
      FROM tblData_Visit
      WHERE RegistrationID = @baseid
  ) 
  SELECT ' ' + CAST(RowNumber AS nvarchar) AS 'Row#'     
  FROM MyTable 
  WHERE RegistrationID = @baseid AND ID = @objectid
  
  
  GO
  </code></pre>
  
  <p>, and call it using:</p>
  
  <pre><code>EXEC upMeta_VisitIndex @baseid @objectid
  </code></pre>
  
  <p>I end up in errorville:</p>
  
  <pre><code>SQL exception data:
  SQL error number: 102. SQL state:1. Error class: 15. Message: "Incorrect 
  syntax near '@objectid'.". Procedure:"".
  System.Data.SqlClient.SqlException (0x80131904): Incorrect syntax near 
  '@objectid'.
  at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, 
  Boolean breakConnection, Action`1 wrapCloseInAction)...............
  </code></pre>
  
  <p>Given, I know @baseid and @objectid are not null, I think the issue is around how I am calling the SP. Is there anything obvious here that I am missing?</p>
  
  <p>Cheers,
  Gav.</p>