如何传递DateValue [英] How to pass DateValue

问题描述

嗨朋友们，我创建了以下商店程序



ALTER程序usr_sp_Search_Room_by_City_Date ----''，'8'，''，'DEC 13 2014'

@RoomName varchar（100），

@CityId int = 0，

@LandMark varchar（100），

@BookingDate varchar

as



begin

将@Query声明为varchar（max）



set @ Query ='选择tblusr.UserId，tblP.RoomId，tblP.LandMark，tblusrL.Role，tblusr.UserFname，tblusr.UserLname，tblusr.UserPhNo，
tblusr.Gender，tblP.RoomName，tblCountry.CntryId，tblCountry.CntryName，tblState.StateId，tblState.StateName，

tblCity.CityId，tblCity.CityName，tblP.RoomPrice，tblP .PTotalSeating，tblP.PAc_NonAc，tblP.RoomPhNo，tblP.RoomTypeId，

tblRoomType.RoomType，tblP.GoogleLink，tblP.ParkingSpace，tblP.Accomodation，tblP.HallArea，tblP.BookingAmount，
tblP.RoomLandLine，tblP.Zip，tblImg.ImageName

来自tblUser tblusr join tblUserLogIn tblusrL on tblusr.UserId = tblusrL.UserId

加入tblRoom tblP on tblP.RoomId = tblusr.UserId join tblImage tblImg on tblImg.RoomId = tblusr.UserId

加入tblCountry on tblP。 RoomCountryId = tblCountry.CntryId在tblState.StateId = tblP.RoomStateId上加入tblState

加入tblCity on tblCity.CityId = tblP.RoomCityId加入tblRoomType on tblRoomType.RoomTypeId = tblP.RoomTypeId

其中''true''= tblImg.IsProfileImage和''true''= tblusrL.Isconfirmed'

if（@RoomName！=''）

set @Query = @ Query +'和tblP.RoomName喜欢''％'+ @ RoomName +'％'''

if（@LandMark！=''）

set @Query = @ Query +'和tblP.LandMark喜欢''％'+ @ LandMark +'％'''

if（@CityId！=''）

set @Query = @ Query +'和tblCity.CityId ='+ cast（@CityId as varchar）

if（@BookingDate！=''）

set @ Query = @ Query + '和tblP.RoomId不在（从TblBookings选择RoomId，其中Pdate喜欢''％'+ CAST （@BookingDate AS VARCHAR）+'％'''

exec（@Query）



结束



每当我试图运行这个商店程序时



命令：



usr_sp_Search_Room_by_City_Date''，'8'，''，'2014年12月13日'



投掷以下错误：



消息102，等级15，状态1，行10

'％D％'附近的语法不正确。



usr_sp_Search_palace_by_City_Date''，'8'，''， '12 -13-2014'



投掷以下错误：



消息102，等级15，状态1，行10

'％1％'附近的语法不正确。



Pdate有数据类型。



请帮我摆脱这个问题。

Hi Friends, I have created below store procedure

ALTER procedure usr_sp_Search_Room_by_City_Date ----'','8','','DEC 13 2014'
@RoomName varchar(100),
@CityId int=0,
@LandMark varchar(100),
@BookingDate varchar
as

begin
Declare @Query as varchar(max)

set @Query='Select tblusr.UserId,tblP.RoomId,tblP.LandMark,tblusrL.Role,tblusr.UserFname,tblusr.UserLname,tblusr.UserPhNo,
tblusr.Gender,tblP.RoomName,tblCountry.CntryId,tblCountry.CntryName,tblState.StateId,tblState.StateName,
tblCity.CityId,tblCity.CityName,tblP.RoomPrice,tblP.PTotalSeating,tblP.PAc_NonAc,tblP.RoomPhNo,tblP.RoomTypeId,
tblRoomType.RoomType,tblP.GoogleLink,tblP.ParkingSpace,tblP.Accomodation,tblP.HallArea,tblP.BookingAmount,
tblP.RoomLandLine,tblP.Zip,tblImg.ImageName
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(@RoomName!=' ')
set @Query=@Query + ' and tblP.RoomName like ''%'+@RoomName+'%'''
if(@LandMark!=' ')
set @Query=@Query + ' and tblP.LandMark like ''%'+@LandMark+'%'''
if(@CityId!=' ')
set @Query=@Query + ' and tblCity.CityId='+cast(@CityId as varchar)
if(@BookingDate!=' ')
set @Query=@Query+'and tblP.RoomId not in (Select RoomId from tblBookings where Pdate like ''%'+CAST(@BookingDate AS VARCHAR)+'%'''
exec(@Query)

end

Whenever I have tried to run this store procedure

Command :

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

It is throwing below error :

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

usr_sp_Search_palace_by_City_Date '','8','','12-13-2014'

It is throwing below error :

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

Pdate have datatype.

Please help me to get rid of this issue.

推荐答案

我执行了



EXECUTE usr_sp_Search_palace_by_City_Date @PalaceName =''，@ CityId = 8，@ LandMark =''，@ BookingDate ='2014-12-13'



抛出以下错误：



消息102，级别15，状态1，行10

'％2％'附近的语法不正确。

I have executed with

EXECUTE usr_sp_Search_palace_by_City_Date @PalaceName ='',@CityId =8, @LandMark ='', @BookingDate ='2014-12-13'

It is throwing below error:

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

Pdate具有varchar数据类型

和第10行是ALTER程序usr_sp_Search_Room_by_City_Date

Pdate have varchar datatype"
and Line 10 is "ALTER procedure usr_sp_Search_Room_by_City_Date

我试过了

仍然给出同样的问题

I have tried it
Still giving same issue

这篇关于如何传递DateValue的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！