Stored Procedure - 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

General SQL Server Forums

New to SQL Server Programming

Stored Procedure

Author

Topic

nicole
Yak Posting Veteran

97 Posts

Posted - 2005-11-08 : 22:36:49

I can create procedures on SQL Server, but can I group the related procedure into Package like Oracle?

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2005-11-08 : 23:34:02

I think it is not possible. There is no Package concept in SQL Server.

Madhivanan

Failing to plan is Planning to fail

nicole
Yak Posting Veteran

97 Posts

Posted - 2005-11-09 : 00:11:31

oh, then it will be quite messy if I have many

jen
Master Smack Fu Yak Hacker

4110 Posts

Posted - 2005-11-09 : 02:27:21

i'm not familiar with oracle but what do you mean by grouping the procedures? what purpose that this provide aside from "neatness"?

do you mean by separate databases?
or you can use a naming conventions

--------------------
keeping it simple...

Kristen
Test

22859 Posts

Posted - 2005-11-09 : 05:44:38

"or you can use a naming conventions"

We do that:

ACC_SP_SOP_ORDH_SomeAction

is a Stored Procedure in our Accounts ("ACC") module/"package" which acts on the SalesOrderProcessing ("SOP") OrderHeader ("ORDH") table

NOTE: You should not prefix your Stored Procedure names with "sp_" as this is inefficient on SQL Server

Kristen

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2005-11-09 : 06:15:13

http://vyaskn.tripod.com/object_naming.htm

Madhivanan

Failing to plan is Planning to fail

nicole
Yak Posting Veteran

97 Posts

Posted - 2005-11-09 : 20:40:38

jen,
I want to group the related procedures into a package just to make it "neat" and organized, that's it

Kristen,
Yes, right now I use the naming convention to do the grouping somehow. I don't like it though as this makes the procedure name very long

Why using prefix "sp_" is inefficient on SQL Server?

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2005-11-09 : 23:54:32

>>Why using prefix "sp_" is inefficient on SQL Server?

Because, it makes query plan to think as if it were part of Master Database

Madhivanan

Failing to plan is Planning to fail

nicole
Yak Posting Veteran

97 Posts

Posted - 2005-11-10 : 03:26:07

Madhivanan:
Thanks. I got the answer from the web site you posted

I have another question.
My web site serves different customers, and each customer has their own customized page which displays different columns of a common table.

We currently put the select statement in the asp file, but I want to put it into a stored procedure. I'm thinking of creating views for each customer, so, all customer pages only have to call the stored procedure instead of writing the select statement.

Question:
Can I use variable for table name and the Where clause? For example,

select * from @CustomerView
  where @Where_Clause

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2005-11-10 : 04:11:25

Sound like Dynamic SQL. Check out sp_executesql in BOL

[KH]

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2005-11-10 : 04:23:14

Thats not good idea. You need to write query to filter data according to customers

Madhivanan

Failing to plan is Planning to fail

nicole
Yak Posting Veteran

97 Posts

Posted - 2005-11-10 : 05:06:45

yes, that's why VIEWs will be created per customer, and I will select record from the view. Further filtering will be put a variable and pass into the procedure as a parameter (e.g. @where_clause). That's my idea.

The purpose is to make this kind of select statement "sharable"...

Kristen
Test

22859 Posts

Posted - 2005-11-10 : 06:50:11

Dynamic SQL, parameterized using sp_ExecuteSQL, should make a Query Plan pretty much as good as a Stored Procedure.

However, you will need to give the user SELECT permissions on the underlying tables, rather than just EXECUTE on the Stored Procedure, so all dynamic SQL is best avoided if possible - if not, i.e. for some other reason, then I don;t foresee a downside to using sp_ExecuteSQL

Kristen

nicole
Yak Posting Veteran

97 Posts

Posted - 2005-11-11 : 03:31:14

I heard that select columns from views will slow down the performance. Is that true? I doubt it cos' views are selected from tables, why have difference in performance?

Kristen
Test

22859 Posts

Posted - 2005-11-11 : 08:44:11

Shouldn't do, no.

Kristen

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources