title: In stored procedure, need to access the column value in the variable where Tablename is taken as a variable too
body: <pre><code>CREATE PROCEDURE dbo.usp_testing
  
  AS
  BEGIN
  DECLARE @STATE INT,@TBLNAME sysname,@TrID VARCHAR(50),@FAMILYID VARCHAR(50),@SQL varchar(8000)
  
  DECLARE cur CURSOR FOR
  SELECT TrID,TableName 
  FROM LogTable
  WHERE Prefix='vid'
  AND YEAR(DateTime)=YEAR(GETDATE())AND MONTH(DateTime)=MONTH(GETDATE())AND  DAY(DateTime)=DAY(GETDATE())
  
  OPEN cur
  
  FETCH NEXT FROM cur
  INTO @TrID,@TBLNAME
  
  IF(@TBLNAME='Student' OR @TBLNAME='Family' OR @TBLNAME='College')
  BEGIN
  **select @SQL='SELECT MemberID FROM'+' '+@TBLNAME+' '+'where TrId='+@TrID
  select @FAMILYID=EXEC(@SQL)**
  </code></pre>
  
  <p>In this line, its gving me an error. </p>
  
  <pre><code>select @SQL='select @ID=(SELECT MemberID FROM'+' '+@TBLNAME+' '+'where TrId='+@TrID +')'
  </code></pre>
  
  <p>as well as in this too</p>
  
  <pre><code>exec(@sql) 
  
  END
  
  END
  GO
  </code></pre>
  
  <p>I need to put the MemberID in the variable @ID</p>
  
  <p>Hi, I have done few changes as explained below</p>
  
  <pre><code>ALTER PROCEDURE [dbo].[USP_TESTSP]
  
  AS
  BEGIN
  DECLARE @SQL nvarchar(max),@TABLENAME sysname,@id nvarchar(max)
      SELECT @TABLENAME='student'
      select @SQL= 'select @id=MemberID FROM'+' '+@TABLENAME+' '+'where TrId=11091'
      exec sp_executesql @SQL,'@id nvarchar(max)output',@id=@id output
      --select exec (@SQL)
  
  END
  </code></pre>
  
  <p>but getting an error as:</p>
  
  <blockquote>
    <p>Procedure expects parameter '@parameters' of type 'ntext/nchar/nvarchar'.</p>
  </blockquote>