Help in Select query - 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

Help in Select query

Author

Topic

requestor
Starting Member

17 Posts

Posted - 2007-10-09 : 11:22:11

Hai,
i have a table in which i have join_date column..
This is not null column.

declare @tbl table
(id int,
name varchar(20),
join_date datetime)

But the SP to retrieve from this table is having two input parameter..
@name varchar(20),@dt datetime..
Sometimes, i get value only for @name
in that case, how can i write my select statment..
now i have written like this

select * from @tbl where
name = @name
and datepart(join_date,'yyyy') = @dt

when i dont recieve the @dt parameter value still i want to retrieve the data based on the name...can i do that, without using dynamic query.
Help pls...

blindman
Master Smack Fu Yak Hacker

2365 Posts

Posted - 2007-10-09 : 11:40:11

Is this MS Access? Your datepart syntax is incorrect as it is.

Try this for SQL Server:

and datepart(year, join_date) = @dt or @dt is null)

e4 d5 xd5 Nf6

requestor
Starting Member

17 Posts

Posted - 2007-10-10 : 00:07:15

yes, i have used the correct syntax..but when posting i typed wrongly, i want this is sql server..
if i use and datepart(year, join_date) = @dt or @dt is null)
then, it will look for the date to be null value colunm..but in my table, the value willnt be null...

in my case ,i recieve the value for the parameter i need to use that value. if i dont receive the value i dont want compare the value ..

quote:
Originally posted by blindman

Is this MS Access? Your datepart syntax is incorrect as it is.

Try this for SQL Server:
and datepart(year, join_date) = @dt or @dt is null)

e4 d5 xd5 Nf6

renu
Starting Member

47 Posts

Posted - 2007-10-10 : 00:37:36

select * from @tbl where
name = @name
OR join_date = @dt

I think this works

Kristen
Test

22859 Posts

Posted - 2007-10-10 : 00:50:17

Find @dt, or if @dt is NULL then find @name:


WHERE    datepart(year, join_date) = @dt 
      OR (@dt is null AND name = @name)

Find @dt if @dt is NOT Null, AND find @name if @name is NOT null:


WHERE     (@dt IS NULL OR datepart(year, join_date) = @dt)
      AND (@name IS NULL OR name = @name)

Kristen

blindman
Master Smack Fu Yak Hacker

2365 Posts

Posted - 2007-10-10 : 10:07:13

quote:
Originally posted by requestor
if i use and datepart(year, join_date) = @dt or @dt is null)
then, it will look for the date to be null value colunm...

No. It will do exactly what you asked. If I take the time to answer your question, it is very rude of you to dismiss my solution without even trying it.

e4 d5 xd5 Nf6

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources