Just a Bit - implicit conversions and the BIT datatype

Consider the following:

DECLARE @I int;
DECLARE @B bit;
DECLARE @False bit;
DECLARE @True bit;


SET @False = 'false';
SET @True = 'true';
SET @I = -5;

--this much we pretty much expect
select @False, @True

-- but what about this ?
WHILE @I < 5
BEGIN
SET @B = @I;
SELECT @I
, @b;
SET @I = @I + 1;
END

While the BIT datatype will throw errors

Jumbled Unreadable SSRS Report Details

In developing reports which have many columns, you may find that the detail lines become jumbled up and unreadable.  Note that the column headers are just fine though.

To save time and headache with your reports, always select your detail line(s) withing the IDE and be sure that the background color is not set to "clear".  In fact, giving them each a color is a good idea.  Note also that the default templates provided by MS always have a background color set.

I can't rationally explain why this matters, it's just one of those things to be written off as "magic".

Strip unwanted characters out of sql string or variant data

-- =============================================

-- Author:           blowers

-- Create date: 2012-05-11

-- Description:      function to strip out special (escape and other) characters from an input string

-- MODIFY @KEEP TO INCLUDE THE CHARACTERS YOU WISH TO KEEP

--     - in the example a-z, 1-9 and a period will be retained and all others removed

--     - go be the uber-dba and create a list of formats you might want to use (numeric only, alpha only, money formatting only, etc)

-- =============================================

CREATE Function [dbo].[strip_special](@Temp VarChar(1000))

Returns VarChar(1000)

AS

       Begin

              DECLARE @KEEP VARCHAR(50)

              SET @KEEP = '%[^a-z0-9.]%'

              While PatIndex(@KEEP, @Temp) > 0

                     Set @Temp = Stuff(@Temp, PatIndex(@KEEP, @Temp), 1, '')

              Return @TEmp

       End

GO

Adding critical logging to your procedures

Here is a little tip that will help produce detailed logging and debugging reference points:

Output the name of the stored procedure when it runs.  Simple.

Procedure names, much like anything else, can change over time.  So how do we keep up changes to those messy output strings?  You don't, Simple again.

Include this little jewel with your print output or logging output and see how easy it is to find your way back to the stored proc:
SELECT OBJECT_SCHEMA_NAME(@@PROCID) as [schema], OBJECT_NAME(@@PROCID) as [procedure]
Maybe really show off what a fancy coder you are by including it in your error handling:
BEGIN CATCH
     SELECT
     OBJECT_SCHEMA_NAME(@@PROCID) as [schema],
     OBJECT_NAME(@@PROCID) as [procedure],    
     ERROR_NUMBER() AS ErrorNumber,     
     ERROR_SEVERITY() AS ErrorSeverity,     
     ERROR_STATE() AS ErrorState,     
     ERROR_PROCEDURE() AS ErrorProcedure,     
     ERROR_LINE() AS ErrorLine,     
     ERROR_MESSAGE() AS ErrorMessage;
END CATCH;   

Nice article about running totals against aggregated row items in SSRS

http://mangalpardeshi.blogspot.com/2009/02/running-totals-in-sql.html

QUOTENAME : A little known util function for "bracketing" a value

DECLARE @TEXT VARCHAR(50)
DECLARE @NUMBER INT
DECLARE @QUOTE CHAR(1)

SET @QUOTE = '"'

SET @TEXT = 'hello'

SET @NUMBER = 13

--bracketed with default brackets

SELECT  QUOTENAME(@TEXT), len(@TEXT), len(quotename(@TEXT))

--bracketed with default brackets

--note that an implicit conversion occurs)

SELECT  QUOTENAME(@NUMBER)

--bracketed with a double quote

SELECT  QUOTENAME(@TEXT, @QUOTE)

Great SQL Brain Teaser.... how many records are in the table?

Create Table TBL1 (col1 int, col2 int)

Create Table TBL2 (col1 int, col2 int)

 

--query1

select count(col1) from TBL1 where col2 >= 5

--results = 3

 

--query2

select count(col1) from TBL1 where col2 < 5

--results = 2

 

-- can you tell how many records are in the table?

-- how/why?