Avoid Dynamic Query

Question

Post reply

Avoid Dynamic Query

KMPSSS

Ten Centuries

Points: 1052
More actions
March 11, 2011 at 1:22 am

#234970

Hi,
Can you please guide, how to avoid dynamic query for the following query. can you re-write the below query to avoid dyanamic query.
CREATE PROC GETBLACKVISITOR (@FNAME VARCHAR(100),@LNAME VARCHAR(20),@NRIC VARCHAR(20),@Company VARCHAR(60),@EMAIL VARCHAR(50))
AS
begin
declare @qry varchar(1000),@cnd varchar(500)
if(@NRIC='')
set @cnd= ''
else
set @cnd= ' OR V_AP_NRICENTRY= ''' + @NRIC + ''''
if(@EMAIL='')
set @cnd= @cnd + ''
else
set @cnd= @cnd + ' OR V_AP_EMAIL= ''' + @EMAIL + ''''
if ( @Company!='--')
set @cnd= @cnd + ' OR V_AP_COMPREP =''' + @Company + ''''
else
set @cnd= @cnd + ''
set @cnd= ' V_AP_BLACKLIST=''Y '' AND ((V_AP_FNAME = ''' + @FNAME + ''' And (V_AP_LNAME IS NULL OR V_AP_LNAME=''' + @LNAME + ''')) ' + @cnd
set @qry='SELECT (V_AP_SERIALNO) ,V_AP_FNAME,ISNULL(V_AP_LNAME,'''') V_AP_LNAME, V_AP_BLACKLISTDATE,V_AP_COMPREP, V_AP_BLACKLISTRES,V_AP_ADDRESS, V_AP_NRICENTRY, V_AP_PHONE, V_AP_EMAIL, V_AP_GROUPVISIT, V_AP_TITLE, V_AP_CATEGORY, Category = (select V_VC_
DESC FROM
VISITOR_CATEGORY where V_VC_CODE =V_AP_CATEGORY ),V_AP_EQ_CODE from APPOINTMENTS
WHERE ' + @cnd + ') ORDER BY V_AP_SERIALNO'
execute(@qry)
end

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

Phil Parkin SSC Guru Points: 246985 More actions · Answer 1

That doesn't look like it will be very easy.

I'm interested to see what happens with white visitors 😀

The absence of evidence is not evidence of absence.
Martin Rees

You can lead a horse to water, but a pencil must be lead.
Stan Laurel

John Mitchell-245523 SSC Guru Points: 148809 More actions · Answer 2

I don't think your query as it stands at the moment will work anyway. The line that begins "set @qry=" seems to end very abruptly.

Having said that, since it's not column names or table names that are being dynamically supplied, you should be able to do this fairly easily without using dynamic SQL. Just write the SELECT statement as in the normal way, and put conditions like Col1 = @Param1 in your WHERE clause. You will need to use CASE statements as well, to handle the possibility of parameters being supplied blank.

Have a go at that and post back if you have any difficulties.

John

KMPSSS Ten Centuries Points: 1052 More actions · Answer 3

I have tried like below to alter the dynamic query.. But i cant able produce the result. Can you please guide me.

Declare @FNAME VARCHAR(100)

Declare @LNAME VARCHAR(20)

Declare @NRIC VARCHAR(20)

Declare @Company VARCHAR(60)

Declare @EMAIL VARCHAR(50)

Declare @Temp VARCHAR(50)

Set @FNAME ='Surya'

Set @LNAME=''

Set @NRIC=''

Set @Company='--'

Set @EMAIL='mmmm.c@xyz.com'

SELECT (V_AP_SERIALNO) ,V_AP_FNAME,ISNULL(V_AP_LNAME,'') V_AP_LNAME, V_AP_BLACKLISTDATE,V_AP_COMPREP, V_AP_BLACKLISTRES,V_AP_ADDRESS, V_AP_NRICENTRY, V_AP_PHONE, V_AP_EMAIL, V_AP_GROUPVISIT, V_AP_TITLE, V_AP_CATEGORY, Category =

(select V_VC_DESC FROM VISITOR_CATEGORY where V_VC_CODE =V_AP_CATEGORY ),V_AP_EQ_CODE from APPOINTMENTS

WHERE V_AP_BLACKLIST='Y ' AND

((V_AP_FNAME =@FNAME And (isnull(V_AP_LNAME,'')='' or V_AP_LNAME=@LNAME )) or

((V_AP_NRICENTRY=@NRIC or isnull(@NRIC,'')='' )) or

((V_AP_EMAIL=@EMAIL or isnull(@EMAIL,'')='' ))

or (Case when ((isnull(@Company,'')<>'--') or (V_AP_COMPREP=@Company)) Then 1 else 0 End= 1)

)

ORDER BY V_AP_SERIALNO

John Mitchell-245523 SSC Guru Points: 148809 More actions · Answer 4

I think we're going to need some table DDL and sample data in order to help you with this, please.

Do you get an error message when you run that query, or do you get the wrong data returned?

John

KMPSSS Ten Centuries Points: 1052 More actions · Answer 5

I didnt receive any errors. I dont know, how to use case statement of @Company parameter in below query. Rest of part good as far as my concern.

Set @FNAME ='Surya'

Set @LNAME=''

Set @NRIC=''

Set @Company='--'

Set @EMAIL='mmb.c@xxx.com'

if isnull(@Company,'')='--'

set @Temp=''

SELECT (V_AP_SERIALNO) ,V_AP_FNAME,ISNULL(V_AP_LNAME,'') V_AP_LNAME, V_AP_BLACKLISTDATE,V_AP_COMPREP, V_AP_BLACKLISTRES,V_AP_ADDRESS, V_AP_NRICENTRY, V_AP_PHONE, V_AP_EMAIL, V_AP_GROUPVISIT, V_AP_TITLE, V_AP_CATEGORY, Category =

(select V_VC_DESC FROM VISITOR_CATEGORY where V_VC_CODE =V_AP_CATEGORY ),V_AP_EQ_CODE from APPOINTMENTS

WHERE V_AP_BLACKLIST='Y ' AND

(((V_AP_FNAME =@FNAME And (isnull(V_AP_LNAME,'')='' or V_AP_LNAME=@LNAME )) or

((V_AP_NRICENTRY=@NRIC or isnull(@NRIC,'')='' )) or

((V_AP_EMAIL=@EMAIL or isnull(@EMAIL,'')='' ))

or (Case when ((isnull(@Company,'')<>'--') and (V_AP_COMPREP=@Company)) Then 1 else 0 End= 1)

) )

ORDER BY V_AP_SERIALNO

John Mitchell-245523 SSC Guru Points: 148809 More actions · Answer 6

Your CASE statement looks OK, although you haven't said exactly what you're trying to achieve with it. As I said, please provide DDL, data and expected results.

John