Stored procedure - 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 procedure

Author

Topic

Pinto
Aged Yak Warrior

590 Posts

Posted - 2005-12-13 : 04:58:24

I'm starting all over again with my previous problem. This is my sp which doesn't work as I'm using IN which I believe you can only use with a dynamic sp. Can anyone help me convert this sp to a dynamic one please.

CREATE Procedure [spRB_GetAvailableRooms2]

@strDateRequired datetime,
@strListOption nvarchar(12),
@strRoomList nvarchar (2000),
@strRoomref nvarchar (10)

as

if @strListOption ='No'

BEGIN
Select *
from vweAvailableRooms where
BD_RoomRef =@strRoomRef and
BD_DateRequired =@strDateRequired
ORDER BY BD_RoomRef, BD_DateRequired asc
END

if @strListOption ='ThisMonth'

BEGIN
Select *
from vweAvailableRooms where
BD_RoomRef IN (''' + Replace(@strRoomList,',',''',''') + ''') and
Month(BD_DateRequired)= Month(@strDateRequired)
ORDER BY BD_RoomRef, BD_DateRequired asc
END
GO

surendrakalekar
Posting Yak Master

120 Posts

Posted - 2005-12-13 : 05:55:24

quote:
Originally posted by Pinto
[brThis is my sp which doesn't work as I'm using IN ...

What is the problem?
Whether it iw throwing any error or not showing any records?

Surendra

Pinto
Aged Yak Warrior

590 Posts

Posted - 2005-12-13 : 06:13:21

It isn't showing any records.

shallu1_gupta
Constraint Violating Yak Guru

394 Posts

Posted - 2005-12-13 : 06:17:43

Hi,
U can insert ur comma separated string into a table variable and use that in ur IN clause

Kristen
Test

22859 Posts

Posted - 2005-12-13 : 06:21:12

See http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=55210&SearchTerms=WHERE+IN+@MyCSV

Kristen

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2005-12-13 : 06:23:28

I already answered
Why was there no reply from you?

http://sqlteam.com/forums/topic.asp?TOPIC_ID=58867

Madhivanan

Failing to plan is Planning to fail

Pinto
Aged Yak Warrior

590 Posts

Posted - 2005-12-13 : 09:10:40

I solved it like this, but not in an sp.

'This builds a string of the rooms that this user is allowed to view from the Room listbox

If Me.LBRooms.SelectedIndex = 0 Then
For n = 1 To Me.LBRooms.Items.Count - 1
strRoomList = strRoomList & "'" & Me.LBRooms.Items(n).Text & "',"
Next n
strRoomList = strRoomList.Substring(0, strRoomList.Length() - 1)
Me.Session("RoomRefLogin") = strRoomList
Else
Me.Session("RoomRefLogin") = Me.LBRooms.SelectedValue
End If

'This gets the records

Select Case Me.Session("ListOption")
Case "No"
mySQLstr = "SELECT * from vweAvailableRooms where BD_RoomRef IN (" & Me.Session("RoomRefLogin") & ")" _
& "and BD_DateRequired = '" & Me.Session("DateRequiredLogin") & "' ORDER BY BD_RoomRef Asc"
Case "ThisMonth"
mySQLstr = "SELECT * from vweAvailableRooms where BD_RoomRef IN (" & Me.Session("RoomRefLogin") & ")" _
& "and Month(BD_DateRequired) = '" & Month(Me.Session("DateRequiredLogin")) & "' ORDER BY BD_RoomRef, BD_DateRequired Asc"
End Select

Thanks everyone for your help

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources