Select case in stored procedure

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)

Select case in stored procedure

Author

Topic

Pinto
Aged Yak Warrior

590 Posts

Posted - 2005-08-26 : 09:09:53

Can anyone see what is wrong with this sp as the Case statements aren't working. There is no error message, but records returned are only selected on the Date and Room Ref

CREATE Procedure [spRB_GetBooking]

@strDateRequired datetime,
@strRoomRef nvarchar(10),
@strSelTimeSlot nvarchar (8)
as

Select * from tblRB_Bookings where
BK_DateRequired = @strDateRequired and
BK_RoomRef= @strRoomRef

SELECT CASE WHEN @strSelTimeSlot =' 1' THEN 'and BK_P1=1'
WHEN @strSelTimeSlot = '2' THEN 'and BK_P2=1'
WHEN @strSelTimeSlot =' 3' THEN 'and BK_P3=1'
WHEN @strSelTimeSlot = '4' THEN 'and BK_P4=1'
WHEN @strSelTimeSlot =' 5' THEN 'and BK_P5=1'
WHEN @strSelTimeSlot = '6' THEN 'and BK_P6=1'
WHEN @strSelTimeSlot =' 7' THEN 'and BK_P7=1'
WHEN @strSelTimeSlot = '8' THEN 'and BK_P8=1'
WHEN @strSelTimeSlot = '9' THEN 'and BK_P9=1'
WHEN @strSelTimeSlot ='10' THEN 'and BK_P10=1'
WHEN @strSelTimeSlot ='11' THEN 'and BK_P11=1'
WHEN @strSelTimeSlot ='12' THEN 'and BK_P12=1'
WHEN @strSelTimeSlot ='13' THEN 'and BK_P13=1'
WHEN @strSelTimeSlot ='14' THEN 'and BK_P14=1'
WHEN @strSelTimeSlot ='15' THEN 'and BK_P15=1'
WHEN @strSelTimeSlot ='16' THEN 'and BK_P16=1'
WHEN @strSelTimeSlot ='17' THEN 'and BK_P17=1'
WHEN @strSelTimeSlot ='18' THEN 'and BK_P18=1'
WHEN @strSelTimeSlot ='19' THEN 'and BK_P19=1'
WHEN @strSelTimeSlot ='20' THEN 'and BK_P20=1'
WHEN @strSelTimeSlot ='21' THEN 'and BK_P21=1'
WHEN @strSelTimeSlot ='22' THEN 'and BK_P22=1'
WHEN @strSelTimeSlot ='23' THEN 'and BK_P23=1'
WHEN @strSelTimeSlot ='24' THEN 'and BK_P24=1'
WHEN @strSelTimeSlot ='25' THEN 'and BK_P25=1'
WHEN @strSelTimeSlot ='26' THEN 'and BK_P26=1'
WHEN @strSelTimeSlot ='27' THEN 'and BK_P27=1'
WHEN @strSelTimeSlot ='28' THEN 'and BK_P28=1'
else 'Nothing'
END
GO

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2005-08-26 : 09:11:55

emmm...what???
what the hell are you trying to do here???
you get 2 recordsets returned...

Go with the flow & have fun! Else fight the flow

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2005-08-26 : 09:25:22

You need Dynamic SQL like this

Declare @sql varchar(2000)
Select @sql='Select * from tblRB_Bookings where
BK_DateRequired = '+cast(@strDateRequired as varchar)+ ' and
BK_RoomRef= '+@strRoomRef +
CASE WHEN @strSelTimeSlot =' 1' THEN ' and BK_P1=1'
WHEN @strSelTimeSlot =' 2' THEN ' and BK_P2=1'
.
.
.
else '' end

Exec(@sql)

Madhivanan

Failing to plan is Planning to fail

Pinto
Aged Yak Warrior

590 Posts

Posted - 2005-08-26 : 09:48:56

I have a Booking record and each BK_P1 etc represents a 30 minute time slot. My user selects a room and a date and a time slot and I want to list the record (there will only be one) meeting these criteria. I was just trying to add the last selection clause.

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2005-08-26 : 09:51:00

Did you try what I have suggested?

Madhivanan

Failing to plan is Planning to fail

Pinto
Aged Yak Warrior

590 Posts

Posted - 2005-08-26 : 10:22:45

I get the error 'must declare the variable @strDateRequired

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2005-08-26 : 10:28:29

Did you use that code inside as part of procedure?
If not, put that code as part of it

Madhivanan

Failing to plan is Planning to fail

Pinto
Aged Yak Warrior

590 Posts

Posted - 2005-08-26 : 10:33:55

I have this

CREATE PROCEDURE spRB_GetBooking1

as
Declare @sql varchar(2000)

Select @sql='Select * from tblRB_Bookings where
BK_DateRequired = '+cast(@strDateRequired as varchar)+ ' and
BK_RoomRef= '+@strRoomRef +

WHEN @strSelTimeSlot =' 1' THEN 'and BK_P1=1'
WHEN @strSelTimeSlot = '2' THEN 'and BK_P2=1'
WHEN @strSelTimeSlot =' 3' THEN 'and BK_P3=1'
WHEN @strSelTimeSlot = '4' THEN 'and BK_P4=1'
WHEN @strSelTimeSlot =' 5' THEN 'and BK_P5=1'
WHEN @strSelTimeSlot = '6' THEN 'and BK_P6=1'
WHEN @strSelTimeSlot =' 7' THEN 'and BK_P7=1'
WHEN @strSelTimeSlot = '8' THEN 'and BK_P8=1'
WHEN @strSelTimeSlot = '9' THEN 'and BK_P9=1'
WHEN @strSelTimeSlot ='10' THEN 'and BK_P10=1'
WHEN @strSelTimeSlot ='11' THEN 'and BK_P11=1'
WHEN @strSelTimeSlot ='12' THEN 'and BK_P12=1'
WHEN @strSelTimeSlot ='13' THEN 'and BK_P13=1'
WHEN @strSelTimeSlot ='14' THEN 'and BK_P14=1'
WHEN @strSelTimeSlot ='15' THEN 'and BK_P15=1'
WHEN @strSelTimeSlot ='16' THEN 'and BK_P16=1'
WHEN @strSelTimeSlot ='17' THEN 'and BK_P17=1'
WHEN @strSelTimeSlot ='18' THEN 'and BK_P18=1'
WHEN @strSelTimeSlot ='19' THEN 'and BK_P19=1'
WHEN @strSelTimeSlot ='20' THEN 'and BK_P20=1'
WHEN @strSelTimeSlot ='21' THEN 'and BK_P21=1'
WHEN @strSelTimeSlot ='22' THEN 'and BK_P22=1'
WHEN @strSelTimeSlot ='23' THEN 'and BK_P23=1'
WHEN @strSelTimeSlot ='24' THEN 'and BK_P24=1'
WHEN @strSelTimeSlot ='25' THEN 'and BK_P25=1'
WHEN @strSelTimeSlot ='26' THEN 'and BK_P26=1'
WHEN @strSelTimeSlot ='27' THEN 'and BK_P27=1'
WHEN @strSelTimeSlot ='28' THEN 'and BK_P28=1'

else '' end

Exec(@sql)

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2005-08-26 : 10:45:14

Why did not you have other variables as parameter?

CREATE Procedure [spRB_GetBooking]

@strDateRequired datetime,
@strRoomRef nvarchar(10),
@strSelTimeSlot nvarchar (8)
as

Declare @sql varchar(2000)

Select @sql='Select * from tblRB_Bookings where
BK_DateRequired = '+cast(@strDateRequired as varchar)+ ' and
BK_RoomRef= '+@strRoomRef + CASE

WHEN @strSelTimeSlot =' 1' THEN ' and BK_P1=1'
WHEN @strSelTimeSlot = '2' THEN ' and BK_P2=1'
WHEN @strSelTimeSlot =' 3' THEN ' and BK_P3=1'
WHEN @strSelTimeSlot = '4' THEN ' and BK_P4=1'
WHEN @strSelTimeSlot =' 5' THEN ' and BK_P5=1'
WHEN @strSelTimeSlot = '6' THEN ' and BK_P6=1'
WHEN @strSelTimeSlot =' 7' THEN ' and BK_P7=1'
WHEN @strSelTimeSlot = '8' THEN ' and BK_P8=1'
WHEN @strSelTimeSlot = '9' THEN ' and BK_P9=1'
WHEN @strSelTimeSlot ='10' THEN ' and BK_P10=1'
WHEN @strSelTimeSlot ='11' THEN ' and BK_P11=1'
WHEN @strSelTimeSlot ='12' THEN ' and BK_P12=1'
WHEN @strSelTimeSlot ='13' THEN ' and BK_P13=1'
WHEN @strSelTimeSlot ='14' THEN ' and BK_P14=1'
WHEN @strSelTimeSlot ='15' THEN ' and BK_P15=1'
WHEN @strSelTimeSlot ='16' THEN ' and BK_P16=1'
WHEN @strSelTimeSlot ='17' THEN ' and BK_P17=1'
WHEN @strSelTimeSlot ='18' THEN ' and BK_P18=1'
WHEN @strSelTimeSlot ='19' THEN ' and BK_P19=1'
WHEN @strSelTimeSlot ='20' THEN ' and BK_P20=1'
WHEN @strSelTimeSlot ='21' THEN ' and BK_P21=1'
WHEN @strSelTimeSlot ='22' THEN ' and BK_P22=1'
WHEN @strSelTimeSlot ='23' THEN ' and BK_P23=1'
WHEN @strSelTimeSlot ='24' THEN ' and BK_P24=1'
WHEN @strSelTimeSlot ='25' THEN ' and BK_P25=1'
WHEN @strSelTimeSlot ='26' THEN ' and BK_P26=1'
WHEN @strSelTimeSlot ='27' THEN ' and BK_P27=1'
WHEN @strSelTimeSlot ='28' THEN ' and BK_P28=1'

else '' end

Exec(@sql)

and keep in mind that there is space between single quote and and
WHEN @strSelTimeSlot =' 1' THEN ' and BK_P1=1'

Madhivanan

Failing to plan is Planning to fail

Pinto
Aged Yak Warrior

590 Posts

Posted - 2005-08-26 : 10:59:45

I am now getting the error
Line 2: Incorrect syntax near '31'

I have copied and pasted your code. Thank you for all your help.

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2005-08-26 : 11:02:14

Did you execute it by supplying valid values?

Madhivanan

Failing to plan is Planning to fail

Pinto
Aged Yak Warrior

590 Posts

Posted - 2005-08-26 : 11:09:16

Yes, I stepped through my asp code and could see what values were being passed.

Pinto
Aged Yak Warrior

590 Posts

Posted - 2005-08-26 : 11:19:52

I am very sorry, but I am going to have to leave this as I have to go home and am on holiday for a week ! I expect it is a silly error in the end. I will mark this and return to it when I come back - what a start to a holiday ! Thanks you once again for all your help.

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2005-08-29 : 01:00:08

Find out which line you get this error when debugging

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources