Stored Procedure explanation in detail

Hi Gokul,

Refer this link...You get idea about stored procedures....

http://www.sql-server-performance.com/articles/dba/stored_procedures_basics_p1.aspx

Cheers :)

Thanks,
T.Saravanan

Gokul, if this helps please login to Mark As Answer. | Alert Moderator

Hello Gokul,
Look at this article, that should help you to understand how to create stored procedure and pass parameters in C#.
http://www.dotnetfunda.com/articles/article375-insert-data-using-parameters-through-stored-procedure-.aspx .

You can also read http://www.dotnetfunda.com/articles/article563-working-with-stored-procedures-using-aspnet-.aspx.

Thanks!

Regards,
Sheo Narayan
http://www.dotnetfunda.com

Gokul, if this helps please login to Mark As Answer. | Alert Moderator

Hi

Please visit the MSDN link given below

http://msdn.microsoft.com/en-us/library/ms345415(v=SQL.100).aspx

Cheers

Cheers
www.SQLServerbuddy.blogspot.com
iLink Multitech Solutions

Gokul, if this helps please login to Mark As Answer. | Alert Moderator

Hi,

Check this link

http://www.c-sharpcorner.com/UploadFile/pk_khuman/ManagedStoredProceduresUsingCSharp02182007232059PM/ManagedStoredProceduresUsingCSharp.aspx

http://www.codeproject.com/KB/database/CSCodeBuilder.aspx

Thanks
Karthik
www.f5Debug.net

Gokul, if this helps please login to Mark As Answer. | Alert Moderator

Hi,
Here is your simple example;

CREATE procedure [dbo].[Address_Add]

@Address_id int OUTPUT
, @Address1 nvarchar(50) = NULL
, @Address2 nvarchar(50) = NULL
, @Address3 nvarchar(50) = NULL
, @Town nvarchar(50) = NULL
, @State nvarchar(50) = NULL
, @Post_code varchar(10) = NULL
, @Country_id int = NULL
, @User_ID int = null

AS
BEGIN
SET NOCOUNT OFF;
DECLARE @ReturnValue int
SET @ReturnValue = 0

BEGIN TRY


INSERT INTO Address
(
Address1
, Address2
, Address3
, Town
, State
, Post_code
, Country_id
)
VALUES
(
@Address1
, @Address2
, @Address3
, @Town
, @State
, @Post_code
, @Country_id
)

SET @Address_id = SCOPE_IDENTITY()

SET @ReturnValue = @@ROWCOUNT

END TRY

BEGIN CATCH
-- Put specific error info in to the @UserComment field
EXEC Error_logError @UserComment=@Address_id
SET @ReturnValue = 0-ERROR_NUMBER()
print 'Failure in ' + ERROR_PROCEDURE()
print ERROR_MESSAGE()
SET @Address_id = -1
END CATCH

SELECT @ReturnValue
RETURN @ReturnValue

-----
Suresh M

Gokul, if this helps please login to Mark As Answer. | Alert Moderator

Hi Suresh

Your sample stored procedure is Fine.

But, You have to consider something as follows...
1. Apropos the recommendation, NOCOUNT should be ON, Anyway you have also used the "SELECT @ReturnValue" so you will have resultset (or) Try to put some comment like Why you have used "NOCOUNT OFF" here... Otherwise, the Users/audience will get confuse.....

2. @@ROWCOUNT statement should be used immediate of the statement you want to capture the affected state. But you have used "SCOPE_IDENTITY()" prior to "@@ROWCOUNT", So the Affected rowcount always "1"..... , So, Kindly change the sequence of the statements as given below...,

INSERT Statement......
SET @ReturnValue = @@ROWCOUNT
SET @Address_id = SCOPE_IDENTITY()

But, as per your code, the INSERT statement also inserts one row, You have to consider all the scenarios, Then only your sample is usefull to the users(Beginners)

Note: When you try to steer something to audiance/user, QUALITY is very important...

Cheers
www.sqlserverbuddy.blogspot.com

Cheers
www.SQLServerbuddy.blogspot.com
iLink Multitech Solutions

Gokul, if this helps please login to Mark As Answer. | Alert Moderator

Hi Pandian,

Sorry for confusion.

1. I should have kept the SET NOCOUNT OFF, I thought the @@ROWCOUNT only get updated when the NOCOUNT is OFF.

2. I put that delibrtly, bcoz, I am using a architecture, which is my own creation, in which I try to catch RowCount of every StoredProcedure, This may be used for the logging purpose also.

I admit this can create a confusion, So Sorry guys for any confusion. Thanks Pandian for pointing out the issue. I appritiate your effort.

-----
Suresh M

Gokul, if this helps please login to Mark As Answer. | Alert Moderator