Search code examples
sql-serverstored-proceduresalter

Can't figure out Scalar Variable Error when Altering Procedure

I have an existing sql server stored procedure, that I'm trying to update. Every time I run Alter Procedure on it, I get the following error:

SQL Error: Must declare the scalar variable "@varOne".

I've Googled around but nothing that I can find helps out with how to get past this error... :/ Procedure below:

ALTER PROCEDURE [dbo].[sp_test] (
  -- Declare
  @varOne tinyint,
  @varTwo numeric(17,0)
) As

Set NoCount On
-- Select
Select  *
Into    #t
From    SQL_Test.dbo.trans with (nolock)
Where   test>=123
    And Case @varOne When 1 Then rNo  When 2 Then iNo  When 3 Then sNo End = @varTwo

Union
Select  *
From    SQL_Test.dbo.trsave with (nolock)
Where   date_time > DateAdd(dd, -60, GetDate())
    And Case @varOne When 1 Then rNo  When 2 Then iNo  When 3 Then sNo End = @varTwo

EDIT: I solved the problem by dropping the original procedure, and recreating it. Still curious why the alter would not work. Guessing it is caused by the client using SQL Server 2012.

Solution

  • I solved the problem by dropping the original procedure, and recreating it. Still curious why the alter would not work. Guessing it is caused by the client using SQL Server 2012.