Guid insertion - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Guid insertion

Author

Topic

sent_sara
Constraint Violating Yak Guru

377 Posts

Posted - 2008-09-10 : 05:21:46

Hi Friends,
How to Insert guid in table [mis_prepared_tmp] for the below query:

insert into mis_prepared_tmp(ord,head,head_type,Aviation,Non_Aviation,sas,bpo,
sub_total,crm,Grant_total,label,company_code,currency,Guid)
exec Mis_PL_Main_sp 'ALL','ALL',@yr_st_date_tmp,@pr_month_end_tmp,'USD','ADMIN'

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2008-09-10 : 05:31:15

quote:
Originally posted by sent_sara

Hi Friends,
How to Insert guid in table [mis_prepared_tmp] for the below query:

insert into mis_prepared_tmp(ord,head,head_type,Aviation,Non_Aviation,sas,bpo,
sub_total,crm,Grant_total,label,company_code,currency,Guid)
exec Mis_PL_Main_sp 'ALL','ALL',@yr_st_date_tmp,@pr_month_end_tmp,'USD','ADMIN'

Do the procedure return values for all the columns except for guid?

Madhivanan

Failing to plan is Planning to fail

sent_sara
Constraint Violating Yak Guru

377 Posts

Posted - 2008-09-10 : 05:31:57

Yes

quote:
Originally posted by madhivanan

quote:
Originally posted by sent_sara

Hi Friends,
How to Insert guid in table [mis_prepared_tmp] for the below query:

insert into mis_prepared_tmp(ord,head,head_type,Aviation,Non_Aviation,sas,bpo,
sub_total,crm,Grant_total,label,company_code,currency,Guid)
exec Mis_PL_Main_sp 'ALL','ALL',@yr_st_date_tmp,@pr_month_end_tmp,'USD','ADMIN'

Do the procedure return values for all the columns except for guid?

Madhivanan

Failing to plan is Planning to fail

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2008-09-10 : 05:36:17

Alter the table to have guid column with default value newid()

Madhivanan

Failing to plan is Planning to fail

sent_sara
Constraint Violating Yak Guru

377 Posts

Posted - 2008-09-10 : 06:01:19

guid is initialized in the begining of stored procedures ,any other alternative way??

quote:
Originally posted by madhivanan

Alter the table to have guid column with default value newid()

Madhivanan

Failing to plan is Planning to fail

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-09-10 : 06:02:37

Show FULL code?

E 12°55'05.63"
N 56°04'39.26"

sent_sara
Constraint Violating Yak Guru

377 Posts

Posted - 2008-09-10 : 07:28:40

alter Procedure Mis_T2_t1_sp
(
@Company_code_in varchar(10),
@fb_id_in varchar(20),
@From_Date_in varchar(20),
@To_Date_in varchar(20),
@Currency_Type_in char(5)
)
as
begin
set nocount off

Declare @yr_st_date_tmp varchar(20)
Declare @to_date_tmp varchar(20)
Declare @pr_month_end_tmp varchar(20)
Declare @guid_tmp varchar(256)
Declare @Highest_Table table(
sno int,head varchar(50),head_type varchar(100),aviation numeric(28,8),non_aviation numeric(18,8),sas numeric(18,8),bpo numeric(28,8),sub_total numeric(28,8),crm numeric(28,8),grant_total numeric(28,8),label varchar(100),
company_code varchar(10),currency varchar(5),period_from datetime,period_to datetime,guid varchar(256)
)

Declare @Lowest_Table table(sno int,head varchar(50),head_type varchar(100),aviation numeric(28,8),non_aviation numeric(18,8),sas numeric(18,8),bpo numeric(28,8),sub_total numeric(28,8),crm numeric(28,8),grant_total numeric(28,8),label varchar(100),
company_code varchar(10),currency varchar(5),period_from datetime,period_to datetime,guid varchar(256))

set @guid_tmp=newid()

/* inserting Template into Table Variable */
insert into @Highest_Table(sno,head,head_type,guid)
select *,@guid_tmp from mis_template(nolock)

/* inserting Template into Table Variable */
insert into @Lowest_Table(sno,head,head_type,guid)
select *,@guid_tmp from mis_template(nolock)

--SELECT 'SENTHIL'

select
@yr_st_date_tmp = convert(varchar,Year_st_date,106),
@to_date_tmp = convert(varchar,To_Date,106),
@pr_month_end_tmp= convert(varchar,pr_month_end,106)
from Mis_Date_Range(nolock)
where from_date=@From_Date_in and To_Date=@To_Date_in

insert into mis_prepared_tmp(ord,head,head_type,Aviation,Non_Aviation,sas,bpo,
sub_total,crm,Grant_total,label,company_code,currency)
exec Mis_PL_Main_sp @Company_code_in,@fb_id_in,@yr_st_date_tmp,@to_date_tmp,@Currency_Type_in,'ADMIN'

update hig
set
Aviation = tmp.Aviation
,Non_Aviation = tmp.Non_Aviation
,sas = tmp.sas
,bpo = tmp.bpo
,sub_total = tmp.sub_total
,crm = tmp.crm
,Grant_total = tmp.Grant_total
,label = 'MIS P&L Report For the period ' +Lower(convert(varchar,@from_date_in,106)) +' To ' +lower(convert(varchar,@to_date_in,106))
,company_code = tmp.company_code
,currency = tmp.currency
,PERIOD_from = @from_date_in
,PERIOD_to = @to_date_in
from @Highest_Table hig inner join mis_prepared_tmp tmp
on hig.head_type=tmp.head_type
--guid join
where hig.guid=@guid_tmp

delete from mis_prepared_tmp --where guid=@guid_tmp

insert into mis_prepared_tmp(ord,head,head_type,Aviation,Non_Aviation,sas,bpo,
sub_total,crm,Grant_total,label,company_code,currency)
exec Mis_PL_Main_sp @Company_code_in,@fb_id_in,@yr_st_date_tmp,@pr_month_end_tmp,'USD','ADMIN'

if (month(@from_date_in)=4 and month(@to_date_in)=4)
or (month(@from_date_in)=4 and month(@to_date_in)=6)
or (month(@from_date_in)=4 and month(@to_date_in)=9)
or (month(@from_date_in)=4 and month(@to_date_in)=3)
begin
update low
set
Aviation = 0
,Non_Aviation = 0
,sas = 0
,bpo = 0
,sub_total = 0
,crm = 0
,Grant_total = 0
,label = 0
,company_code = 0
,currency = 0
from @Lowest_Table low
where low.guid=@guid_tmp
end
else
begin
update low
set
Aviation = tmp.Aviation
,Non_Aviation = tmp.Non_Aviation
,sas = tmp.sas
,bpo = tmp.bpo
,sub_total = tmp.sub_total
,crm = tmp.crm
,Grant_total = tmp.Grant_total
,label = tmp.label
,company_code = tmp.company_code
,currency = tmp.currency
from @Lowest_Table low inner join mis_prepared_tmp tmp
on low.head_type=tmp.head_type
--guid joing
where low.guid=@guid_tmp

end

delete from mis_prepared_tmp --where guid=@guid_tmp

insert into mis_prepared_monthly1
(
ord, head, head_type,
Aviation, Non_Aviation, sas,
bpo, sub_total, crm,
Grant_total, label, company_code,
currency, period_from, period_to,
guid
)
select h.sno, h.head, h.head_type,
sum(isnull(h.aviation,0) - isnull(l.aviation,0)) ,sum(isnull(h.Non_Aviation,0) - isnull(l.Non_Aviation,0)),sum(isnull(h.sas,0) - isnull(l.sas,0)),
sum(isnull(h.bpo,0) - isnull(l.bpo,0)) ,sum(isnull(h.sub_total,0) - isnull(l.sub_total,0)) ,sum(isnull(h.crm,0) - isnull(l.crm,0)),
sum(isnull(h.Grant_total,0) - isnull(l.Grant_total,0)),h.label,h.company_code,
h.currency, h.period_from, h.period_to,
h.guid
from @Highest_Table h inner join @Lowest_Table l
on h.head_type=l.head_type and h.guid=l.guid
where h.guid=@guid_tmp
group by h.sno,h.head,h.head_type,h.label,h.company_code,h.currency,
h.period_from,h.period_to,h.guid

update mis_prepared_monthly1
set
label =isnull(label,'MIS P&L Report For the period ' +Lower(convert(varchar,@from_date_in,106)) +' To ' +lower(convert(varchar,@to_date_in,106)))
,company_code =isnull(company_code,'ALL')
,currency =isnull(currency,'USD')
,period_from =isnull(period_from,@from_date_in)
,period_to =isnull(period_to,@To_date_in)
where guid=@guid_tmp

select
ord as 'ord_out',
head_type as 'head_type_out',
head as 'head_out',
Aviation as 'Aviation_out',
Non_Aviation as 'Non_aviation_out',
sas as 'sas_out',
bpo as 'bpo_out',
sub_total as 'sub_Total_out',
crm as 'crm_out',
Grant_total as 'Grand_total_out',
label as 'label_out',
company_code as 'company_code_out',
currency as 'currency_code_out'
from mis_prepared_monthly1(nolock)

where guid=@guid_tmp

delete from @Highest_Table
delete from @Lowest_Table
delete from mis_prepared_monthly1 where guid=@guid_tmp

set nocount off
end

quote:
Originally posted by Peso

Show FULL code?

E 12°55'05.63"
N 56°04'39.26"

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2008-09-10 : 08:24:48

One method is

insert into mis_prepared_tmp(ord,head,head_type,Aviation,Non_Aviation,sas,bpo,
sub_total,crm,Grant_total,label,company_code,currency)
exec Mis_PL_Main_sp @Company_code_in,@fb_id_in,@yr_st_date_tmp,@to_date_tmp,@Currency_Type_in,'ADMIN'

Update mis_prepared_tmp
set guid=@guid_tmp
where guid is null

Madhivanan

Failing to plan is Planning to fail

sent_sara
Constraint Violating Yak Guru

377 Posts

Posted - 2008-09-11 : 01:52:41

txs madhi ,its working fine

quote:
Originally posted by madhivanan

One method is

insert into mis_prepared_tmp(ord,head,head_type,Aviation,Non_Aviation,sas,bpo,
sub_total,crm,Grant_total,label,company_code,currency)
exec Mis_PL_Main_sp @Company_code_in,@fb_id_in,@yr_st_date_tmp,@to_date_tmp,@Currency_Type_in,'ADMIN'

Update mis_prepared_tmp
set guid=@guid_tmp
where guid is null

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources