Hi, hope I am asking this question in the right area...
I Currently have this line of code:
'Gen_Anaest=CaseWhen ('9251429') IN([PROC1],[PROC2],[PROC3],[PROC4])then'Yes'else'No'end
It works fine although I need to add a wildcard to lookup value, for example:
'Gen_Anaest=CaseWhen ('9251%') IN([PROC1],[PROC2],[PROC3],[PROC4])then'Yes'else'No'end
of course it does not work.
I know I can do a like statement e.g. [Proc1] like('9251%') or [PROC2] LIKE ('9251%')..... etc
Although, I have 30 PROC columns and 50 DX Columns that I need to interigated for various codes and the SQL becomes very long.
Try to move this to TSQL Group, I am bit not clear on your full requirement but assuming you are searching for sp having text so try this
-- Step 1 : SELECT ROUTINE_NAME, ROUTINE_DEFINITION FROM INFORMATION_SCHEMA.ROUTINES WHERE ROUTINE_DEFINITION LIKE '%9251429%' AND ROUTINE_TYPE='PROCEDURE' -- Step 2 : SELECT OBJECT_NAME(id) FROM SYSCOMMENTS WHERE [text] LIKE '%9251429%' AND OBJECTPROPERTY(id, 'IsProcedure') = 1 GROUP BY OBJECT_NAME(id)