sp fast loop through resultset

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 2005 Forums

Transact-SQL (2005)

sp fast loop through resultset

Author

Topic

dr S.A.f.
Starting Member

13 Posts

Posted - 2008-05-16 : 04:49:49

Hi,

I have made some stored procedures to check if a user is involved with a certain record. basically every stored procedure contains the following logic.

example spCheckClientRelated:
select @res = count(*) from client_role where client_id = @cid and employee_id = @eid

if (@res = 0)
begin
... next select
end
if (@res = 0)
begin
... next select
end
....
return @res
end

so far so good. But the final check in CheckClientRelated tests if a user is related to one of the sales projects for that client.

I allready have the spCheckSalesProjectRelated that returns 1 or 0 similar to the example above

so I want to find an efficient method that selects all the sales_project_id 's from the sales_project table where client_id = @cid (i use offcourse select @sid = sales_project_id from sales_project where client_id = @cid at the moment)

And then I have to execute the spCheckSalesProjectRelated method for each @sid and @eid. This if offcourse where my problem is located. I don't know how to do a fast check for every selected @sid, until spCheckSalesProjectRelated returns 1

As you probably can determine from my question, sql is not really my domain, and I'm certainly not an expert, but I don't mind reading or looking up some stuff, so even a clue or a direction to look in would be most appreciated

thx in advance.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-05-16 : 11:06:28

I'm not sure you're approaching this in right way. Can you explain your full requirement please?

dr S.A.f.
Starting Member

13 Posts

Posted - 2008-05-16 : 19:26:12

Ok,

We have a client management application. Our employees can view every record, but they can only edit a record if they are somehow involved with that record (client, sale or a contact)

They can be involved in multiple ways. For example when an employee created a client record he can offcourse edit it. But if he is involved with one of the sales we made to that client he can also edit the client record.

Now to see if an employee is related to a sale. We have to check certain tables, again if he created the sale record he is related. But he can be part of the sales team, or he can be the sales initiator, ....
These are all tables with the following structur: ID, SALES_ID, EMPLOYEE_ID.

The only thing i need to know is if in one of those tables i have a match for the employee_id and sales_id, it doesn't matter if have one or ten matches, a match = that employee is related to that sales project.

This is not a problem, I do a select count(*) from table_x where sales_id = '' and employee_id = '' until count is bigger than zero for one of my selects. (this goes reasonably fast), and I was told that it is better then a select top 1.

The second part of our problem is a little more complicated. If I want to know if someone is somehow related to a client. I first check to see if that employee is by any chance the creator of that client record.
If he isn't I have to check if he is somehow related to one of the sales -by applying the check I explained above- we made for that client. So basically I have to select all the sales_id's from the sales table where the client_id equals the client_id for which I'm checking and call my checksalesproject method for each of those sales.

I allready came here with this question some time ago and then I was adviced to use view. It worked, but in our production environment this equals a view of a couple million rows and it takes up to 15 seconds to get a result, which is much too slow.

So a consultant told me to forget the views which are the result of a union of tables with client_id, employee_id fields, for client, or sales_id, employee_id fields for sales and use a stored procedure instead, that checks one table at a time and breaks when a result is found.

But when I do a lookup for a client and the employee didn't created it, I have to move on and see if that employee is somehow related to one of the sales we made to that client, resulting in a check for each of those sales. So I could like use a cursor to loop through all the sales_id's I find when I select sales_id from sales where client_id = @client_id, but I know that's not a very good approach, so I hoped someone here could give me a clue to a different (better) approach.

Pff, a long explanation, I hope it makes it easier to understand what I meand :)

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-05-17 : 02:49:39

I think you can use a single SP for this

CREATE PROCEDURE EmployeeRelations
@Employee_ID
AS
SELECT ISNULL(e.ID,s.Employee_ID) AS EmpID,c.Client_ID,s.Sales_ID
FROM Employee e
FULL OUTER JOIN Client c
ON c.Employee_ID=e.ID
LEFT JOIN Sales s
ON s.Employee_ID=ISNULL(e.ID,@Employee_ID)
AND s.Client_ID=c.ID
WHERE ISNULL(e.ID,s.Employee_ID)=@Employee_ID
GO

This will provide you with related client/sales project for the employee.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources