OPTIMIZED VIEW 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

SQL Server 2008 Forums

Transact-SQL (2008)

OPTIMIZED VIEW QUERY

Author

Topic

Arun.G
Yak Posting Veteran

81 Posts

Posted - 2010-07-06 : 08:34:30

i had written below stored procedure to display leve history:

create PROCEDURE [dbo].[SP_LEAVEHISTORY_VIEW]

-- The below are the input arguments for calling the procedure

@LEAVETYPE_NAME VARCHAR(50),
@EMP_NAME VARCHAR(50),
@FROM_DATE DATE,
@TO_DATE DATE,
@LEAVESTATUS VARCHAR(50)

AS

SELECT
A.LEAVE_FROM AS FROM_DATE,
A.LEAVE_TO AS TO_DATE,
B.PI_FIRSTNAME AS EMPLOYEE_NAME,
A.NO_DAYS AS NO_DAYS,
C.LEAVETYPE_NAME AS LEAVETYPE,
D.LEAVESTATUS AS LEAVESTATUS,
A.MGR_COMMENTS AS COMMENTS

FROM
LEAVE A,
DATATABLE1 B,
MASTER_LEAVETYPE C,
MASTER_LEAVESTATUS D

WHERE
B.PI_Firstname=@EMP_NAME
AND A.LEAVE_STATUSID=@LEAVESTATUS_ID
AND A.LEAVE_FROM >= @FROM_DATE
AND A.LEAVE_TO <= @TO_DATE
AND A.LEAVETYPE_ID=@LEAVETYPE_ID
AND A.DEL_FLAG=0
AND B.PI_EmployeeId=A.EMP_ID
AND C.LEAVETYPE_ID=A.LEAVETYPE_ID
AND D.LEAVESTATUS_ID=A.LEAVE_STATUSID

-- SP Ends here

Now, the emp_name is dropdown list, leavetypename is dropdownlist,and leavestatus is dropdownlist

empname dropdown contains all the mep names and also one option ALL

same like leavetype has some values and ALL, leavestatus also some values and ALL Option in dropdown

suppose if user selectes ALL in all the 3 dropdownlist input means, i have to write seperate sp for that, or i can use same sp in optimized way to do it?

empname list is coming fron one table that will not contain all only empnames only, if user select ALL means, all employee's leave history
should come,

same like , if leavetype= ALL means all the leavetype should come(causal leave,medical leave,sick leave etc)

and , leavestatus =All , everything should display(Approved, pending,rejected etc)

can v write a function to handle all these conditions, and call that function into the sp?

otherwise i have to write individual sp for each condtitions?

pls suggest me to do in effiecient way ? and tell me how to do it?

KrafDinner
Starting Member

34 Posts

Posted - 2010-07-06 : 09:25:08

I suppose I can't necessarily attest to the efficiency of it, but why not use a couple of if statements to check your conditions at the beginning ?

All you would need to do is adjust your WHERE clause accordingly...

I'm not sure I completely understand your goal here, other than needing to adjust what records are selected based on the input conditions.

Arun.G
Yak Posting Veteran

81 Posts

Posted - 2010-07-07 : 03:05:29

CAN U PLS GIVE ME ONE IF CONDITION CHECK STATEMENT FOR THIS QUERY?

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2010-07-07 : 11:40:18

ARe you trying to do a Catch All Query? If so, check out this link:
http://sqlinthewild.co.za/index.php/2009/03/19/catch-all-queries/

yosiasz
Master Smack Fu Yak Hacker

1635 Posts

Posted - 2010-07-07 : 12:34:44

what is the UI? Web Access VB C#? Either way I would suggest the following 2 options

1.
your drop downs should have 2 values. One value user sees and other value hidden for programming purposes.
Like an array arrayEmpName[PI_Firstname,PI_Value]
So you will have arrayEmpName['ALL Employees', NULL]

Then in your WHERE clause

WHERE B.PI_Firstname=COALESCE(@EMP_NAME, B.PI_Firstname) Do the same thing for other fields that may have the 'ALL' option.

2.
Use dynamic query

All this you can do in one sproc

Hope this helps

<><><><><><><><><><><><><><><><><><><><><><><><><>
If you don't have the passion to help people, you have no passion

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources