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.
| Author |
Topic |
|
PeterG
Posting Yak Master
156 Posts |
 Posted - 2003-07-04 : 13:28:10
|
| I had this question on an employment test, and I didn't know how to go about it because I don't know what things can be done in SQL Server 2000.CustomerCustomerID int (pk)CompanyName varchar(100)Address varchar(100)PaymentType char(1) (fk)PaymentTypePaymentType char(1)PaymentTypeDescription varchar(15)PaymentsPaymentID int (pk)CustomerID int (pk)PymentDescription varchar(50)Amount moneyStatus char(1)PaymentDueDate datetimeUsing SQL Server 2000, write a stored proc that would loop thru customers with company name starting with 'xxx' and have payments type of A or M. If payment type A add 30 days to the due date if status is P. If payment type M due date is today if status is A.A couple of things went into my mind afterreading the question: team table or cursor. What's the best way to do this? |
|
|
nr
SQLTeam MVY
12543 Posts |
Posted - 2003-07-04 : 14:14:32
|
| >> write a stored proc that would loop thru customersAnswer is:'Why would you want to do it like that?'Either that's the answer they were looking for or the test was written by an application programmerIn either case the question doesn't deserve an SP as the answer.==========================================Cursors are useful if you don't know sql.DTS can be used in a similar way.Beer is not cold and it isn't fizzy.Edited by - nr on 07/04/2003 14:15:50 |
 |
|
|
Andraax
Aged Yak Warrior
790 Posts |
Posted - 2003-07-04 : 15:14:43
|
| Looks like an expert VB or Java programmer wrote that question, yes... :)If you really have to loop through the records I would suggest using a cursor... *bracing for slap*You should really write a normal update to do it though. That's the proper way. If the employer thinks it's wrong, educate him... |
|
|
|
PeterG
Posting Yak Master
156 Posts |
Posted - 2003-07-04 : 16:36:47
|
| It seems to me that the person who wrote that question wanted this problem solved using a stored proc, and he must have cursor in mind. If cursor would work in SQL Server 7, is there a better alternative in SQL Server 200? |
|
|
|
nr
SQLTeam MVY
12543 Posts |
Posted - 2003-07-04 : 16:50:12
|
| No difference between v7 and 2000 in this respect.Hope you didn't take the job. Sounds like a company with problems.==========================================Cursors are useful if you don't know sql.DTS can be used in a similar way.Beer is not cold and it isn't fizzy. |
|
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2003-07-05 : 08:42:29
|
| CREATE PROCEDURE UpdatePayments ASSET NOCOUNT ONUPDATE P SET PaymentDueDate=CASE WHEN C.PaymentType='A' AND P.Status='P' THEN DateAdd(d, 30, P.PaymentDueDate)WHEN C.PaymentType='M' AND P.Status='A' THEN GetDate() ELSE P.PaymentDueDate ENDFROM Customer C INNER JOIN Payments P ON C.CustomerID=P.CustomerIDWHERE C.PaymentType IN('A','M') AND P.Status IN('A','P') AND C.CompanyName LIKE 'xxx%'Nigel is right, you don't really need a stored procedure to do this since it is a single UPDATE statement, but here it is anyway. This will work in any version of SQL Server (even 6.5) |
|
|
|
X002548
Not Just a Number
15586 Posts |
Posted - 2003-07-07 : 09:58:49
|
| Peter,Next time call the interviewer a scrub (unless you want the job).Ask him what a batch window is btw..Brett8-) |
|
|
|
|
|
|
|
|
SQL SERVER 200 syntax