best way of stored procedure

MunishBhatia · January 29th, 2009, 10:16 AM

hi
in my Stored procedure, i need to call a table with where clause. from the result of the query i

will make in clause and will call another table like select * from <tablename> where memberid in

(@memberid). the @memberid is string which is result of first query.

what is the best approch here should i take temp tables or should i declare cursor to iterate or

should i used both for some time and then release.
please guide me to use Best way stored procedure for optimal results.

Old Pedant · January 29th, 2009, 05:30 PM

Best answer: DO NOT DO THIS!

Unless you really NEED to return *TWO* result sets (record sets, data tables, whatever) from the SP (which is unusual, but sometimes done) you should do it all in ONE query.

Join the tables. Or, at worst, use a sub-select.

Example:

Code:

Table: Departments
deptid  ::  deptname
113    ::  Administration
773    ::  Engineering
...

Table: Employees
empid :: deptid :: lastname ...
101  ::  773 :: Jones
102  ::  113 :: Adams
103  ::  981 :: Somebody

CREATE PROCEDURE GetEmployeesByDepartmentName( @deptname NVARCHAR(100) )
AS
SELECT E.* 
FROM Departments AS D, Employees AS E
WHERE D.deptid = E.deptid
AND D.deptname = @deptname
ORDER BY E.lastname

Give more details and we can surely find a way to avoid doing two SELECTs. Unless, again, you really *NEED* to get two resultsets. If so, maybe show the code in the "host language" that you will using.

MunishBhatia · January 30th, 2009, 01:13 AM

do you think using the cursors will effect performance of DB Server or will increase size at run time.

Jeff Moden · January 30th, 2009, 01:26 AM

What is it that you're actually trying to do? Forget, for a moment, what you think you want to do in code... I'm asking what the business requirements are so I can get a better idea of how to help you.

And, in SQL Server 2005, a cursor is NEVER necessary for anything.