If Object_ID() Drop Procedure fails to generate

Sean_Lively

SCA fails to generate IF OBJECT_ID('X') IS NOT NULL DROP PROCEDURE X if it's unable to parse the stored procedure.

I have a couple of stored procedures in an existing databases that contain, let's say gratuitous parentheses, and SCA didn't generate the drop procedure code. If I attempt to format the sproc with SQL Prompt, it generates an error- "Incorrect syntax near )."

The sproc has a Where clause like this:
WHERE ((IIF([XYZ].[XYZCodeID]=3,[UserCode], IIF([XYZ].[ZZZCodeID]=2,'ProjectUsers', IIF([XYZ].[ZZZCodeID]=4,'PPCI',IIF([UserCode]='Z0001','ZZZ','YYY')))))=@Parameter1)

It's choking on the extra parentheses around ((IIF...)=@Parameter1

Why would SCA not generate If Object(X) Drop Procedure X if it can't parse the sproc?

Tianjiao_Li

@SeanL

If SCA can't parse the stored procedure, it doesn't know that it's a thing that it needs to drop / consider / take care of. You'd have to fix the stored procedure before retry it in SCA.