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 2008 Forums
 Transact-SQL (2008)
 Stored Procedure

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 Kizer
SQL Server MVP since 2007
http://weblogs.sqlteam.com/tarad/
Go to Top of Page

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?
Go to Top of Page

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??
Go to Top of Page

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 Kizer
SQL Server MVP since 2007
http://weblogs.sqlteam.com/tarad/
Go to Top of Page

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 Kizer
SQL Server MVP since 2007
http://weblogs.sqlteam.com/tarad/
Go to Top of Page

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
Go to Top of Page
   

- Advertisement -