Perhaps one of these is the shorthand the OP is looking for?

Select *

from TEST

where ISNULL(Col1,'')+ISNULL(Col2,'')+ISNULL(Col3,'')+ISNULL(Col4,'') = ''

Select *

from TEST

where COALESCE(Col1,Col2,Col3,Col4) IS NULL

Could it be that the...

This might also work for you:

CREATE TABLE #abc (ID INT, val VARCHAR(10), other CHAR(4))

CREATE TABLE #load (ID INT, val VARCHAR(10))

INSERT #abc (ID, val)

SELECT 1 AS ID, 'ABC' as val UNION...

As I alluded to in my response above to Jeff, there is no reason to have either loop (see the INSERT at the end):

DECLARE @FRACAS TABLE

(ID varchar(50), Region varchar(100), Site...

I recently heard tell that passing data through table variables is a bit slow. Guess I'm finally going to have to setup a table variable and pass it to a...

You may want to try to see if this runs any faster:

Declare @Date as int

Set @Date = 3

Select * from TempTable where correct_date between DATEADD(day,-@date,GETDATE()) and GETDATE()

--

I know this is going to seem silly but I'm assuming 25 and 28 are not hardcoded but they come in as parameters that the OP has no control over...

I have some suggestions but they're pretty involved:

1. First you'll need a good split string function (http://www.sqlservercentral.com/articles/Tally+Table/72993/) like the one from Jeff Moden in the link.

2. Modify your stored procedure...

STUFF with PATINDEX is cool! This will also work:

SELECT REPLACE(LTRIM(REPLACE(Y, '0', '#')),'#', '0')

FROM (SELECT '123' UNION ALL SELECT '0123'

UNION ALL SELECT '000123' UNION ALL SELECT '0001230') AS X(Y);

Assuming...

Actually after ColdCoffee's prior post, I was no longer confused. I was...

Drew - Yes I am aware of hidden RBAR gotchas and if I can I usually try to remove them. In this case, I didn't see that way but...

f you don't know anything about the query or don't have any suggestions, its better you don't make a fool of yourself and keep everything to youself.

Did you even...

Complicated? You haven't seen complicated until you also introduce effective dates for the rules.

I actually support an application that has a similar look up approach to calculating a charge....

ColdCoffee:

So I think what you're saying (I am not an XML Plan-master, nor even a Paduan) is that COUNT(column_name) returns the number of rows that have non-NULL values? I...

So what happens with JayK's solution when there is a third rule?

I would do it like this:

DECLARE @r TABLE

(ID INT, [Min] MONEY, [MAX] MONEY, Fee MONEY)

INSERT INTO

You also should not need:

date_final_settlement is not null and

Assuming of course that your database is set to sort NULLs to before any non-NULL entry.