Testing sp in Analyser - 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)

Testing sp in Analyser

Author

Topic

Pinto
Aged Yak Warrior

590 Posts

Posted - 2005-12-08 : 05:11:35

I am trying to run a sp in Query Analyser as below. However, I do not think I am passing the date parameter correctly as although it says the command completed successfully, it returns no records.

exec "spRB_GetAvailableRooms" '12/08/2005','0BM2','County Hall', 'ThisMonth'

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2005-12-08 : 05:16:28

The syntax looks correct and may be there are no records to match those values

Madhivanan

Failing to plan is Planning to fail

Pinto
Aged Yak Warrior

590 Posts

Posted - 2005-12-08 : 05:21:39

There are records because if I fill in the criteria in my view I get them to list. Maybe it's my sp....

CREATE Procedure [spRB_GetAvailableRooms]

@strDateRequired datetime,
@strRoomRef nvarchar(10),
@strListOption nvarchar(12),
@strLocation nvarchar(100)

as

if @strListOption ='No'

BEGIN
Select *
from vweAvailableRooms where
BD_RoomRef like '%'+@strRoomRef+'%' and
RM_Location like '%' +@strLocation +'%' and
BD_DateRequired =@strDateRequired
ORDER BY BD_RoomRef, BD_DateRequired asc
END

if @strListOption ='ThisMonth'

BEGIN
Select *
from vweAvailableRooms where
BD_RoomRef like '%'+@strRoomRef+'%' and
RM_Location like '%'+@strLocation+'%' and
Month(BD_DateRequired)= Month(@strDateRequired)
ORDER BY BD_RoomRef, BD_DateRequired asc
END
GO

Pinto
Aged Yak Warrior

590 Posts

Posted - 2005-12-08 : 06:56:03

I am passing the value "" to my sp as in Me.Session("RoomRef") = "" (This is the option that doesn't work all other options do) I have also tried me.Session("RoomRef") = "''"

Kristen
Test

22859 Posts

Posted - 2005-12-08 : 13:06:44

"CREATE Procedure [spRB_GetAvailableRooms]

@strDateRequired datetime,
@strRoomRef nvarchar(10),
@strListOption nvarchar(12),
@strLocation nvarchar(100)

as

...

exec "spRB_GetAvailableRooms" '12/08/2005','0BM2','County Hall', 'ThisMonth'"

You've got the last two parameters the wrong way round. Much better to explicitly name them:


exec "spRB_GetAvailableRooms" 
    @strDateRequired = '12/08/2005',
    @strRoomRef = '0BM2',
    @strLocation = 'County Hall', 
    @strListOption = 'ThisMonth'

then the order doesn't matter! Some error checking in the SProc that the @strListOption was invalid would be a good idea too

Kristen

Pinto
Aged Yak Warrior

590 Posts

Posted - 2005-12-09 : 05:59:21

Thank you.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources