Madhivanan

Select columns from (EXEC procedure_name) - Is this possible?

Well.I see many users asking this question in forums. "I have a procedure that returns single resultset. How do I filter the result returned from the procedure? There are, at least, two ways to acheive this

Let us create this procedure

create procedure get_orders
as
select* from northwind..orders 

Select 
           *
from 
          OPENROWSET('SQLOLEDB','Data Source=test;Trusted_Connection=yes;
          Integrated Security=SSPI','Execute yourdb..get_orders')

Select 
           employeeid,orderid,orderdate 
from 
           OPENROWSET('SQLOLEDB','Data Source=test;Trusted_Connection=yes;
           Integrated Security=SSPI','Execute yourdb..get_orders')
where
           orderdate>='19960101' and orderdate<'19970101'

I prefer using method 2 as you dont need to create temp table and 
also you dont need to worry about the structure of the procedure

Random Password Generator

This is one of the methods to generate 8 characters password randomly (combination of alphabets,numbers and special characters)

declare @password varchar(8)
set @password=''

select @password=@password+char(n) from
(
    select top 8 number as n from master..spt_values 
    where type='p' and number between 48 and 122
    order by newid()
) as t

select @password


 

Should alias names be preceded by AS?

If you ask me, I would say Yes

When you write select statements and use alias names, always make sure alias names are preceded by the keyword AS. Oherwise you may get results which are unexpected and sometimes difficult to debug.

Consider the following example

use northwind

select employeeid,orderdate,shippeddate,shipcity from orders

Well. You see the correct resultset

Now run this

select employeeid,orderdate,shippeddate shipcity from orders

and see the resultset.Shippeddate's column name becomes shipcity because there is a missing comma after shippeddate that forces shipcity to be alias for shippeddate. If there are tens of columns and if you miss out a comma, it would become tedius to debug if the resultset is sent to the client application

Consider another example

select count(*) from orders

It returns the count as 830

Now what happens by mistake if you omit the keyword from

select count(*) orders

Now it returns count as 1 with alias name orders.

If SQL Server forces you to use AS before alias name, you would get error for the select statements that returns unexpected result in the above example. So I always ask developers to use AS before alias name which certainly makes the statements more readable and easy to debug

Also make sure you read this http://sqlblog.com/blogs/denis_gobo/archive/2007/11/13/3313.aspx

Simple Multiply Function

There is no direct way to multiply all the values of a column as we do summation using SUM(col). But it is possible with the following methods

create table test (i int, amount float)

insert into test(i,amount)
select 
         abs(cast(cast(newid() as varbinary(100)) as smallint)),
         abs(cast(cast(newid() as varbinary(100)) as smallint))/1000
from 
         sysobjects s1 cross join sysobjects s2 


Method 1 

select 
         i,exp(sum(log(cast(amount as float)))) AS multiplied_value
from 
         test
where 
         amount<>0
group by i
order by i


Method 2

use user defined function

create function dbo.multiply(@number int)
returns float as
begin
declare @i float
set @i=1.0
select @i=@i*amount from test where i
return @i
end


select i,dbo.multiply(i) as multiplied_value from
(
select distinct i from test
) as t
order by i

Easy way to generate number table

Often you may require number table for various purposes (parsing csv, string manipulation,etc)

If you use SQL Server 2005, then it is very easy than any other method you use in lower versions

Here are two methods of generating number table with 10000 numbers

(1) Use Row_number() funtion

Declare @numbers table(number int)

insert into @numbers(number)
select number from
(
select row_number() over (order by s1.name) as number 
from sysobjects s1 cross join sysobjects s2
) as numbers
where number between 1 and 10000

select number from @numbers


(2) Use CTE


Declare @numbers table(number int)

with numbers(number) as
(
select 1 as number
union all
select number+1 from numbers where number<10000
)
insert into @numbers(number)
select number from numbers option(maxrecursion 10000)
select number from @numbers

Different ways to count rows from a table

The most reliable and suggested method is to use count(*)

1 Select count(*) from table_name

But other than that you can also use one of the followings

2 select sum(1) from table_name
3 select count(1) from table_name
4 select rows from sysindexes where object_name(id)='table_name' and indid<2
5 exec sp_spaceused 'table_name'
6 DBCC CHECKTABLE('table_name')

PS

To update rows column of sysindexes table, you need to run this before executing methods 4 and 5

DBCC UPDATEUSAGE ('database_name','table_name') WITH COUNT_ROWS.

Undocumented DATE and TIME related functions

It is quite surprising to know the undocumented date and time related functions that work in both sql server 2000 and 2005

Run these and see(I think they are self-explanatory )

select {fn current_date()}
select {fn current_time()}
select {fn now()}
select {fn extract(hour from getdate())}
select {fn extract(minute from getdate())}
select {fn extract(second from getdate())}
select {fn extract(day from getdate())}
select {fn extract(month from getdate())}
select {fn extract(year from getdate())}
select {fn dayname(GetDate())}
select {fn monthname(GetDate())}
select {fn month(GetDate())}
select {fn year(GetDate())}