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.