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 |
|
KidSQL
Yak Posting Veteran
88 Posts |
 Posted - 2005-06-03 : 12:33:21
|
| I have recently pieced together several large, multi-step queries full of steps like creating, updating, altering, and dropping temporary tables and lots of other stuff in order to work with my data and return what I need. I can honestly say that it is, thus far, a work of crap - but it works and I'm fine with that for now, as I'm still in the learning phase of SQL. I am certain that the final form of what I'll be using will be very different from what I have now, but it's a start.Rather than running each query part separately, I am interested in piecing the whole thing together and parameterizing stuff, so that I could input parameter values from the outset and just let the whole thing run and was thinking about incorporating the whole thing into a stored procedure. My questions are:1). Is my thinking correct? Is this what the scope of a stored procedure is meant to be or is it meant to be much smaller than this?2). I have plenty of 'go' statements between my temporary data additions/deletions, I take it this is a problem, yes?3). I welcome any other comments.I fear some of you might ask 'what are you trying to do.' Well, it's not easy to describe succinctly, as its monstrous stuff (for me, at least). Let's just say I'm: 1). fetching a whole bunch of data that fits a certain criteria, 2). joining this data with other data based on certain criteria, 3). cleaning this data, 4). working with this data to run calculations, etc. Pretty much what so many people are doing in SQL Server all the time.Any feedback would be much appreciated. |
|
|
nr
SQLTeam MVY
12543 Posts |
Posted - 2005-06-03 : 12:47:24
|
| Consider putting sperate tasks in separate SPs then having either a shell SP to call them or if it is sheduled calling each fronm a separate step of a job.==========================================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. |
 |
|
|
X002548
Not Just a Number
15586 Posts |
|
|
KidSQL
Yak Posting Veteran
88 Posts |
Posted - 2005-06-03 : 12:50:40
|
| I suspected as much. Thank you for these comments. I welcome any others. |
|
|
|
|
|
|
|
|
Question on Stored Procedures
Think smallCompartmentalize each of your processes in to small managable sets of code, and turn thos in to stored procedures.Then tie all the mess togetehr with a driver stored procedure that calls each one, in turn.Make sure you use error handling.There's sample here: