Using single user entered attribute in where to match one of 4 different columns 1

[ghbeers]

I have a report that is generated from a table that looks like

id
name
etc.
FA_SCHOLARSHIP
WI_SCHOLARSHIP
SP_SCHOLARSHIP
SU_SCHOLARSHIP

Where zero or more of the 4 scholarship columns will contain "Y"

I want the user to enter FA, WI, SP OR SU into a single prompt. So if they enter "FA" I only want records to display if there's a Y in FA_SCHOLARSHIP. If the user enters "WI" I only want records to display if there's a Y in WI_SCHOLARSHIPS, and so on if SP or SU was entered.

What is the best way to handle this in my stored procedure.

 

[johnherman]

pseudocode:
if INSTR (user input) in ('FA','WI','SP','SU') then
SQL_STMT = 'select id, name, etc from <table> where ' || (user input) || '_SCHOLARSHIP' = ''Y'''
exec SQL_STMT
else <invalid value entered by user, you decide what to do>

==================================
advanced cognitive capabilities and other marketing buzzwords explained with sarcastic simplicity

 

[ghbeers]

THANK YOU!!!! Never thought of building a string to do this. Thank you so much.

 

[ghbeers]

Well I thought I could do this, but I still have a problem, because of my lack of expertise in sql server. Is there a way I can embed a string like johnherman suggest into my select without having to make the entire statement a string? I currently have the below select stmt, but the user now wants to be prompted for a 2 character term code to retrieve only the records where the column associated with that term code contains "Y". It's the very last line in the below syntax that needs to change to reflect the method johnherman suggested.

I realize that I must first convert the 2 character term code to the extension that's on the name of the column associated with the term. That's not a problem.

CREATE PROCEDURE [dbo].[XXX_LOI_BOOKSTORE_RPT]
@YR_CDE CHAR(4) = NULL,
@ID_NUM INT = NULL,
-- @TERM CHAR(4) = NULL
AS
if @YR_CDE is not NULL
SELECT
SPU.id_num,
NM.last_name,
NM.first_name,
SPU.sports_cde,
SPU.yr_cde,
SPU.trm_cde,
convert(varchar, SPU.JOB_TIME, 101) as LAST_CHANGE,
SPU.trm_bk_fall,
SPU.trm_bk_wint,
SPU.trm_bk_spring,
SPU.trm_bk_sum,
SP.SPORTS_SCHOLARSHIP

FROM sports_tracking_udf SPU

left outer JOIN name_master NM ON SPU.id_num = NM.id_num

left outer join stud_term_sum_div SD on SPU.id_num = SD.id_num
and SPU.yr_cde = SD.yr_cde
and SPU.trm_cde = SD.trm_cde

inner join SPORTS_TRACKING SP on SPU.ID_NUM = SP.ID_NUM
and SPU.yr_cde = SP.yr_cde
and SPU.trm_cde = SP.trm_cde
and SPU.SPORTS_CDE = SP.SPORTS_CDE
and SP.SPORTS_SCHOLARSHIP = 'Y'

WHERE SPU.yr_cde = @YR_CDE
AND (@ID_NUM IS NULL OR SPU.ID_NUM = @ID_NUM)

AND (SPU.TRM_BK_FALL = 'Y' OR SPU.TRM_BK_WINT = 'Y' OR SPU.TRM_BK_SPRING = 'Y' OR SPU.TRM_BK_SUM = 'Y')

 

[simian336]

declare @sql as varchar(1000)
declare @TERM as varchar(4)
declare @MyWhere as varchar(100)

set @term = 'FA'

set @sql = 'SELECT SPU.id_num, NM.last_name, NM.first_name, SPU.sports_cde,'
set @sql = @sql + ' SPU.yr_cde, SPU.trm_cde, convert(varchar, SPU.JOB_TIME, 101) as LAST_CHANGE,'
set @sql = @sql + ' SPU.trm_bk_fall, SPU.trm_bk_wint, SPU.trm_bk_spring, SPU.trm_bk_sum, SP.SPORTS_SCHOLARSHIP'
set @sql = @sql + ' FROM sports_tracking_udf SPU'
set @sql = @sql + ' left outer JOIN name_master NM'
set @sql = @sql + ' ON SPU.id_num = NM.id_num'
set @sql = @sql + ' left outer join stud_term_sum_div SD'
set @sql = @sql + ' on SPU.id_num = SD.id_num'
set @sql = @sql + ' and SPU.yr_cde = SD.yr_cde'
set @sql = @sql + ' and SPU.trm_cde = SD.trm_cde'
set @sql = @sql + ' inner join SPORTS_TRACKING SP'
set @sql = @sql + ' on SPU.ID_NUM = SP.ID_NUM'
set @sql = @sql + ' and SPU.yr_cde = SP.yr_cde'
set @sql = @sql + ' and SPU.trm_cde = SP.trm_cde'
set @sql = @sql + ' and SPU.SPORTS_CDE = SP.SPORTS_CDE'
set @sql = @sql + ' and SP.SPORTS_SCHOLARSHIP = ''Y'''
set @sql = @sql + ' WHERE SPU.yr_cde = @YR_CDE'
set @sql = @sql + ' AND (@ID_NUM IS NULL'
set @sql = @sql + ' OR SPU.ID_NUM = @ID_NUM)'

select @MyWhere =
case @TERM
WHEN 'FA'
then ' and SPU.TRM_BK_FALL = ''Y'''
WHEN 'WI'
then ' and SPU.TRM_BK_WINT = ''Y'''
WHEN 'SP'
then ' and SPU.TRM_BK_SPRING = ''Y'''
WHEN 'SU'
then ' and SPU.TRM_BK_SUM = ''Y'''
END

set @sql = @sql + @MyWhere
print @sql

 

[simian336]

Hey,

As a side note, I would encourage you to enter then tab over your ON's AND's and OR's... Like below it will make your code SO much easier to follow

Simi

if @YR_CDE is not NULL
SELECT 
SPU.id_num,
NM.last_name,
NM.first_name,
SPU.sports_cde,
SPU.yr_cde,
SPU.trm_cde,
convert(varchar, SPU.JOB_TIME, 101) as LAST_CHANGE,
SPU.trm_bk_fall,
SPU.trm_bk_wint,
SPU.trm_bk_spring,
SPU.trm_bk_sum,
SP.SPORTS_SCHOLARSHIP
FROM sports_tracking_udf SPU
left outer JOIN name_master NM 
	ON SPU.id_num = NM.id_num
left outer join stud_term_sum_div SD 
	on SPU.id_num = SD.id_num
	and SPU.yr_cde = SD.yr_cde
	and SPU.trm_cde = SD.trm_cde
inner join SPORTS_TRACKING SP 
	on SPU.ID_NUM = SP.ID_NUM
	and SPU.yr_cde = SP.yr_cde
	and SPU.trm_cde = SP.trm_cde
	and SPU.SPORTS_CDE = SP.SPORTS_CDE
	and SP.SPORTS_SCHOLARSHIP = 'Y'
WHERE SPU.yr_cde = @YR_CDE
	AND (@ID_NUM IS NULL 
		OR SPU.ID_NUM = @ID_NUM)
	AND (SPU.TRM_BK_FALL = 'Y' 
		OR SPU.TRM_BK_WINT = 'Y' 
		OR SPU.TRM_BK_SPRING = 'Y' 
		OR SPU.TRM_BK_SUM = 'Y')

 

[ghbeers]

Thank you Simi, your solution is what I implemented over the weekend. It works great. I was just hoping there was a way to do it with something dynamically built, but this method is much better. Thank you.

 

[Andrzejek]

Don't you think Simi deserves a Star?
Click on [blue]Great Post![/blue] to award one.

Have fun.

---- Andy

There is a great need for a sarcasm font.

 

[simian336]

It is old school ugly but it is not dynamic...

Simi

declare @sql as varchar(1000)
declare @TERM as varchar(4)

set @term = 'WI'

if @term = 'WI'
begin
	SELECT 	SPU.id_num,	NM.last_name, NM.first_name, SPU.sports_cde, SPU.yr_cde, SPU.trm_cde,
	convert(varchar, SPU.JOB_TIME, 101) as LAST_CHANGE,
	SPU.trm_bk_fall, SPU.trm_bk_wint, SPU.trm_bk_spring, SPU.trm_bk_sum, SP.SPORTS_SCHOLARSHIP
	FROM sports_tracking_udf SPU
	left outer JOIN name_master NM 
		ON SPU.id_num = NM.id_num
	left outer join stud_term_sum_div SD 
		on SPU.id_num = SD.id_num
		and SPU.yr_cde = SD.yr_cde
		and SPU.trm_cde = SD.trm_cde
	inner join SPORTS_TRACKING SP 
		on SPU.ID_NUM = SP.ID_NUM
		and SPU.yr_cde = SP.yr_cde
		and SPU.trm_cde = SP.trm_cde
		and SPU.SPORTS_CDE = SP.SPORTS_CDE
		and SP.SPORTS_SCHOLARSHIP = 'Y'
	WHERE SPU.yr_cde = @YR_CDE
		AND (@ID_NUM IS NULL 
			OR SPU.ID_NUM = @ID_NUM)
End

if @term = 'FA'
begin
	SELECT 	SPU.id_num,	NM.last_name, NM.first_name, SPU.sports_cde, SPU.yr_cde, SPU.trm_cde,
	convert(varchar, SPU.JOB_TIME, 101) as LAST_CHANGE,
	SPU.trm_bk_fall, SPU.trm_bk_wint, SPU.trm_bk_spring, SPU.trm_bk_sum, SP.SPORTS_SCHOLARSHIP
	FROM sports_tracking_udf SPU
	left outer JOIN name_master NM 
		ON SPU.id_num = NM.id_num
	left outer join stud_term_sum_div SD 
		on SPU.id_num = SD.id_num
		and SPU.yr_cde = SD.yr_cde
		and SPU.trm_cde = SD.trm_cde
	inner join SPORTS_TRACKING SP 
		on SPU.ID_NUM = SP.ID_NUM
		and SPU.yr_cde = SP.yr_cde
		and SPU.trm_cde = SP.trm_cde
		and SPU.SPORTS_CDE = SP.SPORTS_CDE
		and SP.SPORTS_SCHOLARSHIP = 'Y'
	WHERE SPU.yr_cde = @YR_CDE
		AND (@ID_NUM IS NULL 
			OR SPU.ID_NUM = @ID_NUM)
		AND SPU.TRM_BK_FALL = 'Y' 
End


if @term = 'SP'
begin
	SELECT 	SPU.id_num,	NM.last_name, NM.first_name, SPU.sports_cde, SPU.yr_cde, SPU.trm_cde,
	convert(varchar, SPU.JOB_TIME, 101) as LAST_CHANGE,
	SPU.trm_bk_fall, SPU.trm_bk_wint, SPU.trm_bk_spring, SPU.trm_bk_sum, SP.SPORTS_SCHOLARSHIP
	FROM sports_tracking_udf SPU
	left outer JOIN name_master NM 
		ON SPU.id_num = NM.id_num
	left outer join stud_term_sum_div SD 
		on SPU.id_num = SD.id_num
		and SPU.yr_cde = SD.yr_cde
		and SPU.trm_cde = SD.trm_cde
	inner join SPORTS_TRACKING SP 
		on SPU.ID_NUM = SP.ID_NUM
		and SPU.yr_cde = SP.yr_cde
		and SPU.trm_cde = SP.trm_cde
		and SPU.SPORTS_CDE = SP.SPORTS_CDE
		and SP.SPORTS_SCHOLARSHIP = 'Y'
	WHERE SPU.yr_cde = @YR_CDE
		AND (@ID_NUM IS NULL 
			OR SPU.ID_NUM = @ID_NUM)
		AND SPU.TRM_BK_SPRING = 'Y' 
End

if @term = 'SU'
begin
	SELECT 	SPU.id_num,	NM.last_name, NM.first_name, SPU.sports_cde, SPU.yr_cde, SPU.trm_cde,
	convert(varchar, SPU.JOB_TIME, 101) as LAST_CHANGE,
	SPU.trm_bk_fall, SPU.trm_bk_wint, SPU.trm_bk_spring, SPU.trm_bk_sum, SP.SPORTS_SCHOLARSHIP
	FROM sports_tracking_udf SPU
	left outer JOIN name_master NM 
		ON SPU.id_num = NM.id_num
	left outer join stud_term_sum_div SD 
		on SPU.id_num = SD.id_num
		and SPU.yr_cde = SD.yr_cde
		and SPU.trm_cde = SD.trm_cde
	inner join SPORTS_TRACKING SP 
		on SPU.ID_NUM = SP.ID_NUM
		and SPU.yr_cde = SP.yr_cde
		and SPU.trm_cde = SP.trm_cde
		and SPU.SPORTS_CDE = SP.SPORTS_CDE
		and SP.SPORTS_SCHOLARSHIP = 'Y'
	WHERE SPU.yr_cde = @YR_CDE
		AND (@ID_NUM IS NULL 
			OR SPU.ID_NUM = @ID_NUM)
		AND SPU.TRM_BK_SUM = 'Y' 
End

 

[mharroff]

SELECT    SPU.id_num
        , NM.last_name
        , NM.first_name
        , SPU.sports_cde
        , SPU.yr_cde
        , SPU.trm_cde
        , CONVERT(VARCHAR, SPU.JOB_TIME, 101) AS LAST_CHANGE
        , SPU.trm_bk_fall
        , SPU.trm_bk_wint
        , SPU.trm_bk_spring
        , SPU.trm_bk_sum
        , SP.SPORTS_SCHOLARSHIP
    FROM  sports_tracking_udf SPU
          LEFT OUTER JOIN name_master NM
              ON SPU.id_num = NM.id_num
          LEFT OUTER JOIN stud_term_sum_div SD
              ON SPU.id_num = SD.id_num
                 AND SPU.yr_cde = SD.yr_cde
                 AND SPU.trm_cde = SD.trm_cde
          INNER JOIN SPORTS_TRACKING SP
              ON SPU.ID_NUM = SP.ID_NUM
                 AND SPU.yr_cde = SP.yr_cde
                 AND SPU.trm_cde = SP.trm_cde
                 AND SPU.SPORTS_CDE = SP.SPORTS_CDE
                 AND SP.SPORTS_SCHOLARSHIP = 'Y'
    WHERE SPU.yr_cde = @YR_CDE
        AND (@ID_NUM IS NULL
                  OR SPU.ID_NUM = @ID_NUM)
		AND ((case @TERM WHEN 'FA' then 1 ELSE 0 END = 1 and SPU.TRM_BK_FALL = 'Y')
			OR (case @TERM WHEN 'WI' THEN 1 ELSE 0 END = 1 and SPU.TRM_BK_WINT = 'Y')
			OR (CASE @TERM WHEN 'SP' then 1 ELSE 0 END = 1 and SPU.TRM_BK_SPRING = 'Y')
			OR (CASE @TERM WHEN 'SU' THEN 1 ELSE 0 END = 1 and SPU.TRM_BK_SUM = 'Y'))