Stored procedure in a SELECT statement

Objective: Execute a stored procedure inside a SELECT statement and return the result as a table. I found a blog that shows how to execute a stored procedure inside a SELECT statement and return the result as a table. Although this can also be achieved using a table-valued function, this is much useful capturing result sets returned by system stored procedure like, sp_who. Although there are a number of ways to do this, I found this very simple technique. There are a number of consideration before this can be used including: user-rights, configuration setting and the size of the result set to be returned. Also, try to consider using function first. What I did is take advantage of the OPENROWSET function that allows user to access remote data from a data source. Here's a sample of retrieving the result set as returned by the system stored procedure sp_who:

SELECT a.*
FROM OPENROWSET('SQLNCLI', 'Server=.;Trusted_Connection=yes;',
'set fmtonly off; exec sp_who') AS a

As this is like a regular SELECT statement, data merge (JOIN, UNION), filter condition (WHERE) and sorting (ORDER BY) can be used. The result may also be inserted into a table using the INSERT INTO...SELECT statement or to create a new table by including a INTO new_table> statement on the SELECT statement. The new_table may also be a temporary table. Here's another sample. Although the following code may be implemented in some other ways, it was created to illustrate how the stored procedure be executed inside a SELECT statement and return the result. Here's the stored procedure:

create proc tproc (@dbname as varchar(50))
as
begin
   declare @sql varchar(500)
   set @sql = 'select * from ' + @dbname + '.dbo.sysobjects'
   exec (@sql)
end

exec dbo.tproc 'model'
exec dbo.tproc 'master'

To call in a SELECT statement and return as a table:

SELECT a.*
FROM OPENROWSET('SQLNCLI', 'Server=.;Trusted_Connection=yes;',
 'set fmtonly off; exec workdb.dbo.tproc ''master''') AS a;

SELECT a.*
FROM OPENROWSET('SQLNCLI', 'Server=.;Trusted_Connection=yes;',
 'set fmtonly off; exec workdb.dbo.tproc ''model''') AS a;

As always, comments are welcome ....


~~CK

Rollback of Temp Table vs Table Variable

Is there a difference between a Rollback of a Temp Table vs Rollback of a Table Variable? I've read a number of articles about the difference between Temp Table and Table Variable. Not until I have this major project that I got to work on one of the biggest difference between these two types of table. I am required to write, through an existing stored procedure, to a log table that contains description of each major steps on my stored procedure, including error messages. My problem is, I implemented a transaction to handle any error that my stored procedure might encounter. These errors are usually due to violations of constraint, integrity and data type implicit conversions. Once the rollback is executed, I loose all the records in log table that got inserted as my stored procedure executes. The solution: table variable. I created a table variable with the same structure as that of the log table. Records got inserted into log table during the execution of the stored procedure. When an error caused the process to fail, I inserted the value from the log table into the table variable before I executed a rollback. After the rollback, all records that got inserted to the log-table were deleted. Then I inserted everything back from the table variable to the log table. Since table variable are not affected by a rollback statement, the data was preserved in the variable. One major consideration is to figure out which records were inserted by my stored procedure so as not to duplicate the entire table.


~~ CK

Reseeding Identity Column

I received a project that involves inserting thousands of rows into a table with an identity column as its Primary Key. The concern is, with a failed transaction, there is a possibility of significant gaps within the sequence of the column. To handle the requirement, I grab the last identity value right after the transaction starts. This will also lock the table until the transaction is committed or rolled-back. Paired with the TRY..CATCH statement, I was able to rollback the transaction and reseed the table as if the insert did not happen and preserve the sequence of the identity column. I was able to use this technique since I am aware no one else is using the table as this transaction is being executed. If the table is updated frequently, transaction must be handled properly so as not to lock the table longer than necessary.
Here's the pseudo-code:

begin transaction

begin try
set @last_identity_value = ident_current(tablename)
set @last_identity_value =
 case when @last_identity_value = 1 then 0
      else @last_identity_value
 end

/*insert the records to table here*/

commit transaction
end try
begin catch
 rollback transaction
 DBCC CHECKIDENT (tablename,RESEED,@last_identity_value)
end catch

As always, comments are welcome...


~~ CK

SQL Server Reference Guide

I found this excellent article written by Buck Woody. It's a great reference guide for SQL Server, not only for beginners, also for intermediate and even advanced users. A big thank you for Mr. Woody.


~~ CK

Counting Weekends Between Date Range

I encountered a requirement that I need to count the number of Weekends, number of Saturdays and number of Sundays between two date range.
Here's what I got so far...

declare @startdate as datetime, @enddate as datetime

select  @startdate = '12-26-2008', @enddate = '01-26-2009'

select
sum(  
case
 when datename(dw,dateadd(dd,days,@startdate))
      in ('SATURDAY', 'SUNDAY') then 1
 else 0
end) as 'Number of WeekEnd',
sum(  
case
 when datename(dw,dateadd(dd,days,@startdate))
      in ('SUNDAY') then 1
 else 0
end) as 'Number of Sunday',
sum(  
case
 when datename(dw,dateadd(dd,days,@startdate))
      in ('SATURDAY') then 1
 else 0
end) as 'Number of Saturday'
from
(select top 365 colorder - 1 as days
 from master..syscolumns where id = -519536829
order by colorder) x
where datediff(dd,dateadd(dd,days,@startdate),@enddate) >= 0

Here's the resultset:

Number of WeekEnd  Number of Sunday  Number of Saturday
-----------------  ----------------  ------------------
   10                  5                 5

Any suggestions to improve this code is always welcome...


~~CK

A Power of Two in a Recursive Function

Here's a table that represents the sequence of power two raised to a sequence of integers

power    2 raise to power
------   ---------------
19           524,288
18           262,144
17           131,072
16            65,536
15            32,768
14            16,384
13             8,192
12             4,096
11             2,048
10             1,024
9               512
8               256
7               128
6                64
5                32
4                16
3                 8
2                 4
1                 2
0                 1

Now, given a number, say 42. Looking at the list, we first find the first product (2 raise to power) that is less than 42, which is 32, and get the difference (42-32=10). Now we find the first number that is less than 10, which is 8 and get the difference (10-8=2). We keep doing it until the difference becomes zero.
Let's say the number is 511. The sequence would be 511-256=255-128=127-64=63; then 63-32=31-16=15-8=7-4=3-2=1-1=0.
I created a recursive function returning the sequence of integers that served as exponents in the power of two series (make sense?). So in the first sample, 42 should return the list 32, 8, 2. In the second sample, 511 should return the list, 256, 128, 64, 32, 16, 8, 4, 2, 1 Here's the function I created:

create function GetSeries(@intVar int)
returns @t table (seqn int, series int)
as
begin

declare @seqn int, @series int

select top 1 @seqn = seqn, @series = power(2, seqn) from
(select top 20 seqn = (select count(*)
            from sysobjects b where a.id > b.id)
from sysobjects a) seqnkey
where power(2, seqn) <= @intVar order by 1 desc   set @intVar = @intVar - @series    if @intVar > 0
insert into @t (seqn, series)
select @seqn, @series
union
select * from dbo.GetSeries(@intVar)
else
insert into @t (seqn, series)
select isnull(@seqn,0), isnull(@series, power(2, 0))
return
end

Here's the sample output:

select * from GetSeries(42)
order by series desc

Here's the resultset:

seqn     series
----     ------
5         32
3          8
1          2

This may be a simple function, but there are a number of application that can use this. Also, this is a good sample of a recursive function.


~~ CK

A Basic Code for Seat Reservation Algorithm

I receive this question from the forum that I frequently visit. Given a list of availabe seat number, I need to return the first available free seats based on the required number seats.

Say the following are the available seats:
1, 2, 5, 6, 7, 8, 10, 11, 12, 13, 14, 15, 16, 17, 18, 19, 20

Say you need 1 seat, the first available seat is 1.
Say you need 2 seats, the first available seats are 1-2.
Say you need 3 seats, the first available seats are 5, 6, 7
Say you need 4 seats, the first available seats are 5, 6, 7, 8
Say you need 5 seats, the first available seats are 10, 11, 12, 13, 14
Although there are a number of ways to return the available seats, I believe some the processing has to be done on the front-end than just leaving everything on the back-end. Here is my recommended query:

declare @seat table (seatnum smallint)
declare @NumberOfSeatsNeeded smallint

insert into @seat (seatnum) values(1);
insert into @seat (seatnum) values( 2);
insert into @seat (seatnum) values( 5);
insert into @seat (seatnum) values( 6);
insert into @seat (seatnum) values( 7);
insert into @seat (seatnum) values( 8);
insert into @seat (seatnum) values( 10);
insert into @seat (seatnum) values( 11);
insert into @seat (seatnum) values( 12);
insert into @seat (seatnum) values( 13);
insert into @seat (seatnum) values( 14);
insert into @seat (seatnum) values( 15);
insert into @seat (seatnum) values( 16);
insert into @seat (seatnum) values( 17);
insert into @seat (seatnum) values( 18);
insert into @seat (seatnum) values( 19);
insert into @seat (seatnum) values( 20)

set @NumberOfSeatsNeeded = 3

select startseat, endseat,
(endseat - startseat) + 1 as numberofseats
from
(select
startseat =
(select top 1 a.seatnum
  from @seat a
left join @seat b on a.seatnum = b.seatnum + 1
where
   b.seatnum + 1 is null and x1.seatnum > a.seatnum
 order by 1 desc), 
x1.seatnum as endseat
from
(select x.seatnum
from @seat x
left join @seat y on x.seatnum = y.seatnum + 1
where y.seatnum + 1 is not null) x1
union all
select a.seatnum as startseat, b.seatnum as endseat
from @seat a
inner join @seat b on a.seatnum = b.seatnum) vacantseats
where(endseat - startseat) + 1 = @NumberOfSeatsNeeded
order by 1, 3

As I mentioned, there are other ways of doing this. Improvements are always welcome...

~~ CK