title: How to set dynamic query value to a variable in SQL Server stored procedure with parameters?
body: <p>I want to set a variable to the value generated using the dynamic query outside the query.</p>
  
  <p>I tried the <code>sp_executesql</code> concept, but this does not help me because I am using the parameter value in the dynamic query. </p>
  
  <p>Are there any possibilities to fix this issue?</p>
  
  <pre><code>CREATE PROCEDURE [dbo].[SP_test_proc] 
      @id int = null, 
      @deptId int = null
  As
  BEGIN
      DECLARE @Condition VARCHAR(MAX),@Query NVARCHAR(MAX)
  
      SET @Condition= 'Where Id = '@id + case when @deptid is null then '' else @deptid End
  
      SET @Query = 'Declare @Name varchar(100)
                    Set @Name = Select name from student '+ @Condition
  
      SELECT * 
      FROM personal 
      WHERE name = @Name
  END
  </code></pre>