I have a stored procedure and am trying to understand what it is doing(I didn't write it and I'm new to SQL Server). An excerpt from the sp follows:
create proc dbo.up_rpt_DepartExpDetail
(@p_corp_id varchar(255),
@p_cc_id varchar (255),
@p_exp_code_id varchar (255),
@p_start_date varchar(255),
@p_end_date varchar(255),
@i_dbid int
)
as
begin
set nocount on
Declare @c_DB_ID int
Declare @c_DB_ID_char char(6)
Declare @string varchar (500)
Declare @string1 varchar(8000)
/* *************************************************** */
If @i_dbid = 0
Begin
SET ROWCOUNT 1
Select @c_DB_ID = DB_ID
from DB_ID
where DB_ID is not null and
DB_ID <> 0
SET ROWCOUNT 0
End
Else
SELECT @c_DB_ID = @i_dbid
Select @c_DB_ID_char = convert(char(6),@c_DB_ID)
/* *************************************************** */
Select @string = ' and CORP.CORP_ID in ' + @p_corp_id + ' and CORP.CORP_IDB = ' + @c_DB_ID_char
if @p_cc_id <> '' and @p_cc_id is not null
Select @string = @string + ' and CC.CC_ID in ' + @p_cc_id
if @p_exp_code_id <> '' and @p_exp_code_id is not null
Select @string = @string + ' and EXP_CODE.EXP_CODE_ID in ' + @p_exp_code_id
-- Transaction Type: Stocked
Select @string1 = 'SELECT
CORP.ACCT_NO CORP_ACCT_NO,
CORP.NAME CORP_NAME,
CC.ACCT_NO CC_ACCT_NO,
CC.NAME CC_NAME,
EXP_CODE.ACCT_NO EXP_CODE_ACCT_NO,
SUB_ACCT.ACCT_NO SUB_ACCT_NO,
SUB_ACCT.NAME SUB_ACCT_NAME,
SUB_ACCT.ACCT_FMT SUB_ACCT_FMT,
ITEM.ITEM_NO ITEM_NO,
ITEM.DESCR ITEM_DESCR,
SLOC_ITEM_TRANS.QTY QTY,
SLOC_ITEM_TRANS.UM_CD UM_CD,
SLOC_ITEM_TRANS.PRICE UNIT_COST,
''S'' TRAN_TYPE,
SLOC_ITEM_TRANS.QTY*SLOC_ITEM_TRANS.PRICE EXTENDED_COST,
null TAX_AMT
FROM SLOC_ITEM_TRANS
join ITEM on (SLOC_ITEM_TRANS.ITEM_ID = ITEM.ITEM_ID and
SLOC_ITEM_TRANS.ITEM_IDB = ITEM.ITEM_IDB)
join SUB_ACCT on (SLOC_ITEM_TRANS.TO_SUB_ACCT_ID = SUB_ACCT.SUB_ACCT_ID and
SLOC_ITEM_TRANS.TO_SUB_ACCT_IDB = SUB_ACCT.SUB_ACCT_IDB)
join EXP_CODE on (SUB_ACCT.EXP_CODE_ID = EXP_CODE.EXP_CODE_ID and
SUB_ACCT.EXP_CODE_IDB = EXP_CODE.EXP_CODE_IDB)
join CC on (EXP_CODE.CC_ID = CC.CC_ID and
EXP_CODE.CC_IDB = CC.CC_IDB)
join CORP on (CC.CORP_ID = CORP.CORP_ID and
CC.CORP_IDB = CORP.CORP_IDB)
where SLOC_ITEM_TRANS.SLOC_ITEM_TRANS_TYPE in (1000,1100,1110,1111,1200) and
SLOC_ITEM_TRANS.REC_CREATE_DATE >= ''' + @p_start_date + ''' and
SLOC_ITEM_TRANS.REC_CREATE_DATE <= ''' + @p_end_date + ''''
-- Transaction Type: Adjustments
Select @string1 = @string1 + @string +
' union Select
CORP.ACCT_NO CORP_ACCT_NO,
CORP.NAME CORP_NAME,
CC.ACCT_NO CC_ACCT_NO,
CC.NAME CC_NAME,
EXP_CODE.ACCT_NO EXP_CODE_ACCT_NO,
SUB_ACCT.ACCT_NO SUB_ACCT_NO,
SUB_ACCT.NAME SUB_ACCT_NAME,
SUB_ACCT.ACCT_FMT SUB_ACCT_FMT,
ITEM.ITEM_NO ITEM_NO,
ITEM.DESCR ITEM_DESCR,
SLOC_ITEM_TRANS.TO_QTY QTY,
SLOC_ITEM_TRANS.TO_UM_CD UM_CD,
SLOC_ITEM_TRANS.TO_PRICE UNIT_COST,
''A'' TRAN_TYPE,
SLOC_ITEM_TRANS.TO_PRICE * SLOC_ITEM_TRANS.TO_QTY - SLOC_ITEM_TRANS.PRICE * SLOC_ITEM_TRANS.QTY EXTENDED_COST,
null TAX_AMT
FROM SLOC_ITEM_TRANS
join ITEM on (SLOC_ITEM_TRANS.ITEM_ID = ITEM.ITEM_ID and
SLOC_ITEM_TRANS.ITEM_IDB = ITEM.ITEM_IDB)
join SUB_ACCT on (SLOC_ITEM_TRANS.TO_SUB_ACCT_ID = SUB_ACCT.SUB_ACCT_ID and
SLOC_ITEM_TRANS.TO_SUB_ACCT_IDB = SUB_ACCT.SUB_ACCT_IDB)
join EXP_CODE on (SUB_ACCT.EXP_CODE_ID = EXP_CODE.EXP_CODE_ID and
SUB_ACCT.EXP_CODE_IDB = EXP_CODE.EXP_CODE_IDB)
join CC on (EXP_CODE.CC_ID = CC.CC_ID and
EXP_CODE.CC_IDB = CC.CC_IDB)
join CORP on (CC.CORP_ID = CORP.CORP_ID and
CC.CORP_IDB = CORP.CORP_IDB)
where SLOC_ITEM_TRANS.SLOC_ITEM_TRANS_TYPE in (3000,3001,3110,3200,3220,3300,3400,3210) and
SLOC_ITEM_TRANS.REC_CREATE_DATE >= ''' + @p_start_date + ''' and
SLOC_ITEM_TRANS.REC_CREATE_DATE <= ''' + @p_end_date + ''''
-- Transaction Type: Adjustment of Inventory Account - additional rows
Select @string1 = @string1 + @string +
' union Select
CORP.ACCT_NO CORP_ACCT_NO,
CORP.NAME CORP_NAME,
CC.ACCT_NO CC_ACCT_NO,
CC.NAME CC_NAME,
EXP_CODE.ACCT_NO EXP_CODE_ACCT_NO,
SUB_ACCT.ACCT_NO SUB_ACCT_NO,
SUB_ACCT.NAME SUB_ACCT_NAME,
SUB_ACCT.ACCT_FMT SUB_ACCT_FMT,
ITEM.ITEM_NO ITEM_NO,
ITEM.DESCR ITEM_DESCR,
SLOC_ITEM_TRANS.TO_QTY QTY,
SLOC_ITEM_TRANS.TO_UM_CD UM_CD,
SLOC_ITEM_TRANS.TO_PRICE UNIT_COST,
''A'' TRAN_TYPE,
SLOC_ITEM_TRANS.TO_PRICE * SLOC_ITEM_TRANS.TO_QTY * (-1) EXTENDED_COST,
null TAX_AMT
FROM SLOC_ITEM_TRANS
join ITEM on (SLOC_ITEM_TRANS.ITEM_ID = ITEM.ITEM_ID and
SLOC_ITEM_TRANS.ITEM_IDB = ITEM.ITEM_IDB)
join SUB_ACCT on (SLOC_ITEM_TRANS.SUB_ACCT_ID = SUB_ACCT.SUB_ACCT_ID and
SLOC_ITEM_TRANS.SUB_ACCT_IDB = SUB_ACCT.SUB_ACCT_IDB)
join EXP_CODE on (SUB_ACCT.EXP_CODE_ID = EXP_CODE.EXP_CODE_ID and
SUB_ACCT.EXP_CODE_IDB = EXP_CODE.EXP_CODE_IDB)
join CC on (EXP_CODE.CC_ID = CC.CC_ID and
EXP_CODE.CC_IDB = CC.CC_IDB)
join CORP on (CC.CORP_ID = CORP.CORP_ID and
CC.CORP_IDB = CORP.CORP_IDB)
where SLOC_ITEM_TRANS.SLOC_ITEM_TRANS_TYPE = 3210 and
SLOC_ITEM_TRANS.REC_CREATE_DATE >= ''' + @p_start_date + ''' and
SLOC_ITEM_TRANS.REC_CREATE_DATE <= ''' + @p_end_date + ''''
-- Transaction Type: Receipt Accrual
Select @string1 = @string1 + @string +
'union Select
a.CORP_ACCT_NO CORP_ACCT_NO,
a.CORP_NAME CORP_NAME,
a.CC_ACCT_NO CC_ACCT_NO,
a.CC_NAME CC_NAME,
a.EXP_CODE_ACCT_NO EXP_CODE_ACCT_NO,
.
.
.
Part of what I'm trying to understand is the SELECT vs Select in the first conditional loop near the top of the sp and other places throughout the sp. As you can see from the code following those two 'select' statements, it then goes on do another Select but appears to start with an ' and CORP. . .' as if part of the select statement is already built.
The final lines of the sp are:
exec(@string1)
set nocount off
end
GO
From looking at it, it appears to me to be building one sql statement that is then executed at the end using the 'exec(@string1)' command. Can anyone give me an overview or link me to a site that can provide more information about these statements. For example, when does a sql statement actually get executed in the sp? Is it creating mulitple resultsets or just one? Sorry for the long post but this is only about a third of the sp and I felt it was necessary to give enough information about the nature of the sp. The entire sp is returning a resultset that the users say is too large but I'm not even sure what is being executed from my limited knowledge or SQL Server.
The SELECT vs Select seems very confusing.
Thanks in advance for any info!
create proc dbo.up_rpt_DepartExpDetail
(@p_corp_id varchar(255),
@p_cc_id varchar (255),
@p_exp_code_id varchar (255),
@p_start_date varchar(255),
@p_end_date varchar(255),
@i_dbid int
)
as
begin
set nocount on
Declare @c_DB_ID int
Declare @c_DB_ID_char char(6)
Declare @string varchar (500)
Declare @string1 varchar(8000)
/* *************************************************** */
If @i_dbid = 0
Begin
SET ROWCOUNT 1
Select @c_DB_ID = DB_ID
from DB_ID
where DB_ID is not null and
DB_ID <> 0
SET ROWCOUNT 0
End
Else
SELECT @c_DB_ID = @i_dbid
Select @c_DB_ID_char = convert(char(6),@c_DB_ID)
/* *************************************************** */
Select @string = ' and CORP.CORP_ID in ' + @p_corp_id + ' and CORP.CORP_IDB = ' + @c_DB_ID_char
if @p_cc_id <> '' and @p_cc_id is not null
Select @string = @string + ' and CC.CC_ID in ' + @p_cc_id
if @p_exp_code_id <> '' and @p_exp_code_id is not null
Select @string = @string + ' and EXP_CODE.EXP_CODE_ID in ' + @p_exp_code_id
-- Transaction Type: Stocked
Select @string1 = 'SELECT
CORP.ACCT_NO CORP_ACCT_NO,
CORP.NAME CORP_NAME,
CC.ACCT_NO CC_ACCT_NO,
CC.NAME CC_NAME,
EXP_CODE.ACCT_NO EXP_CODE_ACCT_NO,
SUB_ACCT.ACCT_NO SUB_ACCT_NO,
SUB_ACCT.NAME SUB_ACCT_NAME,
SUB_ACCT.ACCT_FMT SUB_ACCT_FMT,
ITEM.ITEM_NO ITEM_NO,
ITEM.DESCR ITEM_DESCR,
SLOC_ITEM_TRANS.QTY QTY,
SLOC_ITEM_TRANS.UM_CD UM_CD,
SLOC_ITEM_TRANS.PRICE UNIT_COST,
''S'' TRAN_TYPE,
SLOC_ITEM_TRANS.QTY*SLOC_ITEM_TRANS.PRICE EXTENDED_COST,
null TAX_AMT
FROM SLOC_ITEM_TRANS
join ITEM on (SLOC_ITEM_TRANS.ITEM_ID = ITEM.ITEM_ID and
SLOC_ITEM_TRANS.ITEM_IDB = ITEM.ITEM_IDB)
join SUB_ACCT on (SLOC_ITEM_TRANS.TO_SUB_ACCT_ID = SUB_ACCT.SUB_ACCT_ID and
SLOC_ITEM_TRANS.TO_SUB_ACCT_IDB = SUB_ACCT.SUB_ACCT_IDB)
join EXP_CODE on (SUB_ACCT.EXP_CODE_ID = EXP_CODE.EXP_CODE_ID and
SUB_ACCT.EXP_CODE_IDB = EXP_CODE.EXP_CODE_IDB)
join CC on (EXP_CODE.CC_ID = CC.CC_ID and
EXP_CODE.CC_IDB = CC.CC_IDB)
join CORP on (CC.CORP_ID = CORP.CORP_ID and
CC.CORP_IDB = CORP.CORP_IDB)
where SLOC_ITEM_TRANS.SLOC_ITEM_TRANS_TYPE in (1000,1100,1110,1111,1200) and
SLOC_ITEM_TRANS.REC_CREATE_DATE >= ''' + @p_start_date + ''' and
SLOC_ITEM_TRANS.REC_CREATE_DATE <= ''' + @p_end_date + ''''
-- Transaction Type: Adjustments
Select @string1 = @string1 + @string +
' union Select
CORP.ACCT_NO CORP_ACCT_NO,
CORP.NAME CORP_NAME,
CC.ACCT_NO CC_ACCT_NO,
CC.NAME CC_NAME,
EXP_CODE.ACCT_NO EXP_CODE_ACCT_NO,
SUB_ACCT.ACCT_NO SUB_ACCT_NO,
SUB_ACCT.NAME SUB_ACCT_NAME,
SUB_ACCT.ACCT_FMT SUB_ACCT_FMT,
ITEM.ITEM_NO ITEM_NO,
ITEM.DESCR ITEM_DESCR,
SLOC_ITEM_TRANS.TO_QTY QTY,
SLOC_ITEM_TRANS.TO_UM_CD UM_CD,
SLOC_ITEM_TRANS.TO_PRICE UNIT_COST,
''A'' TRAN_TYPE,
SLOC_ITEM_TRANS.TO_PRICE * SLOC_ITEM_TRANS.TO_QTY - SLOC_ITEM_TRANS.PRICE * SLOC_ITEM_TRANS.QTY EXTENDED_COST,
null TAX_AMT
FROM SLOC_ITEM_TRANS
join ITEM on (SLOC_ITEM_TRANS.ITEM_ID = ITEM.ITEM_ID and
SLOC_ITEM_TRANS.ITEM_IDB = ITEM.ITEM_IDB)
join SUB_ACCT on (SLOC_ITEM_TRANS.TO_SUB_ACCT_ID = SUB_ACCT.SUB_ACCT_ID and
SLOC_ITEM_TRANS.TO_SUB_ACCT_IDB = SUB_ACCT.SUB_ACCT_IDB)
join EXP_CODE on (SUB_ACCT.EXP_CODE_ID = EXP_CODE.EXP_CODE_ID and
SUB_ACCT.EXP_CODE_IDB = EXP_CODE.EXP_CODE_IDB)
join CC on (EXP_CODE.CC_ID = CC.CC_ID and
EXP_CODE.CC_IDB = CC.CC_IDB)
join CORP on (CC.CORP_ID = CORP.CORP_ID and
CC.CORP_IDB = CORP.CORP_IDB)
where SLOC_ITEM_TRANS.SLOC_ITEM_TRANS_TYPE in (3000,3001,3110,3200,3220,3300,3400,3210) and
SLOC_ITEM_TRANS.REC_CREATE_DATE >= ''' + @p_start_date + ''' and
SLOC_ITEM_TRANS.REC_CREATE_DATE <= ''' + @p_end_date + ''''
-- Transaction Type: Adjustment of Inventory Account - additional rows
Select @string1 = @string1 + @string +
' union Select
CORP.ACCT_NO CORP_ACCT_NO,
CORP.NAME CORP_NAME,
CC.ACCT_NO CC_ACCT_NO,
CC.NAME CC_NAME,
EXP_CODE.ACCT_NO EXP_CODE_ACCT_NO,
SUB_ACCT.ACCT_NO SUB_ACCT_NO,
SUB_ACCT.NAME SUB_ACCT_NAME,
SUB_ACCT.ACCT_FMT SUB_ACCT_FMT,
ITEM.ITEM_NO ITEM_NO,
ITEM.DESCR ITEM_DESCR,
SLOC_ITEM_TRANS.TO_QTY QTY,
SLOC_ITEM_TRANS.TO_UM_CD UM_CD,
SLOC_ITEM_TRANS.TO_PRICE UNIT_COST,
''A'' TRAN_TYPE,
SLOC_ITEM_TRANS.TO_PRICE * SLOC_ITEM_TRANS.TO_QTY * (-1) EXTENDED_COST,
null TAX_AMT
FROM SLOC_ITEM_TRANS
join ITEM on (SLOC_ITEM_TRANS.ITEM_ID = ITEM.ITEM_ID and
SLOC_ITEM_TRANS.ITEM_IDB = ITEM.ITEM_IDB)
join SUB_ACCT on (SLOC_ITEM_TRANS.SUB_ACCT_ID = SUB_ACCT.SUB_ACCT_ID and
SLOC_ITEM_TRANS.SUB_ACCT_IDB = SUB_ACCT.SUB_ACCT_IDB)
join EXP_CODE on (SUB_ACCT.EXP_CODE_ID = EXP_CODE.EXP_CODE_ID and
SUB_ACCT.EXP_CODE_IDB = EXP_CODE.EXP_CODE_IDB)
join CC on (EXP_CODE.CC_ID = CC.CC_ID and
EXP_CODE.CC_IDB = CC.CC_IDB)
join CORP on (CC.CORP_ID = CORP.CORP_ID and
CC.CORP_IDB = CORP.CORP_IDB)
where SLOC_ITEM_TRANS.SLOC_ITEM_TRANS_TYPE = 3210 and
SLOC_ITEM_TRANS.REC_CREATE_DATE >= ''' + @p_start_date + ''' and
SLOC_ITEM_TRANS.REC_CREATE_DATE <= ''' + @p_end_date + ''''
-- Transaction Type: Receipt Accrual
Select @string1 = @string1 + @string +
'union Select
a.CORP_ACCT_NO CORP_ACCT_NO,
a.CORP_NAME CORP_NAME,
a.CC_ACCT_NO CC_ACCT_NO,
a.CC_NAME CC_NAME,
a.EXP_CODE_ACCT_NO EXP_CODE_ACCT_NO,
.
.
.
Part of what I'm trying to understand is the SELECT vs Select in the first conditional loop near the top of the sp and other places throughout the sp. As you can see from the code following those two 'select' statements, it then goes on do another Select but appears to start with an ' and CORP. . .' as if part of the select statement is already built.
The final lines of the sp are:
exec(@string1)
set nocount off
end
GO
From looking at it, it appears to me to be building one sql statement that is then executed at the end using the 'exec(@string1)' command. Can anyone give me an overview or link me to a site that can provide more information about these statements. For example, when does a sql statement actually get executed in the sp? Is it creating mulitple resultsets or just one? Sorry for the long post but this is only about a third of the sp and I felt it was necessary to give enough information about the nature of the sp. The entire sp is returning a resultset that the users say is too large but I'm not even sure what is being executed from my limited knowledge or SQL Server.
The SELECT vs Select seems very confusing.
Thanks in advance for any info!