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,

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

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

Regards
Mike

Don't expect too much, too soon.

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.

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"

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

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"