Can we use parameter to SP on left side of where clause

Question

0.00/5 (No votes)

See more:

SQL-Server-2008

Hello Friends,
I have suck at one place.
I have table named
RPT_MRFProgressDetails
which has column
MRFM_ReqdByDate, MPD_CurrReqdByDateRM

From front user can select from and to date using date control which is send in string format (dd/mm/yyyy) to SP.

Logic i want to use in SP is that.
1. Will check MPD_CurrReqdByDateRM is not null then it is between From and To Date.
2. If it is null, then will check MRFM_ReqdByDate is between

VB

From and To Date.

I have written logic in So as follows:

create procedure [dbo].[SP_RPTGetMRFsForResMgr]                         
(    
 @MRFM_FrDt varchar(10),                                    
 @MRFM_ToDt varchar(10)
)
as
begin
   select ..... from RPT_MRFProgressDetails
where convert(datetime,@MRFM_FrDt,103) <= isnull(MPD.MPD_CurrReqdByDateRM, MM.MRFM_ReqdByDate)
and convert(datetime,@MRFM_ToDt,103) >= isnull(MPD.MPD_CurrReqdByDateRM, MM.MRFM_ReqdByDate) 
end

Can we use parameter in left side and cloumns on right side of where clause?
Any other way to do this?

Thanks in advance :)

Posted 9-May-13 3:50am

dhage.prashant01

Add a Solution

Comments

Richard C Bishop 9-May-13 9:57am

Whats the problem?

dhage.prashant01 10-May-13 7:13am

i jst wana know, using
where convert(datetime,@MRFM_FrDt,103) <= isnull(MPD.MPD_CurrReqdByDateRM, MM.MRFM_ReqdByDate)
in query wont affect the performance?

gvprabu 9-May-13 10:33am

Check my solution... If any clarifications get back to me.

1 solution

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

gvprabu · Answer 1 · 2013-05-09T04:27:00

Hi,
You can use COALSEC[^] and BETWEEN[^] logic for this. Try like this....

SQL

create procedure [dbo].[SP_RPTGetMRFsForResMgr]                         
(    
 @MRFM_FrDt varchar(10),                                    
 @MRFM_ToDt varchar(10)
)
as
begin
    select ..... from RPT_MRFProgressDetails
    where CONVERT(DATETIME,COALESCE(MPD_CurrReqdByDateRM,MRFM_ReqdByDate),103) BETWEEN @MRFM_FrDt AND @MRFM_ToDt 
end

Regards,
GVPrabu