January 28, 2011 at 12:25 am
Hi
I am using sql server 2005, i had two tables
PackageCategory
CREATE TABLE [dbo].[PackageCategory](
[pc_CategoryID] [smallint] NOT NULL,
[pc_CategoryName] [varchar](150) NOT NULL,
CONSTRAINT [PK_PackageCategory] PRIMARY KEY CLUSTERED
(
[pc_CategoryID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
Package
CREATE TABLE [dbo].[Package](
[pa_PackageID] [int] NOT NULL,
[pa_PackageTitle] [varchar](150) NOT NULL,
[pa_CategoryID] [smallint] NOT NULL,
[pa_Country] [varchar](50) NOT NULL,
[pa_Location] [varchar](50) NOT NULL,
[pa_Duration] [varchar](100) NOT NULL,
[pa_Rates] [float] NOT NULL,
CONSTRAINT [PK_Package] PRIMARY KEY CLUSTERED
(
[pa_PackageID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
ALTER TABLE [dbo].[Package] WITH CHECK ADD CONSTRAINT [FK_Package_PackageCategory_CategoryID] FOREIGN KEY([pa_CategoryID])
REFERENCES [dbo].[PackageCategory] ([pc_CategoryID])
GO
These are the values in the table
INSERT INTO PackageCategory(pc_CategoryID,pc_CategoryName)
SELECT 1 ,'Adventure Package'
UNION ALL
SELECT 2 ,'Wildlife'
UNION ALL
SELECT 3 ,'Hoeymoon Package'
UNION ALL
SELECT 4 ,'Beach Holiday'
GO
INSERT INTO Package(pa_PackageID,pa_PackageTitle,pa_CategoryID,pa_Country,pa_Location,pa_Duration,pa_Rates)
SELECT 1 ,'ABCD',1,'India','Bangalore','Upto 3 nights/days',2345
UNION ALL
SELECT 2 ,'EFGH',2,'India','Mumbai','4-7 nights/days',5000
UNION ALL
SELECT 3 ,'IJKL',3,'US','Newyork','8+ nights/days',15000
UNION ALL
SELECT 4 ,'MNO',2,'UAE','Dubai','8+ nights/days',10000
UNION ALL
SELECT 5 ,'PQR',4,'India','Delhi','Upto 3 nights/days',10000
UNION ALL
SELECT 6 ,'ASD',1,'Nepal','kathmandu','4-7 nights/days',8000
UNION ALL
SELECT 7 ,'wer',3,'India','Kerala','8+ nights/days',19000
UNION ALL
SELECT 8 ,'QAZ',4,'India','Mysore','Upto 3 nights/days',1500
UNION ALL
SELECT 9 ,'hjk',1,'Srilanka','Colombo','4-7 nights/days',6000
UNION ALL
SELECT 10 ,'QAZ',2,'SouthAfrica','Capetown','Upto 3 nights/days',15000
UNION ALL
SELECT 11 ,'BVC',3,'US','Washington','8+ nights/days',17000
UNION ALL
SELECT 12 ,'vfg',4,'India','Kashmir','Upto 3 nights/days',9000
GO
I need a search procedure to return all the details from the Package procedure
Based on the Following criteria
1) PackageCategory name i.e pc_CategoryName
2)Package location i.e pa_Location
3)Duration i.e pa_Duration
4)Rate range i.e < 2999, 3000 - 4999, 5000 - 9999, 10000 - 14999, > 15000
(pa_Rates)
Thank you
January 28, 2011 at 12:48 am
Please u write the Procedure first and then let us know where exactly you are Facing the Problem
January 28, 2011 at 12:55 am
are you looking something like this
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE Procedure_Package
-- Add the parameters for the stored procedure here
@PackageCategory varchar(150),
@Package_Location varchar(50),
@Duration varchar(100),
@startRange int,
@EndRange int
AS
BEGIN
SET NOCOUNT ON;
SELECT Package.pa_PackageID,Package.pa_PackageTitle,
Package.pa_Country,
Package.pa_Rates,
PackageCategory.pc_CategoryName
From Package
Inner Join PackageCategory on Package.pa_CategoryID = PackageCategory.pc_CategoryID
Where PackageCategory.pc_CategoryName = @PackageCategory
and Package.pa_Location = @Package_Location
and Package.pa_Duration = @Duration
and Package.pa_Rates > @startRange
and Package.pa_Rates < @EndRange
END
GO
January 28, 2011 at 2:05 am
thanks Sharath this is exactly i wanted.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply