Search sql question - 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

Search sql question

Author

Topic

byte
Starting Member

4 Posts

Posted - 2012-10-20 : 14:10:52

Hi All,

I have recently taken over maintaining our custom asp application and I have a search procedure that finds all records if ShippedDate is empty but the code below doesn't actually work (this is the original developers code):

If @OpenOnly = 1
Begin
Set @sqlStr = @sqlStr + ' and (ShippedDate > ' + "'" + Cast(DateAdd(day, -10, GetDate()) As varchar(50)) + "'"
Set @sqlStr = @sqlStr + ' Or ShippedDate Is Null) '
End

If I remove the line:

Set @sqlStr = @sqlStr + ' and (ShippedDate > ' + "'" + Cast(DateAdd(day, -10, GetDate()) As varchar(50)) + "'"

and modify:

Set @sqlStr = @sqlStr + ' Or ShippedDate Is Null) '

to:

Set @sqlStr = @sqlStr + ' and ShippedDate Is Null '

It works fine but I'm not sure what that first Set line is actually there for and if it would cause any problems with removing that line.

Thanks for any tips as I've been thrown in at the deep end.

Thanks,

byte

chadmat
The Chadinator

1974 Posts

Posted - 2012-10-20 : 14:18:12

Looks like it is defaulting to ShippedDate over the previous 10 days (GetDate will return today, and he is subtracting 10 days, so if ShippedDate is greater than 10 days ago it is returned).

-Chad

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-10-20 : 14:41:45

it really depends on your requirement
based on your explanation you're good to go with the modification but its worth checking reason why first condition was there in the code. It looks to me like attempt was to get orders shipped particular period before (10 days from current date) as well!

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

byte
Starting Member

4 Posts

Posted - 2012-10-20 : 17:16:14

Thanks for casting your expert eyes over this, yes I do need to find out why it is added so I'll test a few more records before confirming to production code.

Thanks,

byte

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-10-20 : 17:31:15

welcome

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources