Pass array to SP - 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)

Pass array to SP

Author

Topic

Pinto
Aged Yak Warrior

590 Posts

Posted - 2005-12-09 : 05:57:42

I am building my string like this in my web page.

Dim n As Integer
Dim RoomList As String
For n = 1 To Me.LBRooms.Items.Count - 1
If Me.LBRooms.Items(n).Selected = True Then
RoomList = RoomList & Me.LBRooms.Items(n).Text & ","
End If
Next
RoomList = RoomList.Substring(0, RoomList.Length() - 1)
Me.Session("RoomList") = RoomList

However when I pass it to my stored procedure I get the error 'Invalid Column name'OBM1' Invalid Column name ' 0DM2' etc for each item in my list. I have a feeling it is because they are strings and my sp example dealt with numbers. What is wrong with my syntax ? TIA

CREATE PROC [spRB_GetAvailableRooms1]
(
@strRoomList varchar(500)
)
AS
BEGIN
SET NOCOUNT ON

DECLARE @SQL varchar(600)

SET @SQL =
'SELECT * from vweAvailableRooms
WHERE BD_RoomRef IN (' + @strRoomList + ')'

EXEC(@SQL)
END
GO

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2005-12-09 : 06:26:36

The values OBM1, OBM2 should be withing single quotes

The Select Statement should be
SET @SQL =
'SELECT * from vweAvailableRooms
WHERE BD_RoomRef IN (''' + Replace(@strRoomList,',',''',''') + ''')'

EXEC(@SQL)

or

SELECT * from vweAvailableRooms
WHERE '%'+@strRoomList+'%' like '%'+BD_RoomRef +'%'

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources