SQL Reporting Services

Using a SELECT statement with a searched CASE expression

SELECT ProductNumber, Name, 'Price Range' =
CASE
WHEN ListPrice = 0 THEN 'Mfg item - not for resale'
WHEN ListPrice <50 THEN 'Under $50'
WHEN ListPrice >= 50 and ListPrice <250 THEN 'Under $250'
WHEN ListPrice >= 250 and ListPrice < 1000 THEN 'Under $1000'
ELSE 'Over $1000'
END
FROM Production.Product
ORDER BY ProductNumber ;

Use UNION ALL rather than UNION wherever necessary. This will speed up the query exceution of the report

SELECT ROWID=IDENTITY(int,1,1) , EMPID, FNAME, LNAME
INTO EMPLOYEE2 FROM EMPLOYEE ORDER BY EMPID

CREATE FUNCTION [dbo].[ParseNumbers]
(
@string VARCHAR(50)
)
RETURNS VARCHAR(50)
AS
BEGIN
DECLARE @IncorrectCharLoc SMALLINT
SET @IncorrectCharLoc = PATINDEX('%[^0-9]%', @string)
WHILE @IncorrectCharLoc > 0
BEGIN
SET @string = STUFF(@string, @IncorrectCharLoc, 1, '')
SET @IncorrectCharLoc = PATINDEX('%[^0-9]%', @string)
END
SET @string = @string
RETURN @string
END

create function ProperCase(@Text as varchar(8000))
returns varchar(8000)
as
begin
declare @Reset bit;
declare @Ret varchar(8000);
declare @i int;
declare @c char(1);

select @Reset = 1, @i=1, @Ret = '';

while (@i < = len(@Text))
select @c= substring(@Text,@i,1),
@Ret = @Ret + case when @Reset=1 then UPPER(@c) else LOWER(@c) end,
@Reset = case when @c like '[a-zA-Z]' then 0 else 1 end,
@i = @i +1
return @Ret
end