Stored Procedure Help - SQL Server Forums

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 2005 Forums

Transact-SQL (2005)

Stored Procedure Help

Author

Topic

jcb267
Constraint Violating Yak Guru

291 Posts

Posted - 2011-04-21 : 09:49:17

Hi ~

I have created a query in SQL 2005 that is pretty large - about 775 lines. It is in 5 steps, logically it makes sense and most importantly IT WORKS!! I have never worked with stored procedures before, is this something that would benefit from being part of an sp? If so, how do I create one??

nigelrivett
Master Smack Fu Yak Hacker

3385 Posts

Posted - 2011-04-21 : 10:07:23

>> is this something that would benefit from being part of an sp
yes

create procedure s_myproc
as
-- copy your query here
go

run by
exec s_myproc

==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy.

jcb267
Constraint Violating Yak Guru

291 Posts

Posted - 2011-04-21 : 10:21:51

Thanks for the help!

What is the benefit? I store a query file on my local drive, the sp is stored in the database though??

quote:
Originally posted by nigelrivett

>> is this something that would benefit from being part of an sp
yes

create procedure s_myproc
as
-- copy your query here
go

run by
exec s_myproc

==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy.

nigelrivett
Master Smack Fu Yak Hacker

3385 Posts

Posted - 2011-04-21 : 10:43:00

Stored in the database and backed up with it, don't need to give users access to the tables, can restructure the database without affecting users, can change the location of data, can split the query into multiple steps, can add logging, can see whnen the sp is being executed, can view the execution plan,...
You should still hold the script of the sp on disk and under source control to keep a history of changes.

==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy.

jcb267
Constraint Violating Yak Guru

291 Posts

Posted - 2011-04-21 : 12:03:10

OK, thanks for the help. I guess I did not fully understand the sp.....

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources