Stored proc Case issue. - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

Stored proc Case issue.

Author

Topic

EveNut
Starting Member

5 Posts

Posted - 2007-03-30 : 07:06:02

Hello,
I am having an issue with the creation of a Stored proc and I am hoping someone might put me on the right path.

the error message I am recieving is:
Server: Msg 156, Level 15, State 1, Procedure sprocFindAgentByDivision_TEST, Line 43
Incorrect syntax near the keyword 'case'.

Create Procedure sprocFindAgentByDivision_TEST
(
@Division varchar(20),
@Corp varchar(50),
@Security float,
@Level varchar(10),
@Storyline varchar(10)
)
as
select
chrFactions.factionName'Faction', chrFactions.factionID,
mapRegions.regionName'Region', mapRegions.RegionID,
en1.itemName'Agent',crpNPCDivisions.divisionName'Divison',agtAgents.agentTypeID,
en2.ItemName'Corporation',staStations.stationName'Station',
Round(cast(mapSolarsystems.security as decimal(5,1)),1)'Security',
agtAgents.[level]'Level',agtAgents.agentID, agtAgents.quality,
sum(((agtagents.[level]*2)-2)+agtagents.quality*0.05)'Required Standing',
MapSolarsystems.solarsystemname'System',staStations.corporationid'CorpID',
mapSolarsystems.SolarsystemID'SystemID',stastations.stationID,Type = case
when agtAgentTypes.agenttype = 'NonAgent' then 'Not and Agent'
when agtAgentTypes.agenttype = 'BasicAgent' then 'Mission Agent'
when agtAgentTypes.agenttype = 'TutorialAgent' then 'Tutorial Agent'
when agtAgentTypes.agenttype = 'ResearchAgent' then 'Research Agent'
when agtAgentTypes.agenttype = 'CONCORDAgent' then 'Concord Agent'
when agtAgentTypes.agenttype = 'GenericStorylineMissionAgent' then 'Storyline Agent'
when agtAgentTypes.agentType = 'StorylineMissionAgent' then 'Storyline Mission Agent'
when agtAgentTypes.agentType = 'EventMissionAgent' then 'Event Agent'
end
from agtAgents inner join eveNames en1 on en1.itemId = agtAgents.agentID
inner join evenames en2 on en2.groupid = 2
inner join staStations on stastations.stationID = agtAgents.stationID and en2.ItemId = staStations.corporationid
inner join mapSolarsystems on mapSolarsystems.solarsystemID = staStations.solarsystemID
inner join crpNPCDivisions on crpNPCDivisions.divisionID = agtAgents.divisionID
inner Join agtAgentTypes on agtAgentTypes.agentTypeID = agtAgents.agentTypeID
inner join crpNPCCorporations on crpNPCCorporations.corporationID = staStations.corporationID
inner join chrFactions on chrFactions.factionID = crpNPCCorporations.factionID
inner join mapRegions on mapRegions.regionID = mapSolarSystems.regionID

where
crpNPCDivisions.divisionName = @Division and
en2.itemname = @Corp and mapSolarsystems.security >= @Security

-- below is the part I am having issues with.
-- I would like to be able to check the value returned from a
-- dropdown control and change part of the WHERE clause
-- based on the information passed.
-- I have tried using COALESCE for this, but I am unable to pass a
-- NULL value to the stored procedure.

case @Level
when 'Storyline' then and agtAgents.agentTypeID =6
when '1' then and agtAgents.[level] = 1
when '2' then and agtAgents.[level] = 2
when '3' then and agtAgents.[level] = 3
when '4' then and agtAgents.[level] = 4
ELSE and agtAgents.[level] between 1 and 4
end

group by agtagents.quality, agtagents.[level], agtagents.agentid,
chrFactions.factionName,mapRegions.regionName,en1.itemName,crpNPCDivisions.divisionName,
agtAgents.agentTypeID,en2.itemName,staStations.stationName,mapSolarsystems.security,
mapSolarsystems.solarSystemName,staStations.corporationID,mapSolarsystems.solarSystemID,
staStations.stationID,chrFactions.factionID,mapRegions.regionID,agtAgentTypes.agentType
order by regionname asc, [level] desc, quality desc

--Result
Server: Msg 156, Level 15, State 1, Procedure sprocFindAgentByDivision_TEST, Line 43
Incorrect syntax near the keyword 'case'.

Thanks,

TS

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-03-30 : 07:12:23

I have no idea of the logic, but something similar to this should work

AND	CASE
		WHEN @Level = 'Storyline' AND agtAgents.AgentTypeID = 6 THEN 1
		WHEN @Level IN ('1', '2', '3', '4') AND agtAgents.[Level] = @Level THEN 1
		WHEN agtAgents.[Level] BETWEEN 1 AND 4 THEN 1
		ELSE 0
	END = 1

Peter Larsson
Helsingborg, Sweden

EveNut
Starting Member

5 Posts

Posted - 2007-03-30 : 07:55:23

quote:
Originally posted by Peso

I have no idea of the logic, but something similar to this should work

Logic went out the window about 4 hours ago...

And it became Hack & Slash.

Thank you for the Rapid reply Peso.

Your code compiled, but I am not getting the desired results.

In the WHERE clause I would like to check @Level for 3 possible Value types, basicly if it equals
'Storyline' I would to have the WHERE clause end with:
"and agtAgents.agentTypeID = 6"
if it is a Number from 1 to 4 I would like to have the WHERE clause end with:
"AND agtAgents.[Level] = @Level" otherwise, I would like to have the WHERE clause end with:
"AND agtAgents.[level] between 1 and 4"

Thanks again,
TS

EDIT: Changed Typo @Storyline to @Level

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-03-30 : 08:35:52

My suggestion above does all that for you.

Peter Larsson
Helsingborg, Sweden

EveNut
Starting Member

5 Posts

Posted - 2007-03-30 : 08:57:32

yes, thank you very much Peso.

After a couple minor tweaks it is working flawlessly. :)

here is what I did.

CASE
WHEN @Level = 'Storyline' and agtAgents.AgentTypeID between 6 and 7 THEN 1
WHEN @Level = 'Research' and agtAgents.AgentTypeID = 4 THEN 1
WHEN @Level = 'Event' and agtAgents.AgentTypeID = 8 THEN 1
WHEN @Level IN ('1', '2', '3', '4') and agtAgents.[Level] = @Level THEN 1
WHEN @Level = '-' and agtAgents.[Level] BETWEEN 1 and 4 THEN 1
ELSE 0
END = 1

you are a Wizard.

TS

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-03-30 : 09:01:33

For faster processing and utilization of existing indexes, use this

AND	(
		(@Level = 'Storyline' AND agtAgents.AgentTypeID BETWEEN 6 AND 7)
	OR
		(@Level = 'Research' AND agtAgents.AgentTypeID = 4)
	OR
		(@Level = 'Event' AND agtAgents.AgentTypeID = 8)
	OR
		(@Level IN ('1', '2', '3', '4') AND agtAgents.[Level] = @Level)
	OR
		(@Level = '-' AND agtAgents.[Level] BETWEEN 1 AND 4)
	)

Peter Larsson
Helsingborg, Sweden

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources