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 2000 Forums
 Transact-SQL (2000)
 Stored Procedures

Author  Topic 

Swede
Yak Posting Veteran

74 Posts
Posted - 2002-01-07 : 10:55:31
A stored procedure would be very useful if I am to create a forum v.2, as far as I understand it.

Or am I wrong? Could I not use a stored procedure to list stuff from a table? And then again, how do I even call a procedure from ASP for example? Or is this the wrong place to ask such question? Am I asking to many questions? hehe

=====================================
Why not try and do the impossible?

Edited by - Swede on 01/07/2002 10:57:18

Nazim
A custom title

1408 Posts
Posted - 2002-01-07 : 11:06:30
Nopes, this is the perfect place to ask SQL Server related questions, and u can ask as many questions u want to.

Yeah, u can certainly pull the data from a table and list it from a stored procedure.

for calling a Stored Procedure check this link
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=11741

Whether you should go for a Stored Procedure or not , this link should help you.
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=10964

HTH




----------------------------
Anything that Doesn't Kills you Makes you Stronger

Edited by - Nazim on 01/07/2002 23:47:30
Go to Top of Page

Swede
Yak Posting Veteran

74 Posts
Posted - 2002-01-07 : 11:16:05
Thanks a bunch for those links Will be most helpful!

Now I just have to figure out how to create Stored Procedures, I haven't done any pure SQL programming except in ASP... Any hints of where I should begin?

=====================================
Why not try and do the impossible?
Go to Top of Page

izaltsman
A custom title

1139 Posts
Posted - 2002-01-07 : 11:51:09
This article will help you to get started:

http://www.4guysfromrolla.com/webtech/111499-1.shtml

(except you probably shouldn't use their naming convention...)

And while you are at it, you should probably pick up a good book or two on SQL Programming (I'd recommend Rob Viera's "Professional SQL Server Programming":

http://sqlteam.com/store.asp



Edited by - izaltsman on 01/07/2002 11:53:10
Go to Top of Page

Swede
Yak Posting Veteran

74 Posts
Posted - 2002-01-07 : 11:55:07
Fantastic Just what I needed!

=====================================
Why not try and do the impossible?
Go to Top of Page

Swede
Yak Posting Veteran

74 Posts
Posted - 2002-01-07 : 12:31:35
Will a SP actually save me time? This is a bad example but take a news engine I created which uses news_id to select the news I want to show in ASP.

Will a stored procedure like this one actually get me the data from the Table faster?

CREATE PROCEDURE sp_read_selected_news
@read_news_id int
AS
Select news_date, news_title, news_body, news_link, news_link_title, news_target From news
Where news_id = @read_news_id
Go


=====================================
Why not try and do the impossible?
Go to Top of Page

Swede
Yak Posting Veteran

74 Posts
Posted - 2002-01-07 : 13:04:45
No need to answer that, I noticed a big difference myself already

=====================================
Why not try and do the impossible?
Go to Top of Page

AjarnMark
SQL Slashing Gunting Master

3246 Posts
Posted - 2002-01-07 : 18:01:45
Hey Swede, a tip on writing your stored procedures.... Write them in an editor (I prefer SQL Query Analyzer) and SAVE the script that you are creating to a file so that you can run it again later or edit it easily. Then if you're working in a professional setting, I encourage you to add these scripts to some sort of source control system like Visual Source Safe.

Create your sprocs vis scripts instead of directly in the Enterprise Manager and you'll thank me later.

<edit>And if you put a command at the top of your script that drops the procedure first, and then creates the new one, it will save you hassle later when you're fine-tuning or updating...</edit>
--------------------------------------------------------------
1000 Posts, Here I come! I wonder what my new title will be...

Edited by - AjarnMark on 01/07/2002 18:03:14
Go to Top of Page

graz
Chief SQLTeam Crack Dealer

4149 Posts
Posted - 2002-01-07 : 18:09:12
quote:

Thanks a bunch for those links Will be most helpful!

Now I just have to figure out how to create Stored Procedures, I haven't done any pure SQL programming except in ASP... Any hints of where I should begin?

=====================================
Why not try and do the impossible?


And I can't let this thread go by without a plug for SQLTeam's very own bookstore (http://www.sqlteam.com/store.asp). Well, really just links to Amazon :) Anyway, those are the books that we suggest.

===============================================
Creating tomorrow's legacy systems today.
One crisis at a time.
Go to Top of Page

Nazim
A custom title

1408 Posts
Posted - 2002-01-07 : 23:44:43
One Book , we missed out is

THE GURU's Guide To Transact Sql by KenHenderson which Robvolk will definetly suggest, if not force you to buy

Another thing, dont start your stored procedure's name with sp_ (that signifies System Stored Procedure).
zdss




----------------------------
Anything that Doesn't Kills you Makes you Stronger

Edited by - Nazim on 01/07/2002 23:46:52
Go to Top of Page

Merkin
Funky Drop Bear Fearing SQL Dude!

4970 Posts
Posted - 2002-01-08 : 00:01:48
Settle down there Nazim...your getting a little eager

Hehehehe...

It is of course, a GREAT book........but...

Professional SQL Server programming is a better book for starting out with this stuff. Guru's guide can come a bit later.


Damian

Edited by - merkin on 01/08/2002 00:05:06
Go to Top of Page

Nazim
A custom title

1408 Posts
Posted - 2002-01-08 : 00:09:18
Merkin, Unfortunately you are right. my last 10 or more Posts have been HORRIBLE .

Yeah , Was trying to Settle Down at 250 .

On Second thought , Not the No. of post but the Quality of Posts Matter(Wow!! ,Quotable Quotes .

Aha! Am more one step nearer now


----------------------------
Anything that Doesn't Kills you Makes you Stronger
Go to Top of Page

Swede
Yak Posting Veteran

74 Posts
Posted - 2002-01-08 : 03:11:32
Ah, yep, am using SQL Query Analyzer as I found it to be easier

Good idea to save the SP considering one couldn't edit them afterwards, god knows why...

Created my first SP yesterday hehe, something simple as a news engine

Thanks guys, guess Ill be back soon with more advanced questions

By the way, can I do IF and THEN sentences in a SP?

=====================================
Why not try and do the impossible?

Edited by - Swede on 01/08/2002 03:16:44
Go to Top of Page

spock
Starting Member

35 Posts
Posted - 2002-01-08 : 03:56:11
Nazim why is it not advisable to name stored procedures sp_ ?
other than confusing with system stored procs does it have any effect on the efficiency?

Thanks
Go to Top of Page

Merkin
Funky Drop Bear Fearing SQL Dude!

4970 Posts
Posted - 2002-01-08 : 04:57:58
Hi all

Swede : Yes you can use IF / THEN logic in stored procs. Not within one select statement, but as control of flow. There is syntax and stuff in books online.

Spock : When you call a stored proc starting with "sp_", it looks for it in the master database first. So yes, you get a very small delay.

Damian
Go to Top of Page

spock
Starting Member

35 Posts
Posted - 2002-01-08 : 05:22:45
Merkin,
when i create a stored proc named p_ComputeResults in a database other than master then would SQL Server search for it first in the local db and if it is not found then check in the master database ? is this the order of searching for a store procedure which is not named sp_XXX ?

Thanks
Go to Top of Page

Merkin
Funky Drop Bear Fearing SQL Dude!

4970 Posts
Posted - 2002-01-08 : 05:29:49
No, in that case it would just look in the current database. If it wasn't there you would get an error.

Damian
Go to Top of Page

Swede
Yak Posting Veteran

74 Posts
Posted - 2002-01-08 : 05:55:44
Great Info Merkin, Thanks

=====================================
Why not try and do the impossible?
Go to Top of Page

AjarnMark
SQL Slashing Gunting Master

3246 Posts
Posted - 2002-01-08 : 12:21:51
Swede, be sure to read through BOL (SQL Server Books Online) as Merkin suggests. One extra piece you will see in there are you read about IF... ELSE... is the need for BEGIN...END when you have multiple lines of code to be executed in the conditional. BOL is your friend! And so is SQLTeam

--------------------------------------------------------------
1000 Posts, Here I come! I wonder what my new title will be...
Go to Top of Page

Swede
Yak Posting Veteran

74 Posts
Posted - 2002-01-09 : 05:19:50
to all friends!

=====================================
Why not try and do the impossible?
Go to Top of Page
   

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources

Articles

Forums

Blogs

Contact Us

About the Site