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 |
|
jcb267
Constraint Violating Yak Guru
291 Posts |
 Posted - 2014-09-19 : 14:28:58
|
| Hi - I have been using SQL for about 3 years now but never really used stored procedures. I started doing a client reporting package four months ago, it will be run every month when our data warehouse refresh is complete. Currently, I have 5 queries to accomplish this. The queries are executed and save the output to a table for archiving purposes. Can I string all five queries together in one stored procedure and save results as needed?Any suggestions??Thanks!John |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2014-09-19 : 14:35:07
|
| Yes. Stored procedures can have thousands of lines of code. I wouldn't recommend a massive proc like that, but it certainly can be done. If you need help writing it, post the code for your current process.Tara KizerSQL Server MVP since 2007http://weblogs.sqlteam.com/tarad/ |
 |
|
|
jcb267
Constraint Violating Yak Guru
291 Posts |
Posted - 2014-09-19 : 14:38:42
|
| It would be rather large. Can you declare and call variables as you would in a query? |
|
|
|
jcb267
Constraint Violating Yak Guru
291 Posts |
Posted - 2014-09-19 : 14:43:25
|
| I understand that 5 queries would make it too big, maybe I can make two "simpler" stored procedures out of the 5 queries. So, if I can declare and call variables like in a query, I can declare them once and call them as needed thus making it more efficient, correct?? |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2014-09-19 : 15:24:33
|
quote:
Originally posted by jcb267
It would be rather large. Can you declare and call variables as you would in a query?
Yes. Basically everything you can do in a query can be done in a stored procedure. Stored procedures have more advantages though.Tara KizerSQL Server MVP since 2007http://weblogs.sqlteam.com/tarad/ |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2014-09-19 : 15:25:30
|
quote:
Originally posted by jcb267
I understand that 5 queries would make it too big, maybe I can make two "simpler" stored procedures out of the 5 queries. So, if I can declare and call variables like in a query, I can declare them once and call them as needed thus making it more efficient, correct??
Declaring it once won't make it more efficient. Indexes, statistics, code refactoring, etc are what will make it faster.Tara KizerSQL Server MVP since 2007http://weblogs.sqlteam.com/tarad/ |
|
|
|
jcb267
Constraint Violating Yak Guru
291 Posts |
Posted - 2014-09-19 : 15:38:25
|
| Thanks, Tara.....I will get to indexing, etc.......one step at a time |
|
|
|
|
|
|
|
|
Stored Procedure