SELECT vs Select. . .when is sql stmt executed

[jcaulder]

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!

 

[jcaulder]

I think I've figured out my own question. The SELECT and Select are the same keyword since SQL Server is not case sensitive.

SELECT @c_db_ID=@i_dbid

is simple forcing an assignment it appears.

And

Select @c_db_id_char=convert(char(6),@c_db_id)

is simply assigning the conversion of the variable to another variable just like the SELECT above it.

The line

Select @string=' and CORP.CORP_ID. . .'

is assigning a this string to @string. It is starting in the middle with an ' and. . .' because the @string is later appended in the middle of the @string1 variable and does become part of the larger sql statement. The entire statement built ends up in @string1 and is executed at the end using the

exec(@string1)

which is actually the concatenation of @string1+@string.

If anyone thinks I'm off track, please correct me.

Thanks!

 

[swampBoogie]

There is no difference between Select and SELECT. Keywords in SQL are case insensitive.

Diffent Tsql statements

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_ca-co_5t9v.asp

using

select @string1 = @string1 + 'and ...'

is just an assignment of a local variable. In later versions the command

set @string1 = @string1 + 'and ...'

can be used. This is more in line with ANSI/PSM syntax.

if you just do a

select * from t

in a stored procedure that will return a result set to the client. In your case the exec(@string1) will return the result of the select statement in @string1.

You can add a

print @string1

to see what value @string1 has.

 

[dhqube]

Using the exec(@sql) syntax in a stored procedure increases its flexibility.

It allows you to build up a select string based on parameters passed ( or obtained by prior sql queries)

For a good ( if rather pointless, business wise) example you can pass the name of a specific field and table as parameters to the sp, so that the sp can remain totally generic

eg.

create procedure sp_getanyfieldfromanytable
@fieldname varchar(100),
@tablename varchar(100)

as

declare @sql as varchar(500)
select @sql = 'SELECT ' + @fieldname
select @sql = rtrim(@sql) + ' from ' + @tablename

exec ( @sql)

 

[jcaulder]

Thanks for the help! I understand it fully now and have figured out the problem with the stored procedure. The original writer joined two tables on a condition of table1.field1 = table1.field1(all records match!) which resulted in the huge number of records. Once I learned a little about the sp, I was able to do the print (@string1) and quickly found the error. The final sql is 5 long sql statements unioned together which made it difficult to follow just reading the sp. Thanks for the posts!