Use variable for field name 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)

Use variable for field name in Stored procedure

Author

Topic

Pinto
Aged Yak Warrior

590 Posts

Posted - 2005-08-25 : 05:21:03

I have the following sp shown below. tblRB_Bookings also has 28 fields BK_P1 to BK_P28 that represent half hour time slots. On my web page, I want my user to be able to click one of these and pass the parameters to the sp. However, I want to pass the time slot they pick and just search to see if that one has a value of 1 in it. I therefore need to use a variable in place of my field name in the line BK_P1 = 1
eg TimeVar = 1

How can I replace my time timeslot field name with a variable dependant on the one my user selects ?

CREATE Procedure [spRB_GetBooking]

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

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

GO

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2005-08-25 : 05:53:08

You need Dynamic SQL

Create Procedure [spRB_GetBooking]

@strDateRequired datetime,
@strRoomRef nvarchar(10),
@strTimeSlot nvarchar(8)
as
Declare @sql varchar(1000)
set @sql='Select *
from tblRB_Bookings where
BK_DateRequired = '''+cast(@strDateRequired as varchar) +''' and
BK_RoomRef='''+ @strRoomRef +''' and '+ @strTimeSlot+' = 1'
select @sql --Make sure this returns expected query
Exec(@sql)

Madhivanan

Failing to plan is Planning to fail

Pinto
Aged Yak Warrior

590 Posts

Posted - 2005-08-25 : 06:30:13

Thanks for you reply. I am now getting the error '@strTimeSlot is not a parameter for procedure spRB_GetBooking'when it tries to fill the dataset. I will be replacing BK_P8 with a variable, but am using this until I get the sp working.

Here's my code calling the sp.

Private Sub FillBookingGrid()
Dim MySQL As String = "spRB_GetBooking"
Dim MyConn As New SqlConnection(strConn)
Dim Cmd As New SqlCommand(MySQL, MyConn)
Cmd.CommandType = CommandType.StoredProcedure
Cmd.Parameters.Add(New SqlParameter("@strDateRequired", Me.Session("DateRequired")))
Cmd.Parameters.Add(New SqlParameter("@strRoomRef", Me.Session("RoomRef")))
Cmd.Parameters.Add(New SqlParameter("@strTimeSlot", "BK_P8"))
SqlDataAdapter1.SelectCommand = Cmd
Me.DsAvailableRooms1.Clear()
SqlDataAdapter1.Fill(DsBooking1, "tblRB_Bookings")

End Sub

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources