How to use nested CASE statemenet?

Hi all,

I have a Sproc. to get records and I was looking to use a nested CASE statement

The thing is that first I want to check the availability of single quote in my where statement and replace it with double quote. The second case is to check for a given condition and make some calculation. The following statement is not running. So would you mind helping me in this regard?

Thank you in advance:

(CASE WHEN CHARINDEX(''ca.caCode'',''' + replace(@.WhereStmt, char(39), char(39) + char(39)) + ''') > 0 THEN ca.ClusterAmount ELSE dbo.vwGrantsMaster.CurrentValueTotalCost END CASE WHEN dbo.vwGrantsMaster.StatusCode IN (3) THEN (IsNull(dbo.vwGrantsMaster.CurrentValueTotalCost, 0))-(IsNull(dbo.vwGrantsMaster.AwardedTotalCostAmount,0)) ELSE dbo.vwGrantsMaster.CurrentValueTotalCost END) AS CurrentValueTotalCost,

I'm confused about what you are attempting to do here. Is this building a command string? I don't see a need to 'nest' CASE statements. You may have multiple actions that need to happen separately.

To replace a single quote with two single quotes (I don't think that you really mean 'double quote'.) use the following:

Code Snippet

DECLARE @.MyString varchar(50)
SET @.MyString = 'This isn''t a test.'

SELECT replace( @.MyString, '''', '''''' )

To put a single quote in a string, which is enclosed in single quotes, you have to 'escape' the single quote. That is done by doubling it up. So to check for a single quote in a string, you look for '''' (4 single quotes, the doubled up one, inside a pair enclosing a string.) And then you just replace that single quote (doubled up) with two single quotes (doubled up), inside a pair eclosing a string. It's mind boggling at first, but really not that difficult to understand.

Why bother to first check to see if a single quote exists, just do the REPLACE(), and if there are single quotes, they are doubled up, and if not, nothing happens.

Besides that, in order to help you with this 'mess', you are need to provide the entire command string building process. I can't make any sense of this...