Best approach to build select statement based on parameter?

[makeitwork09]

Hello,

I will be using Microsoft SQL Server 2005 for a stored procedure. My plan is to have a particular select statment depending on one of the values passed in from a parameter.

In essence, depending on the parameter there is one column I want to eliminate from the select statement. I was wondering what is the best way to approach that?

Please note, the select list will have about 20 items, but like mentioned above, based on the value of a parameter I want to exclude one of the list items.

Thanks in advance for the advice.

 

[simian336]

Perhaps you could show some data examples and list the 2 options and we could help you out.

Simi

 

[makeitwork09]

Yes, take this for example , if the @investors parameter is NULL it do not
want to include INVESTOR in the select list or the group by list, but if I
am given a number to state what INVESTOR list to use, I want to:
(1) include INVESTOR in the select list
(2) include INVESTOR in group by list
(3) have code block for the investors that are tied to the passed integer
and use that in the where clause (not shown below)

ALTER PROC [dbo].[spREG_DATA]
   (@accounting_date datetime,
    @consolidate char(1),
    @reportlevel char(1) = NULL,
    @investors int = NULL
    )
as
set nocount on

begin

declare @report_date smalldatetime
select @report_date = convert(datetime,(convert(varchar,@accounting_date,
112)))

     if @consolidate = 'Y'
     begin
           select
           [ACCOUNTING_DATE],
           [INVESTOR],
           [INVESTOR_TYPE],
           [LOAN],
           [LOAN_XREF],
           [LOAN_KIND],
           [LOAN_NAME],
           [BORROWER],
           [FIELD_OFFICE],
           [CLOSED_DATE],
           [MAT_DATE],
           [CURR_INT_RATE],
           [PRIM_IND],
           [PROP_CODE],
           [PROPERTY],
           SUM(ORIG_PRINCIPAL) AS [ORIG_PRINCIPAL],
           SUM(CURR_PRINCIPAL) AS [CURR_PRINCIPAL],
           SUM(CURR_BOOK_VALUE) AS [CURR_BOOK_VALUE],
           SUM(CURR_IMPAIRMENT) AS [CURR_IMPAIRMENT],
           [REPORTING_CAP_RATE]
           from reg_datamart g
           where g.accounting_date = @report_date
           group by
                  [ACCOUNTING_DATE],
           [INVESTOR],
           [INVESTOR_TYPE],
           [LOAN],
           [LOAN_XREF],
           [LOAN_KIND],
           [LOAN_NAME],
           [BORROWER],
           [FIELD_OFFICE],
           [CLOSED_DATE],
           [MAT_DATE],
           [CURR_INT_RATE],
           [PRIM_IND],
           [PROP_CODE],
           [PROPERTY],
           [REPORTING_CAP_RATE]
     end
end
return
GO

 

[simian336]

something like... untested..

ALTER PROC [dbo].[spREG_DATA]
(@accounting_date datetime,
@consolidate char(1),
@reportlevel char(1) = NULL,
@investors int = NULL)

as

set nocount on
begin
declare @report_date smalldatetime

select @report_date = convert(datetime,(convert(varchar,@accounting_date,112)))

if @consolidate = 'Y'
begin
if @investors > 0
begin
select
[ACCOUNTING_DATE],
[INVESTOR],
[INVESTOR_TYPE],
[LOAN],
[LOAN_XREF],
[LOAN_KIND],
[LOAN_NAME],
[BORROWER],
[FIELD_OFFICE],
[CLOSED_DATE],
[MAT_DATE],
[CURR_INT_RATE],
[PRIM_IND],
[PROP_CODE],
[PROPERTY],
SUM(ORIG_PRINCIPAL) AS [ORIG_PRINCIPAL],
SUM(CURR_PRINCIPAL) AS [CURR_PRINCIPAL],
SUM(CURR_BOOK_VALUE) AS [CURR_BOOK_VALUE],
SUM(CURR_IMPAIRMENT) AS [CURR_IMPAIRMENT],
[REPORTING_CAP_RATE]
from reg_datamart g
where g.accounting_date = @report_date
and [INVESTOR]=@investors
group by
[ACCOUNTING_DATE],
[INVESTOR],
[INVESTOR_TYPE],
[LOAN],
[LOAN_XREF],
[LOAN_KIND],
[LOAN_NAME],
[BORROWER],
[FIELD_OFFICE],
[CLOSED_DATE],
[MAT_DATE],
[CURR_INT_RATE],
[PRIM_IND],
[PROP_CODE],
[PROPERTY],
[REPORTING_CAP_RATE]
end
else
begin
--null
select
[ACCOUNTING_DATE],
[LOAN],
[LOAN_XREF],
[LOAN_KIND],
[LOAN_NAME],
[BORROWER],
[FIELD_OFFICE],
[CLOSED_DATE],
[MAT_DATE],
[CURR_INT_RATE],
[PRIM_IND],
[PROP_CODE],
[PROPERTY],
SUM(ORIG_PRINCIPAL) AS [ORIG_PRINCIPAL],
SUM(CURR_PRINCIPAL) AS [CURR_PRINCIPAL],
SUM(CURR_BOOK_VALUE) AS [CURR_BOOK_VALUE],
SUM(CURR_IMPAIRMENT) AS [CURR_IMPAIRMENT],
[REPORTING_CAP_RATE]
from reg_datamart g
where g.accounting_date = @report_date
group by
[ACCOUNTING_DATE],
[LOAN],
[LOAN_XREF],
[LOAN_KIND],
[LOAN_NAME],
[BORROWER],
[FIELD_OFFICE],
[CLOSED_DATE],
[MAT_DATE],
[CURR_INT_RATE],
[PRIM_IND],
[PROP_CODE],
[PROPERTY],
[REPORTING_CAP_RATE]
End

end
end
return
GO

 

[makeitwork09]

Thanks Simi - that worked. I think I was trying to think of a way to do the same thing without repeating the select and that's what was causing the brain freeze.

 

[simian336]

Well you could do it with Dynamic sql but in this case it would take you longer to do it with out any real benefits.

And would probably make it much harder to debug.

Simi

 

[jshurst]

I've gone the dynamic sql way. I built the statment in a varchar() then executed it [EXEC @blahblah]. As mentioned in the previous post, this is extremely hard to debug and I wouldn't do it again. Even if you have to repeat your sql statment I would stay away from dynamic sql. Plus you don't get the speed benefit of using an SP.

Just thought I would throw that in there...

J