title: Incorrect syntax near '=' while trying to call a stored procedure in T-sql
body: <p>I am calling a stored procedure called <code>Searchprocedure</code>. I get an error at line where I am calling it. I have not made any changes to the parameters passed in the procedure and it was called just fine with same calling statement earlier.</p>
  
  <pre><code>Exec SearchProcedure @firstname = 'Simran', @middlename = 'kaur', @lastname = 'Khurana', @City = 'Delhi'
  </code></pre>
  
  <p>What's wrong with the syntax that is gives the error that says:</p>
  
  <blockquote>
    <p>Incorrect syntax near '=' </p>
  </blockquote>
  
  <p>Edit:
   Statement where I did :</p>
  
  <pre><code> set @sql = 'declare ' + '@Temp'+ @colVar + ' int'
      exec(@sql)
      select @sql as 'SQLFORDECLARATIONS 
  outputs 
  
  
  declare @TempMiddleName int
  </code></pre>
  
  <p>yet when I try to set value in the variable gives error that it should be declared first.</p>
  
  <p>The set statement results in :</p>
  
  <pre><code>select  @TempMiddleName=dbo.[MatchMiddleName](MiddleNameFromUser,MiddleNameFromTable,0)
  </code></pre>
  
  <p>which is what is should be yet it not able to see the declared variable</p>
  
  <p>The stored procedure is as follows:</p>
  
  <pre><code>create procedure SearchProcedure
  (
     @firstname nvarchar(20),
     @middlename nvarchar(20) = null,
     @lastname nvarchar(20),
     @DOB Date = null,
     @SSN nvarchar(30)= null, 
     @ZIP nvarchar(10)= null,
     @StateOfResidence nvarchar(2)= null,
     @City nvarchar(20)= null,
     @StreetName nvarchar(20)= null,
     @StreetType nvarchar(20)= null,
     @BuildingNumber int= null,
     @Aptnumber nvarchar(10)= null
  )
  As 
  DECLARE @sSQL NVARCHAR(2000), @Where NVARCHAR(1000) = ' '
  declare @Percent int,
  @FN nvarchar(20),
  @MN nvarchar(20) = null,
  @LN nvarchar(20),
  @DateOfB Date = null,
  @SSNumber nvarchar(30)= null, 
  @ZIPCode nvarchar(10)= null,
  @StateOfRes nvarchar(2)= null,
  @CityOfRes nvarchar(20)= null,
  @StreetNameRes nvarchar(20)= null,
  @StreetTypeRes nvarchar(20)= null,
  @BuildingNumberRes int= null,
  @AptnumberRes nvarchar(10)= null
  set @Percent = 0
  create table #results
  (
  firstname nvarchar(20) not null,
  middlename nvarchar(20),
  lastname nvarchar(20)not null,
  PercentageMatch int not null,
  DOB Date,
  SSN nvarchar(30), 
  ZIP nvarchar(10),
  [State] nvarchar(2),
  City nvarchar(20),
  StreetName nvarchar(20),
  StreetType nvarchar(20),
  BuildingNumber int,
  Aptnumber nvarchar(10)
   )
  
  declare  c Cursor local static Read_only 
   for 
  SELECT * from dbo.Patients where firstname = @firstname
  
  
  open c
  
  fetch next from c into @FN,
  @MN,
  @LN,
  @DateOfB,
  @SSNumber, 
  @ZIPCode,
  @StateOfRes,
  @CityOfRes,
  @StreetNameRes,
  @StreetTypeRes,
  @BuildingNumberRes,
  @AptnumberRes
  
  while @@FETCH_STATUS = 0 BEGIN
  
  /*set @Percent = dbo.[MatchLastName](@lastname, @LN, @Percent)
  set @Percent = dbo.[MatchMiddleName](@middlename, @MN, @Percent)
  set @Percent = dbo.[MatchCity](@City, @CityOfRes, @Percent)*/
  Exec [dbo].[OutputProcedure]  @lastname,  @LN, @middlename, @MN,@City, @CityOfRes, @Percent output
  Insert into #results values 
  (@FN,@MN,@LN,@Percent, @DateOfB,@SSNumber, @ZIPCode,@StateOfRes,@CityOfRes,@StreetNameRes,@StreetTypeRes,@BuildingNumberRes,@AptnumberRes)
  fetch next from c into @FN,
  @MN,
  @LN,
  @DateOfB,
  @SSNumber, 
  @ZIPCode,
  @StateOfRes,
  @CityOfRes,
  @StreetNameRes,
  @StreetTypeRes,
  @BuildingNumberRes,
  @AptnumberRes
  end
  
  select * from #results order by PercentageMatch desc
  IF OBJECT_ID('tempdb..#results') IS NOT NULL DROP TABLE #results
  go
  </code></pre>
  
  <p><code>OutputProcedure</code> code is as follows:</p>
  
  <pre><code>CREATE Procedure OutputProcedure
  ( 
   @LastNameFromUser nvarchar(20) = null, 
   @LastNameFromTable nvarchar(20), 
   @MiddleNameFromUser nvarchar(20) = null, 
   @MiddleNameFromTable nvarchar(20) = null, 
   @CityFromUser nvarchar(20) = null,
    @CityFromTable nvarchar(20) = null,
  @Percentage int out
    )
  AS
  BEGIN
  select 'OUTPUTPROCEDURECALLED'
  declare @maxvalue int
  DECLARE @variableTable TABLE (
      idx int identity(1,1),
      matchvalue nvarchar(15))
  
      INSERT INTO @variableTable(matchvalue) values ('MiddleName')
      INSERT INTO @variableTable(matchvalue) values ('LastName')
      INSERT INTO @variableTable(matchvalue) values ('City')
  
      SELECT * FROM @variableTable 
  DECLARE @counter int
  declare @sql nvarchar(100)
  declare @sql2 nvarchar(25), @finalResult nvarchar(100)
  declare @sql3 nvarchar(300), @sql4 nvarchar(15),  @tempresultStore nvarchar(20), @temp int, @temp2 int, @average int
  
  
  SET @counter = 1
  SELECT @maxvalue = (SELECT MAX(idx) FROM @variableTable)
  
  select @maxvalue as 'MAXVALUE'
  WHILE(@counter &lt;= @maxvalue)
  BEGIN
      DECLARE @colVar nvarchar(15)
  
      SELECT @colVar = matchvalue FROM @variableTable WHERE idx = @counter
      set @sql = 'declare ' + '@Temp'+ @colVar + ' int'
      exec(@sql)
      select @sql as 'SQLFORDECLARATIONS'
  
      /*set @temp = CHARINDEX(' ',@sql)
      select @temp as 'resultofcharindex'
      set @temp2 = LEN(@sql) - (@temp)
      SELECT @temp2 AS 'AFTERADDING1'
      set @tempresultStore = right(@sql, @temp2)*/
      set @tempresultStore = 'Temp'+@colVar
      SELECT @tempresultStore AS 'FINALCUTPART'
  
  set @sql3 = 'set ' + ' ' + @tempresultStore + '=' + 'dbo.[Match' + @colVar + '](' + @colVar + 'FromUser' + ',' + @colVar + 'FromTable' + ',' + '0)' 
  EXEC(@sql3)  
  select @sql3 as 'check sql query formed'
  
  set @finalResult = @finalResult + @tempresultStore
  select @finalResult as 'SUM'
      SET @counter = @counter + 1
      select @counter as 'COUNTERVALUE'
      END
      set @Percentage = @finalResult/@maxvalue
      SELECT @Percentage AS 'FINALRESULT'
  RETURN 
  END
  </code></pre>