How to get only numeric column values from a VARCHAR column

SELECT column1 FROM table where ISNUMERIC(column1) = 1

find all tables in server with specific column name

select       *
from         information_schema.columns  C
where
                COLUMN_NAME like '%COLUMN_NAME %'

exclusive access could not be obtained because the database is in use sql 2008 -------------------- Delete DB ------------ Delete database which is in use -- ( Delete database)

method 1


use master
declare @sql as varchar(20), @spid as int
select @spid = min(spid)  from master..sysprocesses  where dbid = db_id('OneSource2015_DevTest')
and spid != @@spid  

while (@spid is not null)
begin
    print 'Killing process ' + cast(@spid as varchar) + ' ...'
    set @sql = 'kill ' + cast(@spid as varchar)
    exec (@sql)

    select
        @spid = min(spid)
    from
        master..sysprocesses
    where
        dbid = db_id('OneSource2015_DevTest')
        and spid != @@spid
end

print 'Process completed...'


if u want to drop db: use    below line
Drop database OneSource2015_DevTest

method 2:

use master
go
alter database <dbname>
set single_user with rollback immediate
go
alter database <dbname>
set multi_user
go

method 3

alter database database_name
set offline with rollback immediate
go

alter database database_name
set online
go

list of stored pocs with specific table name

SELECT Name

FROM sys.procedures

WHERE OBJECT_DEFINITION(OBJECT_ID) LIKE '%table name%'

------Example-------

SELECT Name

FROM sys.procedures

WHERE OBJECT_DEFINITION(OBJECT_ID) LIKE '%account_total%'

DB sample

/*
Template for creating a new schedule (Using input fields and linked to accounts)
*/


/* Template for error handling and transactions(big data sets)

declare @newTransactionUId uniqueidentifier
declare @newTransactionId char(36)

set @newTransactionUId = NEWID()
set @newTransactionId = REPLACE(@newTransactionUid,'-','')

select @newTransactionId

begin try
begin transaction @newTransactionId



commit transaction @newTransactionId
end try
begin catch
if(@@TRANCOUNT>0)
begin
rollback transaction @newTransactionId
end
print @@ERROR
end catch

*/

USE OneSource2015
GO

/*

Create table variables.

*/


declare @schedule table
(
client_id int null,
schedule_group_id int null,
schedule_prefix varchar(50) not null,
schedule_name varchar(255) not null,
schedule_sequence int not null,
schedule_type int not null,
schedule_status char(1) not null,
roll_forward bit not null,
effective_year int null,
end_year int null,
modify_date datetime not null,
print_pagesize char(2) null,
print_orientation char(1) null
)

declare @task table
(

[client_id] [int] NULL,
[parent_task] [int] NULL,
[task_description] [varchar](100) NOT NULL,
[task_level] [int] NOT NULL,
[task_sequence] [int] NOT NULL,
[task_type] [char](1) NOT NULL,
[task_url] [varchar](1000) NULL,
[schedule_id] [int] NULL,
[status_ind] [char](1) NOT NULL

)

declare @task_allocation table
(

[task_id] [int] NOT NULL,
[role_id] [uniqueidentifier] NULL,
[client_id] [int] NULL,
[role_ind] [char](1) NULL,
[status_ind] [char](1) NOT NULL,
[modify_date] [datetime] NOT NULL
)

declare  @schedule_parm table
(
[schedule_id] [int] NOT NULL,
[selection] [int] NOT NULL,
[description_id] [int] NOT NULL,
[filter] [varchar](50) NULL,
[linked_schedule_parm_id] [int] NULL,
[linked_filter] [varchar](50) NULL,
[allow_inserts] [bit] NULL
)

declare @schedule_header table
(
      header_text varchar(900) not null
)


declare @schedule_list table
(
    schedule_list_id int not null,
[client_id] [int] NOT NULL,
[list_value] [int] NOT NULL,
[list_text] [varchar](100) NOT NULL,
[status_ind] [char](1) NOT NULL
)


declare @schedule_calculation table
(
         calculation_string  varchar(255) null
)

declare @account table
(
    [client_id] [int] NULL,
[chart_id] [int] NULL,
[account_group] [char](1) NULL,
[account_type_id] [char](1) NULL,
[account_number] [varchar](100) NULL,
[account_description] [varchar](200) NULL,
[effective_year] [int] NOT NULL,
[end_year] [int] NULL,
[status_ind] [char](1) NOT NULL,
[modify_date] [datetime] NOT NULL
)

declare @schedule_template table
(
[schedule_id] [int] NOT NULL,
[column_no] [int] NOT NULL,
[client_id] [int] NOT NULL,
[selection] [int] NOT NULL,
[schedule_header_id] [int] NULL,
[schedule_list_id] [int] NULL,
[account_id] [int] NULL,
[calculation_id] [int] NULL,
[column_type] [char](1) NOT NULL,
[column_format] [varchar](50) NULL,
[protected_ind] [bit] NOT NULL,
[display_ind] [bit] NOT NULL,
[display_sequence] [int] NOT NULL,
[print_ind] [bit] NOT NULL,
[print_sequence] [int] NULL,
[sort_sequence] [int] NULL,
[sort_asc] [bit] NULL,
[filter] [varchar](255) NULL,
[effective_year] [int] NOT NULL,
[rollover_column] [int] NULL,
[end_year] [int] NULL,
[modify_date] [datetime] NOT NULL,
[group_by] [int] NOT NULL,
[linked_column] [int] NULL

)


declare @schedule_footer table
(
[client_id] [int] NOT NULL,
[schedule_id] [int] NOT NULL,
[selection] [int] NOT NULL,
[line_no] [int] NOT NULL,
[column_no] [int] NOT NULL,
[schedule_header_id] [int] NULL,
[account_id] [int] NULL,
[calculation_id] [int] NULL,
[column_type] [char](1) NOT NULL,
[column_format] [varchar](50) NULL,
[column_style] [varchar](50) NULL,
[reverse_account] [int] NULL,
[balance_account_id] [int] NULL,
[effective_year] [int] NOT NULL,
[end_year] [int] NULL
)


declare @schedule_list_id int
set @schedule_list_id =
(
      select
            MAX(schedule_list_id) +1
      from schedule_list
)


declare @newTransactionUId uniqueidentifier
declare @newTransactionId char(36)


set @newTransactionUId = NEWID()
set @newTransactionId = REPLACE(@newTransactionUid,'-','')



begin try
begin transaction @newTransactionId

  -- 1.) Insert into schedule temp table:

          insert into @schedule
select
null 'client_id',
(
select top 1
schedule_group_id
from
schedule_group
where
group_name='K - Section 6quat rebate'
)  'schedule_group_id',
'C 4.5' 'schedule_prefix',
'Transfer pricing and financial' 'schedule_name',
18 'schedule_sequence',
1 'schedule_type',
'S' 'schedule_status',
0 'roll_forward',
2006 'effective_year',
null 'end_year',
GETDATE() 'modify_date',
'A4' 'print_pagesize',
'P' 'print_orientation'


SELECT 'schedule' As title
select * from @schedule AS schedule



  -----Actual insert from temp table to actual table

insert
into schedule
(client_id,schedule_group_id,schedule_prefix,schedule_name,schedule_sequence,schedule_type,schedule_status,roll_forward,effective_year,end_year,modify_date,print_pagesize,print_orientation)
SELECT
new.client_id,
new.schedule_group_id,
new.schedule_prefix 'schedule_prefix',
new.schedule_name 'schedule_name',
new.schedule_sequence,
new.schedule_type,
new.schedule_status,
new.roll_forward,
new.effective_year,
new.end_year,
new.modify_date,
new.print_pagesize,
new.print_orientation
FROM
schedule existing
right join
@schedule new
on
(
new.schedule_prefix=existing.schedule_prefix and
new.schedule_name=existing.schedule_name
)
where
existing.schedule_prefix is null
and
existing.schedule_name is null

select * from schedule


-- 2. insert into schedule_parm temp table

insert into @schedule_parm
select
ISNULL((
select
top 1 schedule_id
from
schedule
where
schedule_prefix='C 4.5'
and
schedule_name ='Transfer pricing and financial'
),0) 'schedule_id',
0 'selection',
ISNULL((
select
top 1 schedule_id
from
schedule
where
schedule_prefix='C 4.5'
and
schedule_name ='Transfer pricing and financial'
),0) 'description_id',
null 'filter',
null 'linked_schedule_parm_id',
null 'linked_filter',
null 'allow_inserts'


---Actual insert into schedule_param table
INSERT INTO
schedule_parm(schedule_id,selection,description_id,filter,linked_schedule_parm_id,linked_filter,allow_inserts)
SELECT
new.schedule_id 'schedule_id',
new.selection,
new.description_id 'description_id',
new.filter,
new.linked_schedule_parm_id,
new.linked_filter,
new.allow_inserts
FROM  
schedule_parm existing
right join
@schedule_parm new
on
(
new.schedule_id=existing.schedule_id and
new.description_id=existing.description_id
)
where
existing.schedule_id is null and
existing.description_id is null



SELECT 'schedule_parm' As title
    select * from @schedule_parm AS schedule_parm
    select * from schedule_parm AS schedule_parm
 
 
 
   --3. insert into task temp table
insert into @task
select
null 'client_id',
(
select
 parent_task
from task where task_id=171
)
'parent_task',
 'Schedule C 4.5 - Transfer pricing and financial' 'task_description',
 5 'task_level',
 41 'task_sequence',
 'P' 'task_type',
 ISNULL((select
'frmDisclosureScheduleEngine.aspx?Schedule_Parm_ID=' +
CONVERT(NVARCHAR(50),(select
schedule_parm_id
from
schedule_parm
where
schedule_id =
ISNULL((
select
top 1 schedule_id
from
schedule
where
schedule_prefix='C 4.5'
and
schedule_name ='Transfer pricing and financial'
),0)))
),0) 'task_url',
ISNULL((
select
top 1 schedule_id
from
schedule
where
schedule_prefix='C 4.5'
and
schedule_name ='Transfer pricing and financial'
),0) 'schedule_id',
'A' 'status_ind'



---Actual insert into Task table

INSERT
INTO
Task(client_id,parent_task,task_description,task_level,task_sequence,task_type,task_url,schedule_id,status_ind)
SELECT
new.client_id,
new.parent_task,
new.task_description 'task_description',
new.task_level,
new.task_sequence,
new.task_type,
new.task_url  'task_url',
new.schedule_id 'schedule_id',
new.status_ind
FROM
task  existing
right join
@task new
on
(
existing.task_description=new.task_description and
existing.task_url=new.task_url
)
where
existing.task_description is null and
existing.task_url is null

SELECT 'Tasktable' As title
select * from @task   as   Task
select * from task


--4.  insert into temp task_allocation table

insert into @task_allocation
                  select    
                              ISNull((select
                                                top 1 task_id
                                          from
                                    task
                              where
                                    task_description='Schedule C 4.5 - Transfer pricing and financial')
                                    ,0)
                'task_id',
                (
                        select
                              top 1 roleid
                        from
                              aspnet_Roles
                        where
                              RoleName = 'Admin'
                ) 'role_id',
                null 'client_id',
                'S'  'role_ind',
                'A' 'status_ind',
                getdate() 'modify_date'
               
            insert into @task_allocation
                  select    
                              ISNull((select
                                                top 1 task_id
                                          from
                                    task
                              where
                                    task_description='Schedule C 4.5 - Transfer pricing and financial')
                                    ,0)
                'task_id',
                (
                        select
                              top 1 roleid
                        from
                              aspnet_Roles
                        where
                              RoleName = 'SuperUser'
                ) 'role_id',
                null 'client_id',
                'S'  'role_ind',
                'A' 'status_ind',
                getdate() 'modify_date'
               
            insert into @task_allocation
                  select    
                              ISNull((select
                                                top 1 task_id
                                          from
                                    task
                              where
                                    task_description='Schedule C 4.5 - Transfer pricing and financial')
                                    ,0)
                'task_id',
                (
                        select
                              top 1 roleid
                        from
                              aspnet_Roles
                        where
                              RoleName = 'Reviewer'
                ) 'role_id',
                null 'client_id',
                'S'  'role_ind',
                'A' 'status_ind',
                getdate() 'modify_date'
               
            insert into @task_allocation
                  select    
                              ISNull((select
                                                top 1 task_id
                                          from
                                    task
                              where
                                    task_description='Schedule C 4.5 - Transfer pricing and financial')
                                    ,0)
                'task_id',
                (
                        select
                              top 1 roleid
                        from
                              aspnet_Roles
                        where
                              RoleName = 'Preparer'
                ) 'role_id',
                null 'client_id',
                'S'  'role_ind',
                'A' 'status_ind',
                getdate() 'modify_date'

 
  -----actual insert into TaskAllocation table

INSERT
                  INTO
                        task_allocation(task_id,role_id,status_ind,modify_date)
            select
                        new.task_id 'task_id',
                        new.role_id,
                        new.status_ind,
                        new.modify_date
            from
                        task_allocation existing
                        right join
                        @task_allocation new
                        on
                              (                            
                        existing.task_id=new.task_id and
                        existing.role_id=new.role_id
                              )
            where
                              existing.task_id is null


SELECT 'taskallocationtable' As title  
select * from @task_allocation  as taskallocationtable
select * from task_allocation  as taskallocationtable


--5.  insert into temp  schedule_header table
insert into @schedule_header
select
'Nature' 'header_text'
union
select
'Amount' 'header_text'
union
select
'Transfer pricing adjustment' 'header_text'
union
select
'Financial assistance' 'header_text'
union
select
'Transfer pricing other than financial assistance' 'header_text'


----actual insert into ScheduleHeader  Table
INSERT INTO schedule_header(header_text)
select
new.header_text 'header_text'
from
schedule_header existing
right join
@schedule_header new
on
(
existing.header_text=new.header_text
)
where
existing.header_text is null


SELECT 'ScheduleHeaderTable' As title  
select * from @schedule_header  as ScheduleHeaderTable



--6.  insert into temp Schedule_list  table
   insert into @schedule_list
      select  
            @schedule_list_id 'schedule_list_id',
            0 'client_id',
            1 'list_value',
            'Yes' 'list_text',
            'A' 'status_ind'          
            union
            select
            @schedule_list_id 'schedule_list_id',
            0 'client_id',
            2 'list_value',
            'No' 'list_text',
            'A' 'status_ind'



--- actual insert into schedule_list  table
insert into schedule_list
(client_id,list_value,list_text,status_ind)
select
new.client_id,
new.list_value,
new.list_text,
new.status_ind
from
schedule_list  existing
right join
@schedule_list new
on
(
  existing.schedule_list_id=new.schedule_list_id and
  existing.list_value=new.list_value
)
where
existing.list_value is null

SELECT 'schedule_listTable' As title  
select * from @schedule_list as schedule_listTable


--7.  insert into temp account table
insert into @account
select
 null 'client_id',
 1 'chart_id',
 'S' 'account_group',
 null 'account_type_id',
 'C 4.5' 'account_number',
 'Transfer pricing and financial' 'account_description',
 2007 'effective_year',
 null 'end_year',
 'A' 'status_ind',
 GETDATE() 'modify_date'


 ---insert into actual table
 insert
into
account(client_id,chart_id,account_group,account_type_id,account_number,account_description,effective_year,end_year,status_ind,modify_date)
 select
new.client_id,
new.chart_id,
new.account_group,
new.account_type_id,
new.account_number,
new.account_description,
new.effective_year,
new.end_year,
new.status_ind,
new.modify_date
from
account existing
right join
   @account new
on
(
existing.account_number=new.account_number and
existing.account_description=new.account_description
)
where
existing.account_number is null and
existing.account_description is null



SELECT 'accountTable' As title  
select * from @account  as accountTable


--8.  insert into temp schedule_template table
insert into @schedule_template
select
ISNULL(
      (select
            top 1
           schedule_id
            from schedule where
            schedule_prefix = 'C 4.5' and
       schedule_name='Transfer pricing and financial' )
     ,0) 'schedule_id',
   
      1 'column_no',
      0 'client_id',
      0 'selection',
      ISNULL((
select
top 1 schedule_header_id
from
schedule_header
where
header_text = 'Nature'
),0) 'schedule_header_id',

null 'schedule_list_id',
null 'account_id',
null 'calculation_id',
'M' 'column_type',
null 'column_format',
0 'protected_ind',
1 'display_ind',
1 'display_sequence',
1 'print_ind',
1 'print_sequence',
null 'sort_sequence',
null 'sort_asc',
null 'filter',
   2006 'effective_year',
null 'rollover_column',
null 'end_year',
getdate() 'modify_date',
0 'group_by',
null 'linked_column'

---union 'Amount'
union

select
           ISNULL(
  (select
top 1
schedule_id
        from schedule where
        schedule_prefix = 'C 4.5' and
schedule_name='Transfer pricing and financial' )
 ,0) 'schedule_id',
   
  2 'column_no',
  0 'client_id',
  0 'selection',
  ISNULL((
select
top 1 schedule_header_id
from
schedule_header
where
header_text = 'Amount'
),0) 'schedule_header_id',

null 'schedule_list_id',
ISNULL (
(
select top 1 account_id
    from account
where
   account_description='Transfer pricing and financial')
,0) 'account_id',
null 'calculation_id',
'M' 'column_type',
'{0:#,#.00}' 'column_format',
0 'protected_ind',
1 'display_ind',
2 'display_sequence',
1 'print_ind',
2 'print_sequence',
null 'sort_sequence',
null 'sort_asc',
null 'filter',
2006 'effective_year',
null 'rollover_column',
null 'end_year',
getdate() 'modify_date',
0 'group_by',
null 'linked_column'
--union 'Transfer pricing adjustment'
union

select
           ISNULL(
  (select
top 1
schedule_id
        from schedule where
        schedule_prefix = 'C 4.5' and
schedule_name='Transfer pricing and financial' )
 ,0) 'schedule_id',
   
  3 'column_no',
  0 'client_id',
  0 'selection',
  ISNULL((
select
top 1 schedule_header_id
from
schedule_header
where
header_text = 'Transfer pricing adjustment'
),0) 'schedule_header_id',

189 'schedule_list_id',
null 'account_id',
null 'calculation_id',
'M' 'column_type',
null 'column_format',
0 'protected_ind',
1 'display_ind',
3 'display_sequence',
1 'print_ind',
3 'print_sequence',
null 'sort_sequence',
null 'sort_asc',
null 'filter',
2006 'effective_year',
null 'rollover_column',
null 'end_year',
getdate() 'modify_date',
0 'group_by',
null 'linked_column'


union

select
ISNULL(
  (select
top 1
schedule_id
        from schedule where
        schedule_prefix = 'C 4.5' and
schedule_name='Transfer pricing and financial' )
 ,0) 'schedule_id',
   
  4 'column_no',
  0 'client_id',
  0 'selection',
  ISNULL((
select
top 1 schedule_header_id
from
schedule_header
where
header_text = 'Financial assistance'
),0) 'schedule_header_id',

null 'schedule_list_id',
null 'account_id',
null 'calculation_id',
'M' 'column_type',
'{0:#,#.00}' 'column_format',
0 'protected_ind',
1 'display_ind',
4 'display_sequence',
1 'print_ind',
4 'print_sequence',
null 'sort_sequence',
null 'sort_asc',
null 'filter',
2006 'effective_year',
null 'rollover_column',
null 'end_year',
getdate() 'modify_date',
0 'group_by',
null 'linked_column'

      -----union Transfer pricing other than financial assistance
      ---no account id,no List id,follow column format
      union
      select
ISNULL(
  (select
top 1
schedule_id
        from schedule where
        schedule_prefix = 'C 4.5' and
schedule_name='Transfer pricing and financial' )
 ,0) 'schedule_id',
   
  4 'column_no',
  0 'client_id',
  0 'selection',
  ISNULL((
select
top 1 schedule_header_id
from
schedule_header
where
header_text = 'Transfer pricing other than financial assistance'
),0) 'schedule_header_id',

null 'schedule_list_id',
null 'account_id',
null 'calculation_id',
'M' 'column_type',
'{0:#,#.00}' 'column_format',
0 'protected_ind',
1 'display_ind',
5 'display_sequence',
1 'print_ind',
5 'print_sequence',
null 'sort_sequence',
null 'sort_asc',
null 'filter',
2006 'effective_year',
null 'rollover_column',
null 'end_year',
getdate() 'modify_date',
0 'group_by',
null 'linked_column'
   
     SELECT 'schedule_templateTable' As title  
    select * from @schedule_template as schedule_templateTable
   
   
   
    --9.  insert into temp schedule_footer table
   
    ---------insert options for Column_no,scheduleheader_id,account_id,column_type,column_format,column_style,reverse_account,balance_account_id
    /*  Case 1
    headertext=Nature
    Column_no 4,
    scheduleheader_id null,
    account_id get acc id,
    column_type M,
    column_format FORMAT,
    column_style  NULL,
    reverse_account 0,
    balance_account_id NULL
    */
    insert into @schedule_footer  
   
   
     /*  case 1
    headertext=Nature
    Column_no 1,
    scheduleheader_id GET ID,
    account_id get acc NULL,
    column_type T,
    column_format null,
    column_style  'font-weight:bold',
    reverse_account 0,
    balance_account_id NULL
    */
    select
     0 'client_id',
           ISNULL(
      (select
            top 1
           schedule_id
            from schedule where
            schedule_prefix = 'C 4.5' and
       schedule_name='Transfer pricing and financial' )
     ,0) 'schedule_id'      
           ,0 'selection'
           ,1 'line_no'
           ,4 'column_no'
           ,null 'schedule_header_id'
           ,ISNULL (
(
select top 1 account_id
    from account
where
   account_description='Transfer pricing and financial')
,0)  'account_id'
           ,null 'calculation_id'
           ,'M' 'column_type'
           ,'{0:#,#.00}' 'column_format'
           ,null 'column_style'
           ,0 'reverse_account' --------TBA
           ,null 'balance_account_id'       ---------TBA
           ,2006 'effective_year'          
           ,0  'end_year'
         
                   
           union
         
         
         
            /*  case 2
    headertext=Nature
    Column_no 1,
    scheduleheader_id GET ID,
    account_id get acc NULL,
    column_type T,
    column_format null,
    column_style  'font-weight:bold',
    reverse_account 0,
    balance_account_id NULL
    */
select
0 'client_id',
ISNULL(
(select
top 1
schedule_id
from schedule where
schedule_prefix = 'C 4.5' and
schedule_name='Transfer pricing and financial' )
,0) 'schedule_id'      
,0 'selection'
,1 'line_no'
,1 'column_no'
, ISNULL((
select
top 1 schedule_header_id
from
schedule_header
where
header_text = 'Nature'
),0) 'schedule_header_id'
,null  'account_id'
,null 'calculation_id'
,'T' 'column_type'
,null 'column_format'
,'font-weight:bold' 'column_style'
,0 'reverse_account' --------TBA
,null 'balance_account_id'       ---------TBA
,2006 'effective_year'          
,0  'end_year' --------TBA    

        union    
         
         
            /*  case 3
    headertext=Amount
    line number=2
    Column_no 1,
    scheduleheader_id GET ID,
    account_id get acc NULL,
    column_type T,
    column_format null,
    column_style  'font-weight:bold',
    reverse_account 0,
    balance_account_id NULL
    */

select
0 'client_id',
ISNULL(
(select
top 1
schedule_id
from schedule where
schedule_prefix = 'C 4.5' and
schedule_name='Transfer pricing and financial' )
,0) 'schedule_id'      
,0 'selection'
,2 'line_no'
,1 'column_no'
, ISNULL((
select
top 1 schedule_header_id
from
schedule_header
where
header_text = 'Amount'
),0) 'schedule_header_id'
,null  'account_id'
,null 'calculation_id'
,'T' 'column_type'
,null 'column_format'
,'font-weight:bold' 'column_style'
,0 'reverse_account' --------TBA
,null 'balance_account_id'       ---------TBA
,2006 'effective_year'          
,0  'end_year' --------TBA  

union


 /*  case 4
    headertext=Amount
    line number=2
    Column_no 4,
    scheduleheader_id GET ID,
    account_id get acc getaid,
    column_type m,
    column_format null,
    column_style  bold,
    reverse_account tba null,
    balance_account_id NULL
    */

select
0 'client_id',
ISNULL(
(select
top 1 schedule_id
from
schedule
where
schedule_prefix = 'C 4.5'
and
schedule_name='Transfer pricing and financial' )
,0) 'schedule_id'      
,0 'selection'
,2 'line_no'
,4 'column_no'
, ISNULL((
select
top 1 schedule_header_id
from
schedule_header
where
header_text = 'Amount'
),0)
'schedule_header_id'
,ISNULL (
(select
top 1 account_id
from
account
where
account_description='Transfer pricing and financial')
,0)
'account_id'
,null 'calculation_id'
,'M' 'column_type'
,'{0:#,#.00}' 'column_format'
,null 'column_style'
,0 'reverse_account' --------TBA
,null 'balance_account_id'       ---------TBA
,2006 'effective_year'          
,0  'end_year' --------TBA  

union

 /*  case 5
    headertext='Transfer pricing adjustment'
    line number=3  
    Column_no 4,
    scheduleheader_id GET ID,
    account_id get acc getaid,
    column_type m,
    column_format null,
    column_style  bold,
    reverse_account tba null,
    balance_account_id NULL
    */

select
0 'client_id',
ISNULL(
(select
top 1
schedule_id
from schedule where
schedule_prefix = 'C 4.5' and
schedule_name='Transfer pricing and financial' )
,0) 'schedule_id'      
,0 'selection'
,2 'line_no'
,1 'column_no'
, ISNULL((
select
top 1 schedule_header_id
from
schedule_header
where
header_text = 'Transfer pricing adjustment'
),0) 'schedule_header_id'
,ISNULL (
(
select top 1 account_id
from
account
where
account_description='Transfer pricing and financial')
,0)
'account_id'
,null 'calculation_id'
,'M' 'column_type'
,'{0:#,#.00}' 'column_format'
,null 'column_style'
,0 'reverse_account' --------TBA
,null 'balance_account_id'       ---------TBA
,2006 'effective_year'          
,0  'end_year' --------TBA  


union


 /*  case 6
    headertext='Transfer pricing adjustment'
    line number=3  
    Column_no 1,
    scheduleheader_id GET ID,
    account_id  null,
    column_type T,
    column_format null,
    column_style  bold,
    reverse_account tba null,
    balance_account_id NULL
    */

select
0 'client_id',
ISNULL(
(select
top 1
schedule_id
from schedule where
schedule_prefix = 'C 4.5' and
schedule_name='Transfer pricing and financial' )
,0) 'schedule_id'      
,0 'selection'
,2 'line_no'
,1 'column_no'
, ISNULL((
select
top 1 schedule_header_id
from
schedule_header
where
header_text = 'Transfer pricing adjustment'
),0) 'schedule_header_id'
,null  'account_id'
,null 'calculation_id'
,'M' 'column_type'
,null 'column_format'
,'font-weight:bold' 'column_style'
,0 'reverse_account' --------TBA
,null 'balance_account_id'       ---------TBA
,2006 'effective_year'          
,0  'end_year' --------TBA  


union


 /*  case 7
    headertext='Financial assistance'
    line number=4  
    Column_no 1,
    scheduleheader_id GET ID,
    account_id  null,
    column_type T,
    column_format null,
    column_style  bold,
    reverse_account tba null,
    balance_account_id NULL
    */

select
0 'client_id',
ISNULL(
(select
top 1
schedule_id
from schedule where
schedule_prefix = 'C 4.5' and
schedule_name='Transfer pricing and financial' )
,0) 'schedule_id'      
,0 'selection'
,4 'line_no'
,1 'column_no'
, ISNULL((
select
top 1 schedule_header_id
from
schedule_header
where
header_text = 'Financial assistance'
),0) 'schedule_header_id'
,null 'account_id'
,null 'calculation_id'
,'M' 'column_type'
,null 'column_format'
,'font-weight:bold' 'column_style'
,0 'reverse_account' --------TBA
,null 'balance_account_id'       ---------TBA
,2006 'effective_year'          
,0  'end_year' --------TBA


        union
       
       
          /*  case 8
    headertext='Financial assistance'
    line number=4  
    Column_no 4,
    scheduleheader_id GET ID,
    account_id  getId,
    column_type M,
    column_format '{0:#,#.00}',
    column_style  null,
    reverse_account tba null,
    balance_account_id NULL
    */

select
0 'client_id',
ISNULL(
(select
top 1
schedule_id
from schedule where
schedule_prefix = 'C 4.5' and
schedule_name='Transfer pricing and financial' )
,0) 'schedule_id'      
,0 'selection'
,4 'line_no'
,1 'column_no'
, ISNULL((
select
top 1 schedule_header_id
from
schedule_header
where
header_text = 'Financial assistance'
),0)
'schedule_header_id'
,ISNULL (
(select
top 1 account_id
from
account
   where
account_description='Transfer pricing and financial')
,0)
'account_id'
,null 'calculation_id'
,'M' 'column_type'
,'{0:#,#.00}' 'column_format'
,null 'column_style'
,0 'reverse_account' --------TBA
,null 'balance_account_id'       ---------TBA
,2006 'effective_year'          
,0  'end_year' --------TBA
       
        union
       
       
       
       
            /*  case 9
    headertext='Transfer pricing other than financial assistance'
    line number=5  
    Column_no 4,
    scheduleheader_id GET ID,
    account_id  getId,
    column_type M,
    column_format '{0:#,#.00}',
    column_style  null,
    reverse_account tba null,
    balance_account_id NULL
    */

select
0 'client_id',
ISNULL(
(select
top 1
schedule_id
from schedule where
schedule_prefix = 'C 4.5' and
schedule_name='Transfer pricing and financial' )
,0) 'schedule_id'      
,0 'selection'
,5 'line_no'
,4 'column_no'
, ISNULL((
select
top 1 schedule_header_id
from
schedule_header
where
header_text = 'Transfer pricing other than financial assistance'
),0)
'schedule_header_id'
,ISNULL (
(select
top 1 account_id
from
account
   where
account_description='Transfer pricing and financial')
,0)
'account_id'
,null 'calculation_id'
,'M' 'column_type'
,'{0:#,#.00}' 'column_format'
,null 'column_style'
,0 'reverse_account' --------TBA
,null 'balance_account_id'       ---------TBA
,2006 'effective_year'          
,0  'end_year' --------TBA
       
        union
       
                  /*  case 10
    headertext='Transfer pricing other than financial assistance'
    line number=5  
    Column_no 1,
    scheduleheader_id GET ID,
    account_id  null,
    column_type t,
    column_format null,
    column_style  bold,
    reverse_account tba null,
    balance_account_id NULL
    */

select
0 'client_id',
ISNULL(
(select
top 1
schedule_id
from schedule where
schedule_prefix = 'C 4.5' and
schedule_name='Transfer pricing and financial' )
,0) 'schedule_id'      
,0 'selection'
,5 'line_no'
,1 'column_no'
, ISNULL((
select
top 1 schedule_header_id
from
schedule_header
where
header_text = 'Transfer pricing other than financial assistance'
),0)
'schedule_header_id'
,null 'account_id'
,null 'calculation_id'
,'T' 'column_type'
,null 'column_format'
,'font-weight:bold' 'column_style'
,0 'reverse_account' --------TBA
,null 'balance_account_id'       ---------TBA
,2006 'effective_year'          
,0  'end_year' --------TBA
       
         
     SELECT 'schedule_footerTable' As title  
    select * from  @schedule_footer  AS  schedule_footerTable
   



commit transaction @newTransactionId
end try
begin catch
if(@@TRANCOUNT>0)
begin
rollback transaction @newTransactionId
end
SELECT
        ERROR_NUMBER() AS ErrorNumber,
        ERROR_MESSAGE() AS ErrorMessage;
end catch








 
 
 









commit transaction @newTransactionId
end try
begin catch
if(@@TRANCOUNT>0)
begin
rollback transaction @newTransactionId
end
SELECT
        ERROR_NUMBER() AS ErrorNumber,
        ERROR_MESSAGE() AS ErrorMessage;
end catch