Execute dynamic query inside procedure

Question

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.

• Oreo: Haya, if your @Messages variable is super-long, you might hit a truncation error because your @SQL variable is only an NVARCHAR(1000). Might want to use NVARCHAR(4000) or VARCHAR(8000) instead. Or follow one of the answers that doesn't even need dynamic T-SQL.

Answer 1

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.

Answer 2

First off your @ConcatenatedErrorMessages variable in your dynamic SQL statement isn't declared. So that will cause an error.... Anyways, if I were to replace @ConcatenatedErrorMessages with @Messages it will give me the error you are talking about. There is no scope for the @Messages variable at run time the way it's currently written which is why it's complaining it needs to be declared.

To get the data you want into the script do the following:
'+ @variablename +'

SET @SQL = 'SELECT parsename(replace(substring('+@Messages+',charindex('+quotename(':','''')+','+@Messages+') + 1,len('+@Messages+')),'+quotename(':','''')+','+quotename('.','''')+'),4)';

• Haya: Sorry, the ConcatenatedErrorMessages is a miss spelling. Thank you I think the problem solved. But, I'm new in the sql server and I'm not sure if I call the procedure in the right way because it gives syntax error. I call it like that: test.ZTEST2 'test1:test2:test3:test4:test5'