Passing special character [char(11), char(7)] in stored procedure as string

Question

I want to search in table for all records with a special characters - char(11), char(7) etc.

I have found one stored procedure which helps me to find it. But it is not accepting the input parameters as follows:

EXEC sp_FindStringInTable '%'+char(7)+'%', 'CPOA-TALENTLink-Test-Leeds', 'TALENT_Contact_ChangeLog'

Error:

Msg 102, Level 15, State 1, Line 1
Incorrect syntax near '+'.

Stored procedure:

CREATE PROCEDURE dbo.sp_FindStringInTable 
     @stringToFind NVARCHAR(100), 
     @schema SYSNAME, 
     @table SYSNAME
AS
BEGIN TRY
    DECLARE @sqlCommand VARCHAR(MAX) = 'SELECT * FROM [' + @schema + '].[' + @table + '] WHERE ' 

    SELECT @sqlCommand = @sqlCommand + '[' + COLUMN_NAME + '] LIKE ''' + @stringToFind + ''' OR '
    FROM INFORMATION_SCHEMA.COLUMNS 
    WHERE TABLE_SCHEMA = @schema
      AND TABLE_NAME = @table 
      AND DATA_TYPE IN ('char','nchar','ntext','nvarchar','text','varchar')

    SET @sqlCommand = LEFT(@sqlCommand, LEN(@sqlCommand) - 3)

    EXEC (@sqlCommand)
   PRINT @sqlCommand
END TRY
BEGIN CATCH 
   PRINT 'There was an error. Check to make sure object exists.'
   PRINT error_message()
END CATCH 

As mentioned in error, I'm unable to search through table for special characters.

Answer 1

In calls to procedures expressions other than literals or variables don't work.

Assign the concatenation to a variable and pass that variable to the procedure.

DECLARE @p varchar(max) = '%' + char(7) + '%';

EXEC sp_FindStringInTable @p, 'CPOA-TALENTLink-Test-Leeds', 'TALENT_Contact_ChangeLog';