Error calling this 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)

Error calling this sp

Author

Topic

Pinto
Aged Yak Warrior

590 Posts

Posted - 2005-11-17 : 05:22:34

I am calling the following sp from asp.net. Have I got the syntax correct around the variables @strRoomRef etc as I am getting an error message 'Must declare the variable '@strRoomRef'

CREATE Procedure [spRB_GetBlockBookingDates]

@WHEREClause varchar(1000),
@ORDERBYClause varchar(500),
@strRoomRef nvarchar (50),
@strSDate datetime,
@strEDate datetime

AS

-- Create a variable @SQLStatement
DECLARE @SQLStatement varchar(1000)

-- Enter the dynamic SQL statement into the
-- variable @SQLStatement
SELECT @SQLStatement = "Select * from vweBlockBookings where
BD_RoomRef = @strRoomRef and
BD_DateRequired >= @strSDate and BD_DateRequired <= @strEDate and"
+ @WHEREClause + @ORDERBYClause

-- Execute the SQL statement
EXEC(@SQLStatement)
GO

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2005-11-17 : 05:26:21

SELECT @SQLStatement = 'Select * from vweBlockBookings where
BD_RoomRef = ''' + @strRoomRef + ''' and
BD_DateRequired >= ''' + @strSDate + ''' and BD_DateRequired <= ''' + @strEDate + ''' and '
+ @WHEREClause + @ORDERBYClause

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

Pinto
Aged Yak Warrior

590 Posts

Posted - 2005-11-17 : 05:38:05

Many thanks. I am now getting the error 'Syntax error converting datetime from character string.' The WHEREClause will be built dynamically when I can get it working properly !

I am passing my values as below and there are valid dates being passed

Dim MySQL As String = "spRB_GetBlockBookingDates"
Dim MyConn As New SqlConnection(strConn)
Dim Cmd As New SqlCommand(MySQL, MyConn)

Cmd.Parameters.Clear()

Dim WHEREClause As String = "and BD_P6=0 and BD_P7=0"

Cmd.CommandType = CommandType.StoredProcedure
Cmd.Parameters.Add(New SqlParameter("@strRoomRef", Me.lblNewRoom.Text))
Cmd.Parameters.Add(New SqlParameter("@strSDate", CDate(Me.txtDatefrom.Text)))
Cmd.Parameters.Add(New SqlParameter("@strEDate", CDate(Me.txtDateTo.Text)))
Cmd.Parameters.Add(New SqlParameter("@WHEREClause", WHEREClause))
Cmd.Parameters.Add(New SqlParameter("@ORDERBYClause", "BD_DateRequired"))

SqlDataAdapter1.SelectCommand = Cmd
Me.DsAvailableRooms1.Clear()

SqlDataAdapter1.Fill(DsAvailableRooms1, "vweBlockBookings")

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources