store procedure ?

Subin Thomas

7y1.4k.NET

what is stored procedure as i am aware of it but havent used it can any one explain in a understandable manner .

thanks in advance,

subin

8 Replies

Know the answer? Post it — somebody with the same question will find it here.

Hamid Khan5y ago
Yes please follow the link :
https://www.c-sharpcorner.com/uploadfile/jitendra1987/sql-server-stored-procedure-with-C-Sharp-net/

I am here posting one sample for real example.

CREATE PROCEDURE usp_InsertEmployee
@flag bit output,-- return 0 for fail,1 for success
@EmpID int, @Name varchar(50), @Salary int, @Address varchar(100)
AS
BEGIN
BEGIN TRANSACTION
BEGIN TRY
Insert into Employee(EmpID,FirstName,Salary,[Address])
Values(@EmpID,@Name,@Salary,@Address)
set @flag=1;
IF @@TRANCOUNT > 0
BEGIN
commit TRANSACTION;
END
END TRY
BEGIN CATCH
IF @@TRANCOUNT > 0
BEGIN
rollback TRANSACTION;
END
set @flag=0;
END CATCH
END
----------------------------------------
--Execute above created procedure to insert rows into table
Declare @flag bit
EXEC usp_InsertEmployee
@flag output,
1,'Deepak',14000,'Noida'
if @flag=1
print 'Successfully inserted'
else print 'There is some error'
-------------------------------
--Execute above created procedure to insert rows into table
Declare @flag bit
EXEC usp_InsertEmployee
@flag output,
5,'Deepak',14000,'Noida'
if @flag=1
print 'Successfully inserted'
else print 'There is some error'
Subin Thomas7y ago
Jignesh Kumar thanks
Jignesh Kumar7y ago
Hi Subin,

Please find below link,
https://www.c-sharpcorner.com/uploadfile/jitendra1987/sql-server-stored-procedure-with-C-Sharp-net/
Subin Thomas7y ago
Sanwar Ranwa thanks i got some of it thank you for you explanation.
Subin Thomas7y ago
Packiaraj Santhiyagu thanks i will suerly check the link
Subin Thomas7y ago
madan sorab thanks for the explanation.
Madan Shekar7y ago
Stored procedures in SQL Server can accept input parameters and return multiple values of output parameters; in SQL Server, stored procedures program statements to perform operations in the database and return a status value to a calling procedure or batch. User-defined procedures are created in a user-defined database or in all system databases, except for when a read-only (resource database) is used. They are developed in Transact-SQL (T-SQL) or a reference to Microsoft. Temporary procedures are stored in tempdb, and there are two types of temporary procedures: local and global. Local procedures are only visible to the current user connection, while global procedures are visible to any user after they are created. System procedures arrive with SQL Server and are physically stored in an internal, hidden-resource database. They appear in the SYS schema of each system, as well as in a user-defined database.
Packiaraj Santhiyagu7y ago
Hi Subin,

Please refer below link

https://www.c-sharpcorner.com/article/how-to-create-a-stored-procedure-in-sql-server-management-studio/

store procedure ?

Jignesh Kumar thanks

Sanwar Ranwa thanks i got some of it thank you for you explanation.

Packiaraj Santhiyagu thanks i will suerly check the link