Wrox Programmer Forums
Go Back   Wrox Programmer Forums > ASP.NET and ASP > ASP.NET 2.0 > ASP.NET 2.0 Basics
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 software programmers and website developers including Wrox book authors and readers. New member registration was closed in 2019. New posts were shut off and the site was archived into this static format as of October 1, 2020. If you require technical support for a Wrox book please contact http://hub.wiley.com
Old June 4th, 2007, 07:51 AM
Registered User
Join Date: Jun 2007
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?

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,

Old June 5th, 2007, 02:38 AM
planoie's Avatar
Friend of Wrox
Join Date: Aug 2003
Posts: 5,407
Thanks: 0
Thanked 16 Times in 16 Posts

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.

Old June 5th, 2007, 03:35 AM
Friend of Wrox
Join Date: Aug 2004
Posts: 550
Thanks: 0
Thanked 1 Time in 1 Post

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


Don't expect too much, too soon.
Old June 6th, 2007, 05:48 PM
Authorized User
Join Date: Mar 2007
Posts: 13
Thanks: 0
Thanked 0 Times in 0 Posts

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.
Old June 7th, 2007, 12:55 AM
Friend of Wrox
Join Date: Aug 2006
Posts: 109
Thanks: 0
Thanked 0 Times in 0 Posts

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)
--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"
Old June 7th, 2007, 01:25 AM
Authorized User
Join Date: Aug 2005
Posts: 32
Thanks: 0
Thanked 0 Times in 0 Posts

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

Old June 7th, 2007, 05:44 AM
Friend of Wrox
Join Date: Aug 2006
Posts: 109
Thanks: 0
Thanked 0 Times in 0 Posts

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"

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

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