Thursday, 4 June 2015

SQL Server Real Time Query

Problem
           Write a stored procedure with parameters to pick the data with that date. If there is not data in insurance project then it has to pick from IT otherwise Banking.
Solution

First to create three tables in three various projects

use insurance
create table insurance_data
(
empid int,
date1 date,
amt1 int)

empid
date1
amt1
100
01-01-2015
10
101
02-01-2015
100
102
03-01-2015
1000
103
04-01-2015
10000

use it
create table it_data
(
empid int,
date2 date,
amt2 int)

empid
date2
amt2
201
01-02-2015
20
202
02-02-2015
200
203
03-02-2015
2000
204
04-01-2015
20000


use banking
create table banking_data
(
empid int,
date3 date,
amt3 int)


empid
date3
amt3
301
01-03-2015
30
302
02-03-2015
300
303
03-03-2015
3000
304
04-02-2015
350


Stored procedure with parameters as follows

create procedure GetData_3Projects
(
@input_date date,
@output_val int output
)
as
begin
      set @output_val=0

      select @output_val=amt1 from insurance.dbo.insurance_data where                                                                             date1=@input_date
      if(@output_val = 0)
            select @output_val=amt2 from it.dbo.it_data where                                                                 date2=@input_date
      if(@output_val = 0)
            select @output_val=amt3 from banking.dbo.banking_data where                                                                 date3=@input_date

return

end

Output


declare @output int
exec GetData_3Projects '2015-02-04',@output output

print @output

Output is 250.

Let me know what you think about this article. 

No comments:

Post a Comment