Sql query problem - 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

General SQL Server Forums

New to SQL Server Programming

Sql query problem

Author

Topic

pkuchaliya
Starting Member

49 Posts

Posted - 2008-09-02 : 09:50:06

HI ALL
I HAVE A TABLE IN WHICH DIFFERENT TYPE OF TABLE IS STORED.
create table user_detail(profile_id varchar(50),age int,salary bigint,designation varchar(50),residence varchar(50))

insert into
user_detail(profile_id,age,salary,designation,residence )
values('rahul',20,38000,'Manager','Delhi')
insert into
user_detail(profile_id,age,salary,designation,residence )
values('manish',28,8000,'Developer','Delhi')
insert into
user_detail(profile_id,age,salary,designation,residence )
values('satish',30,28000,'Executive','lucknow')
insert into
user_detail(profile_id,age,salary,designation,residence )
values('sarita',29,8000,'receptionist','lucknow')
insert into
user_detail(profile_id,age,salary,designation,residence )
values('srivani',35,18000,'Team leader','Bombay')

THE PROBLEM IS THAT I HAVE TO SEARCH THE DATA ON THE BASIS OF AGE ,SALARY, DESIGNATION,RESIDENCE.BUT THE CONDITION IS THAT IF USER GIVE ALL THE PARAMETER THE IT SEARCH ALL THE PARAMETER , IF NOT THEN I SEARCH ONLY THE GIVEN PARAMETER.

SUPPOSE USER GIVE THE AGE AND SALARY TO SEARCH THEN IT SHOULD DISPLAY THE RECORD THOSE AGE MATCH IN THE TABLE, AND ALSO WHOSE SALARY ALSO MATCH.
PLEASE ANY BODY HELP

pankaj

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-09-02 : 09:54:17

just use where condition like this

where (profile_id=@user OR @user IS NULL)
OR (age =@age OR @age is null )

....

and set the default values of all params to be NULL

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-09-02 : 09:57:52

[code]CREATE TABLE #Sample
(
profileID VARCHAR(50),
age INT,
salary BIGINT,
designation VARCHAR(50),
residence VARCHAR(50)
)

INSERT #Sample
SELECT 'rahul', 20, 38000, 'Manager', 'Delhi' UNION ALL
SELECT 'manish', 28, 8000, 'Developer', 'Delhi' UNION ALL
SELECT 'satish', 30, 28000, 'Executive', 'lucknow' UNION ALL
SELECT 'sarita', 29, 8000, 'receptionist', 'lucknow' UNION ALL
SELECT 'srivani', 35, 18000, 'Team leader', 'Bombay'

DECLARE @profileID VARCHAR(50),
@age INT,
@salary BIGINT,
@designation VARCHAR(50),
@residence VARCHAR(50)

SET @age = 29

SELECT *
FROM #Sample
WHERE (@profileID = profileID OR @profileID IS NULL)
AND (@age = age OR @age IS NULL)
AND (@salary = salary OR @salary IS NULL)
AND (@designation = designation OR @designation IS NULL)
AND (@residence = residence OR @residence IS NULL)

DROP TABLE #Sample[/code]

E 12°55'05.63"
N 56°04'39.26"

pkuchaliya
Starting Member

49 Posts

Posted - 2008-09-02 : 10:07:34

THANKS,

pankaj

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources