title: SQL not valid identifier error
body: <p>I have a function which executes a select statement. When I attempt to execute this select statement which returns a single value and sets it to a variable my function crashes with the following error:</p>
  
  <blockquote>
    <p>Msg 203, Level 16, State 2, Procedure Info_GetWholeNumber, Line 20<br>
    The name 'SELECT MAX(LEN(CAST(FLOOR([pcom_audit_cant_con]) AS
    VARCHAR(38)))) AS WHOLE_NO FROM Auditorias.prod_com_audit' is not a
    valid identifier.</p>
  </blockquote>
  
  <p>If I copy and paste that select statement it works fine... therefore it's not the select it has something to do with my EXEC... anyways here's my functions code...</p>
  
  <pre><code>ALTER FUNCTION [dbo].[Info_GetWholeNumber]
  (
      @TABLE VARCHAR(MAX)
      , @COLUMN VARCHAR(MAX)
  )
  RETURNS INT
  AS
  BEGIN
      -- Declare the return variable here
      DECLARE @WHOLE_NO INT
  
      -- Add the T-SQL statements to compute the return value here
      DECLARE @SQL VARCHAR(MAX)
      SET @SQL = 'SELECT MAX(LEN(CAST(FLOOR([' + @COLUMN + ']) AS VARCHAR(38))))
        AS WHOLE_NO FROM ' + @TABLE
      EXEC @WHOLE_NO = @SQL
  
      -- Return the result of the function
      RETURN @WHOLE_NO
  
  END
  </code></pre>
  
  <p>If anyone has any ideas how I can fix this I'd really appreciate the help! Thanks in advance!</p>
  
  <p><strong>UPDATES:</strong></p>
  
  <p>OK so im attempting to use the sp_executesql function as stated and i am going to paste the new function.</p>
  
  <pre><code>ALTER FUNCTION [dbo].[Info_GetWholeNumber]
  (
  @TABLE VARCHAR(MAX)
  , @COLUMN VARCHAR(MAX)
  )
  RETURNS INT
  AS
  BEGIN
  DECLARE @WHOLE_NO INT
  DECLARE @SQL NVARCHAR(MAX)
  DECLARE @PARAMS NVARCHAR(MAX)
  
  SET @SQL = N'SELECT @WHOLE_NOOUT = MAX(LEN(CAST(FLOOR(@COL) AS VARCHAR(38)))) FROM @TBL'
  SET @PARAMS = N'@COL VARCHAR(MAX), @TBL VARCHAR(MAX), @WHOLE_NOOUT INT OUTPUT'
  
  EXECUTE sp_executesql @SQL, @PARAMS, @COL = @COLUMN, @TBL = @TABLE, @WHOLE_NOOUT = @WHOLE_NO OUTPUT;
  
  -- Return the result of the function
  RETURN @WHOLE_NO
  
  END
  </code></pre>
  
  <p>I am now getting this error:</p>
  
  <p><strong>Msg 1087, Level 16, State 1, Line 1
  Must declare the table variable "@TBL"</strong></p>
  
  <p>Which in my opinion seems correct since the variables arent declared other than in the @PARAM variable... Am I missing something here?</p>