CODEWITHSUNDEEP
sqlsql-serverdatabaset-sqlstored-procedures
Prabhavith
Prabhavith

Reputation: 486

Convert Dynamic SQL to Static SQL

Can any one help me from solving this Dynamic query to a Static one 

DECLARE @REPCD VARCHAR(10)
SET @REPCD='CA%'

IF (CHARINDEX('*',@REPCD,1)>0 OR  CHARINDEX('%',@REPCD,1)>0) 
BEGIN
    SET @WHERE = ' AND REP_CD like ''' + REPLACE(@REPCD, '''', '') + ''''
END
ELSE
BEGIN
    SET @WHERE = ' AND REP_CD = ''' + REPLACE(@REPCD, '''', '') + ''''
END

I would like to convert the above query to something like this

SELECT * FROM REP
WHERE CASE WHEN CHARINDEX('%',@REPCD,1)>0 THEN REP_CD like 'CA%' END AS REP_CD

Upvotes: 0

Views: 1674

Answers (3)

Joe G Joseph
Joe G Joseph

Reputation: 24046

try this

DECLARE @REPCD VARCHAR(10)
SET @REPCD='CA'
set @REPCD=REPLACE(@REPCD,'*','%')
SELECT * FROM REP where REP_CD like @REPCD

Upvotes: 0

podiluska
podiluska

Reputation: 51494

select * 
from Rep
where Rep_CD like replace(@repcd, '*','%')

Upvotes: 0

Aaron Bertrand
Aaron Bertrand

Reputation: 280351

Why won't this work?

...
WHERE REP_CD LIKE REPLACE(@REPCD, '*', '%');

In the case where @REPCD contains no wildcards, LIKE works just like =. So there is no reason for a CASE or any other variation of conditional logic at all.

There currently is no way to make LIKE/= interchangeable without dynamic SQL. Well, I guess there is this convoluted way (assuming REP_CD can't be NULL), but I don't have any clue why this is better than what I posted above.

SET @REPCD = REPLACE(@REPCD, '*', '%');

...
WHERE REP_CD LIKE CASE WHEN CHARINDEX('%', @REPCD) > 0 THEN @REPCD ELSE REP_CD END
AND REP_CD = CASE WHEN CHARINDEX('%', @REPCD) > 0 THEN REP_CD ELSE @REPCD END

Upvotes: 2

Related Questions