November 2007 - Posts

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 

1 Create a temporary table whose structure is identical to the result of the procedure and query on      this   table

Create table #orders(Orderid int,..................)

Insert into #orders EXEC get_orders

Select* from #orders where orderdate>='19960101' and orderdate<'19970101'

2 Use OPENROWSET

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

Now you can easily filter the resultset

Select 
employeeid,orderid,orderdate 

from

OPENROWSET('SQLOLEDB','Data Source=Server_name;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

Posted by Madhivanan with 8 comment(s)

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

 

Posted by Madhivanan with 3 comment(s)

Should alias names be preceded by AS?

If you ask me, I would say Yes Smile

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 difficult 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

Posted by Madhivanan with 7 comment(s)
Filed under: ,

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=@number

return @i

end

select
i,dbo.multiply(i) as multiplied_value from

(

select distinct i from test

) as t

order by i

Posted by Madhivanan with 4 comment(s)
Filed under: ,

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.

Posted by Madhivanan with 6 comment(s)
Filed under: , ,

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 Smile)

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())}

Posted by Madhivanan with no comments