Wrox Programmer Forums

Need to download code?

View our list of code downloads.

Go Back   Wrox Programmer Forums > ASP.NET and ASP > ASP.NET 2.0 > ASP.NET 2.0 Basics
Password Reminder
Register
| FAQ | Members List | Calendar | Search | Today's Posts | Mark Forums Read
ASP.NET 2.0 Basics If you are new to ASP or ASP.NET programming with version 2.0, this is the forum to begin asking questions. Please also see the Visual Web Developer 2005 forum.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the ASP.NET 2.0 Basics section of the Wrox Programmer to Programmer discussions. This is a community of tens of thousands of software programmers and website developers including Wrox book authors and readers. As a guest, you can read any forum posting. By joining today you can post your own programming questions, respond to other developers’ questions, and eliminate the ads that are displayed to guests. Registration is fast, simple and absolutely free .
DRM-free e-books 300x50
Reply
 
Thread Tools Search this Thread Display Modes
  #1 (permalink)  
Old June 4th, 2007, 07:51 AM
Registered User
 
Join Date: Jun 2007
Location: Lahore, Punjab, Pakistan.
Posts: 6
Thanks: 0
Thanked 0 Times in 0 Posts
Send a message via Yahoo to mfarooqw
Default How i can write stored procedure confidently?

Hi,
I want to ask about stored procedurs. How they write, I am new in Asp.net, I want ot learn and make stored procedures from scratch. I can write queries, I have no problem in quries. Plz help me. There is many confusion in stored procedures such that input, output variables, and so on. I search many books and search on web but i can't become cofident in writing stored procedures. Plz help me how i can write stored procedures confidently.
Thanks to all,


Reply With Quote
  #2 (permalink)  
Old June 5th, 2007, 02:38 AM
planoie's Avatar
Friend of Wrox
Points: 16,481, Level: 55
Points: 16,481, Level: 55 Points: 16,481, Level: 55 Points: 16,481, Level: 55
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Aug 2003
Location: Clifton Park, New York, USA.
Posts: 5,407
Thanks: 0
Thanked 16 Times in 16 Posts
Default

Have you read any books on the subject? What particularly areas are you uncomfortable with?

Stored procedures (SPROCS) are just that, procedures that are stored. They are typically collections 1 or more regular T-SQL queries logically grouped together to perform some action. Often they are nothing more then a procedure of a single query operation.

One of the key points about SPROCS is that they are pre-optimized. When I call the database server and ask for this: "SELECT * FROM MyTable", the server has to first compute how to best execute this query. This is referred to the "execution plan". When you put this query (as simple as it is) into a SPROC, when you insert the SPROC into the database, the DB engine "compiles" it and stores it and the execution plans for all the queries in it. This makes the execution of the query faster each time, which could save lots of cycles.

Another key point of using SPROCS is the ability to make all or parts of the group of queries contained in it transactional. What this means is that you could execute several different (but related) queries in sequence and if you detect that one of them fails you can roll back the entire set of queries, leaving your data as it was before you started the transaction. This is a very powerful tool when you have complex procedures that touch many tables. Non-transactional sequences of related queries with a mid-execution failure can lead to orphan records. (An important note: with MS-SQL, any single query is executed in a single transaction. If you execute a stored procedure, the whole thing will execute as a single transaction. However, you can certainly explicitly define transactions inside a SPROC so that you can commit them as you go.)

As far as SPROC parameters go, it's much like functions in any program code. A SPROC can have parameters in several flavors:
Input only: pass a value into a SPROC
Output only: sets a value inside the SPROC for use after you call it
Input/Output: pass in and/or retrieve a value
Return: a special return value set inside the SPROC with "RETURN(x)". This can be any DB data type. In MS-SQL this parameter is always named @RETURN.

SPROCS can return 1 or more result sets. SELECT queries inside a SPROC will return result sets. These would be consumed in your .NET code.

Hopefully this has helped a bit. Feel free to ask more specific questions.

-Peter
Reply With Quote
  #3 (permalink)  
Old June 5th, 2007, 03:35 AM
Friend of Wrox
Points: 1,935, Level: 17
Points: 1,935, Level: 17 Points: 1,935, Level: 17 Points: 1,935, Level: 17
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Aug 2004
Location: United Kingdom
Posts: 550
Thanks: 0
Thanked 1 Time in 1 Post
Default

Wow Peter, it was a wonderful explanation....

Regards
Mike

Don't expect too much, too soon.
Reply With Quote
  #4 (permalink)  
Old June 6th, 2007, 05:48 PM
Authorized User
 
Join Date: Mar 2007
Location: , , .
Posts: 13
Thanks: 0
Thanked 0 Times in 0 Posts
Default

Start with simple queries:

select * from mytable

A good way to learn is to use SQL Query Analyzer [Enterprise Manager -> Tools -> SQL Query Analyzer]. You can use this to develop your queries and see the results in the grid below. Use PRINT statements to see what values are present - PRINT CONVERT(VARCHAR, @MYVAR)

Once you've got the hang of it you can write what are effectively small programmes, using the basic control methods: IF <expression>, ELSE, WHILE <expression>, etc.

SPs are a doodle really - if you can abstract SQL from your ASP page into a SP on the db, do so. It's the logical place for it anyway.
Reply With Quote
  #5 (permalink)  
Old June 7th, 2007, 12:55 AM
Friend of Wrox
 
Join Date: Aug 2006
Location: Amman, , Jordan.
Posts: 109
Thanks: 0
Thanked 0 Times in 0 Posts
Default

well.. first yeap it was a great explanation!! U defenitly were in the mood then!! :)

and for the first entry.. I really don't get one thing about ur question.. the thing about "make stored procedures from scratch."!!
it's simple.. u go to the query analyzer..
you type :
create procedure YourProcedureName
--If you have parameters define them here
@paramater1 int,
@parameter2 varchar(50)
as
--your queiry goes here
select * from myTable where refno = @parameter2


thats the simple meaningless stored procedure.. hope it will help you :) and off course you can use the enterprise manager

Nothing is impossible. The impossible only takes longer. "Digital Fortress, Dan Brown"
Reply With Quote
  #6 (permalink)  
Old June 7th, 2007, 01:25 AM
Authorized User
 
Join Date: Aug 2005
Location: , , .
Posts: 32
Thanks: 0
Thanked 0 Times in 0 Posts
Default

how to define SP to return more than one resultset.
and how to get it into dot net code say in dataset

Reply With Quote
  #7 (permalink)  
Old June 7th, 2007, 05:44 AM
Friend of Wrox
 
Join Date: Aug 2006
Location: Amman, , Jordan.
Posts: 109
Thanks: 0
Thanked 0 Times in 0 Posts
Default

more than one set = more than one select statement in the procedure :)

to fill your dataset, u should use a dataAdapter, and use the "fill()" function to fill the dataset :)

Nothing is impossible. The impossible only takes longer. "Digital Fortress, Dan Brown"
Reply With Quote
Reply


Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Trackbacks are Off
Pingbacks are On
Refbacks are Off


Similar Threads
Thread Thread Starter Forum Replies Last Post
how to write Store Procedure For Search biswapinky SQL Language 2 January 9th, 2008 02:02 PM
stored procedure kdm260 SQL Server 2000 2 June 19th, 2006 04:45 PM
How to write a retrieve procedure... milindgpatil Oracle 2 August 9th, 2005 10:49 PM
Write stored procs to accept 'all' values in a col pjmair SQL Server 2000 2 May 25th, 2005 01:14 AM
how to write stored procedure madhuri.sirsat SQL Server 2000 2 April 10th, 2005 02:10 PM



All times are GMT -4. The time now is 10:05 AM.


Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
© 2013 John Wiley & Sons, Inc.