Presumably the OP's question was to find rows where all columns have the same value???

CROSS APPLY VALUES is definitely the way to go, but your CASE statement made me scratch...

There are a number of special case odds where the fraction used is not the simplest (e.g. 6/4 and 100/30)

In order to cope with these you will need a database...

Performance may be poor on larger datasets, because the solution is a Triangular Join or "Hidden RBAR". For more information, see the link in SQLRNNR/Jason's signature!

If you data isn't big...

Your logic is incorrect, the OP's original request was to pad out the numbers to 7 places, not add 3 zeroes on the front.

That means that the optimizer estimated the cost of the function as 99% of the total cost of the query, which is not quite the same thing as it actually...

Never thought about bit fields and constants before.

If I do the code above and the bit field is non-indexed, I get an index scan with the predicate:

CONVERT_IMPLICIT(tinyint, [SomeTable].[SomeBITField],0)=(1)

so the bit...

what you've posted is not a valid SELECT statement, comparisons within a WHERE clause or connected by AND or OR - not commas.

As far as doing nothing if it does...

..and half that time is just writing the output to the screen.

Setting "discard results after execution" results in an execution time of 420ms.

I've created an XML document as per your sample above, and used copy and paste to extend it to 80,000 entries. On my machine, your function parses it and...

Your outer query has the WHERE clause:

where PostedDate='1/19/2011'

and PostedDate is defined in the view as:

CAST(CAST(SUBSTRING(WH_Status.StatusHistory,CHARINDEX('Batch posted successfully',CONVERT(VARCHAR(MAX),WH_Status.StatusHistory))-35,26) AS DATE) AS DATETIME) AS PostedDate

You will always get a scan on the...

you can still create some dynamic SQL and exec it, but you need to set the value of the variable within the dynamic SQL as I showed you above.

you've not set the variable "@Percentage2012" to anything in the dynamic SQL.

the OUTER select should be something like:

SELECT @Percentage2012 = STR(([2012] / ISNULL(NULLIF([2011],0),1) - 1),8,5) as [2012_Growth] ...

Cast the float as a decimal datatype first.

e.g.

create table #xml_test (id int identity primary key, int_val int, decimal_val float);

set nocount on;

insert into #xml_test (int_val, decimal_val)

values (10,5.55),(15,6.75);

select id

,int_val

,decimal_val

, cast(decimal_val as decimal(12,2))...

if the result is just a scalar value (or a few scalar values) you can pass values back to a calling proc by declaring parameters as OUTPUT (note that the...

... and if there are procedures from more than one category in an encounter, how to determine which category to include the encounter in? Is there a priority to...