Not a member of Pastebin yet?
Sign Up,
it unlocks many cool features!
- ALTER PROCEDURE spVillaGet
- -- Add the parameters for the stored procedure here
- @accomodationFK int = null,
- @regionFK int = null,
- @arrivalDate datetime,
- @numberOfNights int,
- @sleeps int = null,
- @priceFloor money = null,
- @priceCeil money = null
- AS
- BEGIN
- -- SET NOCOUNT ON added to prevent extra result sets from
- -- interfering with SELECT statements.
- SET NOCOUNT ON;
- -- Insert statements for procedure here
- select tblVillas.*, tblWeeklyPrices.price from tblVillas
- INNER JOIN tblWeeklyPrices on tblVillas.villaId = tblWeeklyPrices.villaFK
- where
- If @accomodationFK <> null then
- accomodationTypeFK = @accomodationFK
- @regionFK <> null Then
- And regionFK = @regionFK
- IF @sleeps <> null Then
- And sleeps = @sleeps
- IF @priceFloor <> null Then
- And price >= @priceFloor And price <= @priceCeil
- END
- select tblVillas.*, tblWeeklyPrices.price
- from tblVillas
- INNER JOIN tblWeeklyPrices on tblVillas.villaId = tblWeeklyPrices.villaFK
- where (@accomodationFK IS null OR accomodationTypeFK = @accomodationFK)
- AND (@regionFK IS null or regionFK = @regionFK)
- AND (@sleeps IS null OR sleeps = @sleeps)
- AND (@priceFloor IS null OR (price BETWEEN @priceFloor And @priceCeil))
- SELECT *
- FROM vehicles
- WHERE ([vin] LIKE COALESCE(@vin, [vin]) + '%' ESCAPE '')
- AND ([year] LIKE COALESCE(@year, [year]) + '%' ESCAPE '')
- AND ([make] LIKE COALESCE(@make, [make]) + '%' ESCAPE '')
- AND ([model] LIKE COALESCE(@model, [model]) + '%' ESCAPE '')
- SELECT *
- FROM vehicles
- WHERE (([vin] LIKE COALESCE(@vin, [vin]) + '%' ESCAPE '')
- OR (@vin IS NULL AND [vin] IS NULL))
- AND (([year] LIKE COALESCE(@year, [year]) + '%' ESCAPE '')
- OR (@year IS NULL AND [year] IS NULL))
- AND (([make] LIKE COALESCE(@make, [make]) + '%' ESCAPE '')
- OR (@make IS NULL AND [make] IS NULL))
- AND (([model] LIKE COALESCE(@model, [model]) + '%' ESCAPE '')
- OR (@model IS NULL AND [model] IS NULL))
- Where accomodationTypeFK = IsNull(@accomodationFK, accomodationTypeFK)
- And regionFK = Coalesce(@regionFK,regionFK)
- And sleeps = IsNull(@sleeps,sleeps )
- And price Between IsNull(@priceFloor, Price) And IsNull(priceCeil, Price)
Add Comment
Please, Sign In to add comment