Any alternate for Dynamic SQL - 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

SQL Server 2005 Forums

Transact-SQL (2005)

Any alternate for Dynamic SQL

Author

Topic

mrleokarthik
Starting Member

16 Posts

Posted - 2007-11-23 : 00:19:18

Form Single query based on many dynamic criteria.

--sample Query
select * from person where iage > @age and cgender=@gender

If we have @age and @gender then above query is fine, but we may not have @age and/or @gender also(means find all age group and gender)

In this case without using Dynamic Sql how to form single query.

suresha_b
Yak Posting Veteran

82 Posts

Posted - 2007-11-23 : 01:12:41

Try something like this:
if @age is null and @gender is null
select * from person
else if @age is not null and @gender is not null
select * from person where iage > @age and cgender=@gender
else ...

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2007-11-23 : 01:26:00

simpler version:-
select * from person
where (iage > @age OR @age IS NULL)
and (cgender=@gender OR @gender IS NULL

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2007-11-23 : 01:26:54

missed closing bracket
select * from person
where (iage > @age OR @age IS NULL)
and (cgender=@gender OR @gender IS NULL)

mrleokarthik
Starting Member

16 Posts

Posted - 2007-11-23 : 02:04:03

quote:
Originally posted by visakh16

missed closing bracket
select * from person
where (iage > @age OR @age IS NULL)
and (cgender=@gender OR @gender IS NULL)

Thank everyone for your time.

visakh16: Thanks for your right answer.

evilDBA
Posting Yak Master

155 Posts

Posted - 2007-11-23 : 03:26:10

NB: 'sinmpler' answer is not always the right one.
I doubt that ge and gender are indexed, and inxed on gender is useless anyway.
But if you have dynamic criteria on high selectivity columns, then you should better write multiple IFs, then one statement with ( ... OR ...)

mrleokarthik
Starting Member

16 Posts

Posted - 2007-11-23 : 06:32:06

quote:
Originally posted by evilDBA

NB: 'sinmpler' answer is not always the right one.
I doubt that ge and gender are indexed, and inxed on gender is useless anyway.
But if you have dynamic criteria on high selectivity columns, then you should better write multiple IFs, then one statement with ( ... OR ...)

I got a big query and many search criteria and write multiple IF's is impossible on all possibilities. Either i should go with visakh16's or else DynamicSQL.

Please suggest me should i need to indices all where clause columns ( except fields like age int,gender char(1) ofcourse)

evilDBA
Posting Yak Master

155 Posts

Posted - 2007-11-23 : 07:44:03

You can index all search columns with a high selectivity.
For all other columns you can use solution with ... OR ...
With 3 indexed columns you have 8 combinations (if cases)... with 4 indexed columns - 16.
So everything depends on the number of indexed columns you have.

mrleokarthik
Starting Member

16 Posts

Posted - 2007-11-23 : 08:30:09

quote:
Originally posted by evilDBA

You can index all search columns with a high selectivity.
For all other columns you can use solution with ... OR ...
With 3 indexed columns you have 8 combinations (if cases)... with 4 indexed columns - 16.
So everything depends on the number of indexed columns you have.

I have Login table - Should i need to index username and password columns. Other than that most of my frequently using fields are int datatype.
Can we index those fields, since these fields are used in public site's search criteria ?

evilDBA
Posting Yak Master

155 Posts

Posted - 2007-11-23 : 09:40:24

You should probably index username, it is unique.
password???? do you search by password??????????????? I doubt it.
How many records do you have?

mrleokarthik
Starting Member

16 Posts

Posted - 2007-11-23 : 10:24:31

quote:
Originally posted by evilDBA

You should probably index username, it is unique.
password???? do you search by password??????????????? I doubt it.
How many records do you have?

Iam telling about login table. to compare password for authentication only when user logs-in.

evilDBA
Posting Yak Master

155 Posts

Posted - 2007-11-23 : 11:37:15

You compare it, but you search by username only.

mrleokarthik
Starting Member

16 Posts

Posted - 2007-11-25 : 05:48:35

Thank you.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources