How to import multiple result sets from Stored procedure?

Posted on by madhivanan

One of my friends asked me if it is possible to load multiple resultsets returned from a stored procedure into a temporary table for some analysis purpose. The answer is “It depends”. If all the result sets return same number of columns then it is possible otherwise it is not possible.

Create the following stored procedure

USE tempdb 

GO

CREATE PROCEDURE proc_test
AS 
  SELECT 1003   AS number 
  SELECT 28 AS number 
  SELECT 987345  AS number 

When you execute this procedure

  EXEC proc_test

You get three different result sets

number
-----------
1003

number
-----------
28

number
-----------
987345

Now create a temporary table

  CREATE TABLE #temp 
  ( 
       number INT 
  )

Add data to this table by executing the stored procedure

  INSERT INTO #temp 
  ( 
	number 
  ) 
  EXEC proc_test

Now check the result from this temporary table

SELECT number FROM #temp 

The result is

number
---------
1003
28
987345

So if the table structure is similar to the result set of the stored procedure, it does not matter how many similar result sets are returned, it can be imported to the table. However Currently it is not possible to identify a specific result set from the stored procedure.

Find Nth Maximum value

Posted on by madhivanan

“Find Nth Maximum value” is one of the very popular interview questions.

There are several methods to find out the answer. Here are some methods

Consider the following data set


use tempdb 

create table numbers (number integer)
Insert into numbers values(1288)
Insert into numbers values(8653)
Insert into numbers values(1)
Insert into numbers values(390)
Insert into numbers values(4009)
Insert into numbers values(345)
Insert into numbers values(2777)

Assume that you want to find out 3rd maximum value, use one of the following methods
(1) Use Inner Join

select
	t1.number from numbers t1 inner join numbers t2
	on t1.number<=t2.number  group by t1.number having count(t1.number)=3

(2) Use Top Operator

 Select top 1 number from (      Select  		top 3 number  	from  		numbers  	order by number desc ) T  order by number asc

(3) Generate Serial No based on descending order of the values

 select number from  (          Select  			( 				select  					count(*)  				from  					numbers  				where number>=T.number)as Sno ,
		number
        from numbers as T
) as temp
where Sno=3

(4) Generate Serial No based on descending order of the values in where Clause

select number from numbers as n
where
(
	select
		count(*)
	from
		numbers
	where number>=n.number
)=3

(5) Use Aggregate Function

Select min(number) from
(
	select
		top 3 number
	from
		numbers
	order by number desc
) T

(6) Use Row_number() function

select number from
(
    select
		number, row_number() over (order by number desc) as sno
	from
		numbers
) as t
where sno=3

Now you can replace 3 to the numbers that you want to find the maximum value

ODBC scalar functions – DATE , TIME and Interval functions

Posted on by madhivanan

You know very well how to return current date and time using getdate() function. Did you know that there exists ODBC function to return the same?

Select getdate()

returns current date and time

The scalar function

select {fn now()}

also returns current date and time

You can use get month, hour, day, etc using EXTRACT interval function

select {fn extract(month from getdate())}

returns the current month

select {fn extract(day from getdate())}

returns current day value

Here are some functions to explore. These 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())}