Feeds:
Posts
Comments

Posts Tagged ‘Proof’

You can passs temp table inside a dynamic sql and it will process that for you


declare @s varchar(200)

if object_id('tempdb..#t') is not null 
drop table #t

Select 1 as n
into #t

select * from #t -- first time 

set @s = 
	'update #t 
	set n = 2

	insert into #t
	values (3)
	'

exec (@s)

select * from #t -- seccond time

 
Advertisements

Read Full Post »


--Proof of nulls and selects
----------------------------------

declare @a table 
(col int)
insert into @a
select 1
union
select null
union 
select 3 

select count(*) from @a -- counts nulls
select count('') from @a -- counts nulls
select count(1) from @a -- counts nulls

select count(col) SpecificColumn  from @a	-- only counts NON null 

--Proof of Sum and Null
-----------------------

create table #a 
(
	b int
)

insert into #a values (1)
insert into #a values (null)
insert into #a values (1)

select sum(b) from #a

Read Full Post »

-- Proof that avg function does not take null into calculations
--17 Nov 2010
DECLARE @test TABLE (NAME VARCHAR(12) NULL, value INT NULL)

INSERT @test
VALUES ('a', 1)

INSERT @test
VALUES ('b', 2)

INSERT @test
VALUES ('a', NULL)

INSERT @test
VALUES ('b', NULL)

SELECT NAME, value
FROM @test

SELECT NAME, avg(value)
FROM @test
GROUP BY NAME


Read Full Post »

-- Nulls cannot be counted whether by itself or even if its inside a calculation. Example below:

declare @c int
set @c = 0

declare @t table (a varchar(5), b int)

while (@c != 2)
begin
-- value will only be inserted in column 'b' so that in 'a' NUll is stored
INSERT into @t ( b )
select @c
set @c= @c + 1
end
select * from @t

select count(a) CountingNullsOnly , count(b) CountingNonNulls, count(a - b) CountingNullsInACalculation from @t

-- Here is a samll snippet to show if you keep forgetting which bit stand for which flag

declare @test bit

set @test = 'false'
select @test

set @test = 'true'
select @test

Read Full Post »