August 18, 2009 at 5:39 pm
I have a table with faculty member information. The faculty teach one of 3 types of courses. The course type is hardcoded in the faculty table. I am trying to get the first initial of each faculty member for a particular course type. This is what I have so far. I am having problems with the case select statement.
Thank you for your assistance.
CREATE PROCEDURE [dbo].[usp_faculty_letters_by_course_type] --usp_faculty_letters_by_course_type 'jumpstart'
@course_type varchar(50)
AS
BEGIN
SET NOCOUNT ON;
SELECT DISTINCT left(lastName, 1) as firstInitial, GRI, JUMPSTART, REMASTERS
INTO #tempFirstInitial
FROM faculty
WHERE FacultyID NOT in(46, 66)
ORDER BY firstInitial
SELECT firstInitial
FROM #tempFirstInitial
WHERE (CASE
WHEN @course_type = 'gri' then GRI = '1'
WHEN @course_type = 'jumpstart' then JUMPSTART = '1'
WHEN @course_type = 'remasters' then REMASTERS = '1')
END
END
August 19, 2009 at 1:15 am
you can't use the case like that in where clause
try this
CREATE PROCEDURE [dbo].[usp_faculty_letters_by_course_type] --usp_faculty_letters_by_course_type 'jumpstart'
@course_type varchar(50)
AS
BEGIN
SET NOCOUNT ON;
SELECT DISTINCT left(lastName, 1) as firstInitial, GRI, JUMPSTART, REMASTERS
INTO #tempFirstInitial
FROM faculty
WHERE FacultyID NOT in(46, 66)
ORDER BY firstInitial
----------Start check
if (@course_type = 'gri')
begin
SELECT firstInitial
FROM #tempFirstInitial
WHERE GRI = '1'
end
if (@course_type = 'jumpstart')
begin
SELECT firstInitial
FROM #tempFirstInitial
WHERE JUMPSTART = '1'
end
if (@course_type ='remasters' )
begin
SELECT firstInitial
FROM #tempFirstInitial
WHERE REMASTERS = '1'
end
END
August 19, 2009 at 2:26 am
Hi,
Also try this
create table #temp
(
slno int,
GRI int,
JUMPSTART int,
REMASTERS int
)
insert into #temp
select 11,1,2,3
union all
select 22,2,3,4
union all
select 33,3,4,5
union all
select 44,4,5,6
declare @result varchar(10)
set @result = 'GRI'
select * from #temp
where GRI = (case when @result = 'GRI' then '1'else GRI end)
and JUMPSTART = (case when @result = 'jumpstart' then '1'else jumpstart end)
and remasters = (case when @result = 'remasters' then '1'else remasters end)
August 19, 2009 at 10:27 am
Thank you very much! This is very helpful!
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply