CODEWITHSUNDEEP
sqlsql-serverwildcard
Akira
Akira

Reputation: 47

How to use wildcard symbol in SQL query that is assigned as parameter value using SET?

What is missing in following query?

It get an error

The data types varchar and varchar are incompatible in the modulo operator

My code:

DECLARE @SQLStatement AS VARCHAR(max)
DECLARE @GroupName VARCHAR(100) = 'abc xyz abc'

CREATE TABLE #a 
(
    col1 INT, 
    col2 VARCHAR(50), 
    col3 VARCHAR(100)
)

SET @SQLStatement = 'INSERT INTO #a
                        SELECT col1, col2, col3
                        FROM Group
                        WHERE GroupName like '+@GroupName+''%''' '

PRINT (@SQLStatement)
EXEC (@SQLStatement)

SELECT * FROM #a

Upvotes: 0

Views: 160

Answers (2)

Tahbaza
Tahbaza

Reputation: 9548

Your @SQLStatement should be as follows.

SET @SQLStatement = 'INSERT INTO #a SELECT col1, col2, col3 FROM Group WHERE GroupName LIKE '''+@GroupName+'%''';

However, you should consider not doing dynamic SQL for this for the entire statement. You could get away with just the @GroupName parameter being constructed.

Also, look into understanding SQL Injection Attacks and you may well rethink this approach entirely.

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1269873

Use parameters and sp_executesql:

SET @SQLStatement = '
                        INSERT INTO #a
                        SELECT col1, col2, col3
                        FROM Group
                        WHERE GroupName like @GroupName + ''%''
                    ';

exec sp_executesql @SQLStatement, N'@GroupName varchar(100)', @GroupName = @GroupName;

It seems unlikely that this does anything useful, Presumably, you intend:

WHERE ' ' + GroupName + ' ' LIKE '% ' + @GroupName + ' %'

Upvotes: 2

Related Questions