title: SQL Stored procedure- Must declare the scalar variable "@user_name"
body: <p>I am getting the following error while I execute it.</p>
  
  <p>Msg 137, Level 15, State 2, Line 2
  Must declare the scalar variable "@user_name".</p>
  
  <p>(1 row(s) affected)</p>
  
  <pre><code>USE [qcsl]
  GO
  /****** Object:  StoredProcedure [dbo].[UserAdvancedSearch]    Script Date: 3/31/2017 12:09:22 PM ******/
  SET ANSI_NULLS ON
  GO
  SET QUOTED_IDENTIFIER ON
  GO
  ALTER PROCEDURE [dbo].[UserAdvancedSearch]
      -- Add the parameters for the stored procedure here
      (
      @user_name varchar(50) = NULL,
      @role_id int = 2,
      @user_status int = 1
  
  
  
      )
      AS
  BEGIN
      -- SET NOCOUNT ON added to prevent extra result sets from
      -- interfering with SELECT statements.
      SET NOCOUNT ON;
  
      -- Insert statements for procedure here
  DECLARE @query varchar(2000);
  SET @query = 'select * from user_main
  where (CONCAT(user_first_name,user_last_name) like  CONCAT(@user_name) OR CONCAT(CONCAT(user_first_name,user_last_name) IS NULL)
  AND
  (user_role_id_ref=@role_id OR user_role_id_ref IS NULL)
  AND
  (user_status = @user_status OR user_status IS NULL)';
  EXEC(@query)
  END
  </code></pre>