T-sql – Execute dynamic query inside procedure

dynamic-sqlt-sql

I'm trying to build a procedure as follows:

CREATE PROCEDURE [test].[ZTEST1] 
@Messages NVARCHAR(4000)

AS
SET NOCOUNT ON;

BEGIN
  Declare @SQL nvarchar(1000);
  SET @SQL = 'SELECT parsename(replace(substring(@ConcatenatedErrorMessages,charindex('+quotename(':','''')+',@ConcatenatedErrorMessages) + 1,len(@ConcatenatedErrorMessages)),'+quotename(':','''')+','+quotename('.','''')+'),4)';

EXECUTE SP_EXECUTESQL @SQL;

END
GO

I call the procedure like this: 

test.ZTEST2 'test1:test2:test3:test4:test5';

but I get this error:

Must declare the scalar variable "@Messages".

I'm not sure what the problem is.

Best Answer

You are making this needlessly complicated. You are trying to build a dynamic query where a static one would do just fine. Assuming the logic in the dynamic SQL is correct, the entire procedure could look like this:

CREATE PROCEDURE test.ZTEST1
  @Messages nvarchar(4000)
AS
BEGIN
  SET NOCOUNT ON;

  SELECT
    PARSENAME(REPLACE(SUBSTRING(@Messages, CHARINDEX(':', @Messages) + 1, LEN(@Messages)), ':', '.'), 4);
  /* or you could split the complex expression into multiple lines,
     for potentially easier understanding of the logic:
    PARSENAME(
      REPLACE(
        SUBSTRING(
          @Messages,
          CHARINDEX(':', @Messages) + 1,
          LEN(@Messages)
        ),
        ':',
        '.'
      ),
      4
    );
  */
END
GO

I have replaced @ConcatenatedErrorMessages with @Messages as that seemed an obvious mistake (and may have been the cause of an error message very similar to the one you have posted).

On a different note, the way you are calling the stored procedure:

test.ZTEST2 'test1:test2:test3:test4:test5';

is acceptable only if that statement is first in the batch. The more universal way of calling stored procedures that would work regardless of where the statement was located would be using the EXEC/EXECUTE keyword:

EXECUTE test.ZTEST2 'test1:test2:test3:test4:test5';

Finally, the name of the procedure you are trying to execute, test.ZTEST2, does not exactly match the name of the one being created above (test.ZTEST1) – so, you need to fix that too.

Related Question