CODEWITHSUNDEEP
sqlsql-server
Parvesh Soni
Parvesh Soni

Reputation: 27

How to pass a date parameter into a stored procedure

I have created a stored procedure for booking rooms that are not in the tblBooking table for the date which is passing in as a parameter.

Pdate is of type varchar

When I try to run this stored procedure with the command:

usr_sp_Search_Room_by_City_Date '','8','','DEC 13 2014'

It is throws this error:

Msg 102, Level 15, State 1, Line 10
Incorrect syntax near '%D%'.

Here is my procedure:

ALTER procedure usr_sp_Search_Room_by_City_Date 
    @RoomName varchar(100),
    @CityId int = 0,
    @LandMark varchar(100),
    @BookingDate varchar
as
begin
   Declare @Query as varchar(max)

   set @Query = 'Select * from tblUser tblusr 
                 join tblUserLogIn tblusrL on tblusr.UserId = tblusrL.UserId
                 join tblRoom tblP on tblP.RoomId = tblusr.UserId 
                 join tblImage tblImg on tblImg.RoomId = tblusr.UserId 
                 join tblCountry on tblP.RoomCountryId = tblCountry.CntryId 
                 join tblState on tblState.StateId = tblP.RoomStateId
                 join tblCity on tblCity.CityId = tblP.RoomCityId 
                 join tblRoomType on tblRoomType.RoomTypeId = tblP.RoomTypeId
                 where ''true''=tblImg.IsProfileImage and ''true''=tblusrL.Isconfirmed'

   if(@BookingDate != ' ')
      set @Query=@Query+'and tblP.RoomId not in (Select RoomId from tblBookings 

   where Pdate like ''%'+CAST(@BookingDate AS VARCHAR)+'%'''

   exec(@Query)

end

Upvotes: 2

Views: 5150

Answers (4)

Dan Bracuk
Dan Bracuk

Reputation: 20804

How do you pass a date parameter? First, specify the datatype as date, not varchar.

Second, you don't need dynamic sql. You can use a case construct for your conditional logic. Specifically, this:

if(@BookingDate!=' ')
set @Query=@Query+'and tblP.RoomId not in (Select RoomId from tblBookings 
where Pdate like ''%'+CAST(@BookingDate AS VARCHAR)+'%'''

can be something like this.

where @BookingDate is null
or 
(
@BookingDate is not null
and 
tblP.RoomId not in 
(Select RoomId from tblBookings 
where Pdate = @BookingDate)
)

Upvotes: 0

Solomon Rutzky
Solomon Rutzky

Reputation: 48914

The source of the actual error is due to you needing a final right parenthesis at the end of:

AS VARCHAR)+'%'')'

Also, you need a space at the beginning of the set @Query=@Query+'and statement. Currently the SQL will render as (partially):

'true'=tblusrL.Isconfirmedand tblP.RoomId not

Even better, you don't even need the CAST as the datatype is already VARCHAR, so the SET controlled by the IF should be:

SET @Query = @Query + ' AND tblP.RoomId not in (Select RoomId from tblBookings 
  where Pdate like ''%'+ @BookingDate + '%'')';

Always specify a size for variable length datatypes. The @BookingDate input parameter and CAST(@BookingDate AS VARCHAR) (which again, should be removed anyway) should both specify VARCHAR(20). The default size for VARCHAR / NVARCHAR / etc, in some situations, is 1. In other situations it is 30. In either case, do not rely upon defaults.

General notes:

  • What does the actual data in the Pdate field look like? Just wondering why you are using LIKE instead of = for the test.

  • You should probably use the BIT datatype for IsProfileImage and Isconfirmed, or at least TINYINT. Either one would be incredibly more efficient than a string field.

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1271003

If you printed out the query, you would probably find something like this:

where 'true'=tblImg.IsProfileImage and 'true'=tblusrL.Isconfirmedand tblP.RoomId not in (Select RoomId from tblBookings

Notice that the where statement runs into the and, because you have no spacer.

You don't intend for the comparison to be to '%D%', but that is not a syntax error. This is cause because you have omitted lengths on varchar() declarations.

Here are my recommendations:

  • Always use length with varchar().
  • If you want to pass a date into a stored procedure, use a date variable, not a varchar().
  • Don't use like with dates.
  • Debug this code by printing out @Query to see what is really being generated.

Upvotes: 2

tsohtan
tsohtan

Reputation: 850

Is not about the date.

This is the output query from your dynamics query

Select * from tblUser tblusr 
join tblUserLogIn tblusrL on tblusr.UserId=tblusrL.UserId
join tblRoom tblP on tblP.RoomId=tblusr.UserId join tblImage tblImg 
on tblImg.RoomId=tblusr.UserId 
join tblCountry on tblP.RoomCountryId=tblCountry.CntryId join tblState 
on tblState.StateId=tblP.RoomStateId
join tblCity on tblCity.CityId=tblP.RoomCityId join tblRoomType 
on tblRoomType.RoomTypeId=tblP.RoomTypeId
where 'true'=tblImg.IsProfileImage and 'true'=tblusrL.Isconfirmedand tblP.RoomId not in (Select RoomId from tblBookings 

where Pdate like '%0%'

You miss the ")" at the end.

where Pdate like ''%'+CAST(@BookingDate AS VARCHAR)+'%'')'

Upvotes: 0

Related Questions