How To Write To Sp Register From C Variable Ccs

In this article you will learn about how to create a stored process in SQL. This article covers answers to the post-obit questions,

1. What is a stored procedure in SQL?
2. Why do we use SET NOCOUNT ON in a stored procedure?
3. How many types of stored procedure are at that place?
4. How to write comments in SQL Server?
5. What are the naming conventions for stored procedures?
6. How to create a stored procedure to select data from a database tabe using SELECT SQL query?
7. How to execute stored procedures in SQL Server?
8. What is are parameters in stored procedures?
9. How to create parameters in a SELECT query stored procedure which return records as per parameter passed?
10. How to create an INSERT query based stored procedure?
11. How to create an UPDATE query based stored procedure?
12. How to create a stored process to delete records using DELETE query?

What is a Stored Procedure?

A SQL stored process (SP) is a collection SQL statements and sql command logic, which is compiled and stored on the database. Stored procedues in SQL allows us to create SQL queries to be stored and executed on the server. Stored procedures can besides be cached and reused. The principal purpose of stored procedures to hibernate direct SQL queries from the code and better operation of database operations such as select, update, and delete data.

Why do nosotros utilise SET NOCOUNT ON in a stored procedure?

While we gear up SET NOCOUNT ON information technology means there is no messages which shows the number of rows affected.

NOCOUNT means do not count that is ON.

Now you will come to know what happened when Ready NOCOUNT OFF.

Types of stored procedures

At that place are 2 types of stored procedures available in SQL Server:

1. User divers stored procedures
2. Organisation stored procedures

User divers stored procedures

User defined stored procedures are created by database developers or database administrators. These SPs contains one more more SQL statements to select, update, or delete records from database tables. User defined stored procedure tin can take input parameters and return output parameters. User divers stored process is mixture of DDL (Data Definition Language) and DML (Information Manipulation Language ) commands.

User divers SPs are farther classified into two types:

T-SQL stored procedures:T-SQL (Transact SQL) SPs receive and returns parameters. These SPs procedure the Insert, Update and Delete queries with or without parameters and render data of rows every bit output. This is one of the most common ways to write SPs in SQL Server.

CLR stored procedures:CLR (Mutual Linguistic communication Runtime) SPs are written in a CLR based programming language such as C# or VB.NET and are executed by the .Internet Framework.

System stored procedures

Organisation stored procedyres are created and executed by SQL Server for the server administrative activities. Developers usually don't interfere with system SPs.

Login to SQL Server database

Allow's login to our SQL Server database, so we can accomplish the folowing:

• How to create a SELECT QUERY based stored procedure which return all records?
• How to create a PARAMETER based SELECT QUERY stored procedure which return records based on parameters?
• How to create an INSERT query based stored process?
• How to create an UPDATE query based stored procedure?
• How to create a DELETE query based stored process?

Login in SQL SERVER with your Server Proper name, Login and Countersign.

Switch to your database. My database name is MBKTest.

Empty stored procedure will be created using the following:

The empty template created by SQL Server for a SP looks like the post-obit. The CREATE PROCEDURE SQL control is used to create a procedure, followed by a SP name and its parameters. The BEGIN and END area is used to ascertain the query for the operation. This is where you will write a select, update, insert, or delete queries.

12. SET  ANSI_NULLS ON
13. GO
14. SET  QUOTED_IDENTIFIER ON
15. Become
21. CREATE PROCEDURE  <Procedure_Name, sysname, ProcedureName>
23.     <@Param1, sysname, @p1> <Datatype_For_Param1, ,int > = <Default_Value_For_Param1, , 0>,
24.     <@Param2, sysname, @p2> <Datatype_For_Param2, ,int > = <Default_Value_For_Param2, , 0>
25. AS
26. Brainstorm
29. Prepare  NOCOUNT ON ;
32. SELECT  <@Param1, sysname, @p1>, <@Param2, sysname, @p2>
33. END
34. GO

How to write comments in SQL SERVER?

You tin annotate in sql server in the following ways,

1. -- (two hyphens / dash) for a single line of comment.
2. beginning with /* ……. stop with  */    for multiline comments.

What is the naming convention for stored procedures?

We must follow standard naming conventions which may also depend on your projection and coding policies.

For user divers stored procedure naming conventions, my suggestions are to add ane of the post-obit prefixes to your SP names.

1. sp
2. stp
3. stp_
4. udstp
5. udstp_

Naming conventions are just to identify objects. By calculation these prefixes in the proper name, nosotros can clearly identify that this object is a stored procedure.

Create a database table

Before, we can create and execute any SPs, we need a database tabular array. I create a database table named, "tblMembers" using the following SQL query and execute it on the server. Equally you tin see, my table has iv cavalcade where the first cavalcade is an idenity column. Once the table is created, open tabular array in your SSMS and add together some data past manually inbound data to the table.

1. USE [MBKTest]
2. Become
4. /****** Object:Table  [dbo].[tblMembers]    Script Date : xviii-Nov-17,Sabbatum half dozen:47:55 PM ******/
5. SET  ANSI_NULLS ON
6. Become
8. Set  QUOTED_IDENTIFIER ON
9. Go
11. Fix  ANSI_PADDING ON
12. GO
14. CREATE Table  [dbo].[tblMembers](
15.     [MemberID] [int ] IDENTITY(1,1) NOT NULL ,
16.     [MemberName] [varchar ](50) Nada ,
17.     [MemberCity] [varchar ](25) NULL ,
18.     [MemberPhone] [varchar ](15) Aught
19. )
21. Get
23. SET  ANSI_PADDING OFF
24. Become

How to create a SELECT stored procedure?

Click on your Database and expand "Programmability" item and right click on "Stored Procedures" or press CTRL + N to get new query window. In the query area betwixt BEGIN and END, type your SELECT statement to select records from the table. See the Select statement in the beneath code.

1. Set  ANSI_NULLS ON
2. Become
3. Fix  QUOTED_IDENTIFIER ON
4. GO
11. CREATE Procedure  stpGetAllMembers
12. AS
13. Brainstorm
16. SET  NOCOUNT ON ;
19. Select  * from  tblMembers
20. END
21. GO

Now, press F5 or click on Execute button to execute the SP.

You lot should see a message, "Command(due south) completed successfully."

Now go to Programmability -->Stored Procedures, Right Click and select Refresh.

You tin can encounter in the following image, the new SP called stpGetAllMembers is created.

Execute stored procedures in SQL Server

In beneath UI, right click on the SP name and select Execute Stored Process... to execute a SP. From here, you tin can also alter an exisitng SP.

Alternatively, you lot tin also execute a SP from the Query window.

To run stored procedure in SQL Server Direction Studio, switch to Query window or CTRL +Northward to open up an new query window and blazon the following command.

• Syntax - EXEC <stored procedure name>
• Example - EXEC stpGetAllMembers

Now, we run our stored procedure called stpGetAllMembers. The output looks similar the post-obit:

OUTPUT

What are parameters in stored procedures?

Parameters in SPs are used to laissez passer input values and render output values. At that place are two types of parameters:

1. Input parameters - Pass values to a stored process.
2. Output parameters - Return values from a stored process.

How to create a SELECT query SP with parameters?

In the previous steps, nosotros created a simple SP that returned all rows from a table. At present, let's create a new SP that will take a city name equally an inpurt parameter and will return all rows where city proper name matches the input parameter value.

Hither is the updated SP with a parameter @CityName.

1. Gear up  ANSI_NULLS ON
2. Go
3. SET  QUOTED_IDENTIFIER ON
4. GO
10. CREATE PROCEDURE  stpGetMembersByCityName
12.     @CityName nvarchar(xxx)
14. AS
15. Brainstorm
18. SET  NOCOUNT ON ;
20. Select  * From  tblMembers
21. where  MemberCity like '%' +@CityName+ '%'
23. Finish
24. GO

Execute information technology.

To run this SP, type the following command in SQL query tool:

EXEC GetMemberByCityName   @CityName = 'mal'

OR from the UI, run the SP and provide the following input.

The code to execute looks like the following:

1. Employ [MBKTest]
2. Become
4. DECLARE  @return_value int
6. EXEC     @return_value = [dbo].[GetMemberByCityName]
7.         @CityName = Due north'mal'
9. SELECT 'Return Value'  = @return_value
11. Become

OUTPUT

How to create a INSERT query based stored procedure?

Nosotros tin can utilize an INSERT INTO SQL query to insert data into a table. The following SQL statement creates an INSERT SP with three parameters.

1. SET  ANSI_NULLS ON
2. Get
3. SET  QUOTED_IDENTIFIER ON
4. GO
10. CREATE Procedure  stpInsertMember
11. @MemberNamevarchar (50),
12. @MemberCityvarchar (25),
13. @MemberPhonevarchar (15)
15. As
16. Brainstorm
19. SET  NOCOUNT ON ;
21. Insert into  tblMembers (MemberName,MemberCity,MemberPhone)
22. Values  (@MemberName,@MemberCity, @MemberPhone)
24. END
25. GO

Right click on stored procedure in Object Explorer and select Refresh.

Laissez passer the value of parameter in Execute dialog box. Something like this:

The following lawmaking tin can be used to execute this SP in SSMS.

1. USE [MBKTest]
2. GO
4. DECLARE  @return_value int
6. EXEC     @return_value = [dbo].[stpInsertMember]
7.         @MemberName = N'Mahesh Chand' ,
8.         @MemberCity = N'NewYork' ,
9.         @MemberPhone = North'9999945121'
10. SELECT 'Return Value'  = @return_value
11. GO

OUTPUT

In the query window, you can cheque if a new record for Member Proper noun 'Mahesh Chand' is added to the table.

Y'all tin can also run the same SP in code.

EXEC stpInsertMember @MemberName = 'Suhana & Ashish Kalla ', @MemberCity = 'Mumbai ', @MemberPhone = N'9022592774xxx'

OUTPUT

You tin can check "Suhana & Ashish Kalla" record is added successfully.

How to create an UPDATE quert based stored process?

Let's create a new SP that volition update a table records based on the Member ID cavalcade. The ID is passed as an input parameter. Here is the new SP that uses an UPDATE..SET..WHERE command.

1. Prepare  ANSI_NULLS ON
2. GO
3. Set  QUOTED_IDENTIFIER ON
4. Become
10. CREATE PROCEDURE  stpUpdateMemberByID
11. @MemberIDint ,
12. @MemberNamevarchar (l),
13. @MemberCityvarchar (25),
14. @MemberPhonevarchar (15)
16. As
17. Begin
20. SET  NOCOUNT ON ;
22. UPDATE  tblMembers
23. Ready  MemberName = @MemberName,
24.         MemberCity = @MemberCity,
25.         MemberPhone = @MemberPhone
26. Where  MemberID = @MemberID
27. Terminate
28. Become

Right click on stored procedure in the Object Explorer and select Refresh. You volition see the SP is created.

At present, Right click on SP name and select Execute stored procedure…. Provide the input values and execute.

We tin use the following control in SSMS.

1. Apply [MBKTest]
2. Get
4. DECLARE  @return_value int
6. EXEC     @return_value = [dbo].[stpUpdateMemberByID]
7.         @MemberID = 20,
8.         @MemberName = Northward'Nirupama Kalla' ,
9.         @MemberCity = N'Mumbai' ,
10.         @MemberPhone = North'904512541xxxx'
12. SELECT 'Return Value'  = @return_value
14. GO

EXEC stpUpdateMemberByID  17,'Gopal Madhavrai','Bikaner','90454564xxx'

The results should show you lot the updated values.

How to create a DELETE query based stored procedure?

Let's create a SP that will delete records. The new SP uses a DELETE command and delete all records that matches provided Member ID.

1. Set  ANSI_NULLS ON
2. Get
3. Prepare  QUOTED_IDENTIFIER ON
4. GO
10. CREATE Process  stpDeleteMemberByMemberID
11.     @MemberIDint
12. AS
13. Begin
16. SET  NOCOUNT ON ;
18. Delete from  tblMembers
19. where  MemberId = @MemberID
21. Stop
22. Become

Execute information technology.

Right click on Stored Procedures in the Object Explorer and select Refresh.

RUN stored procedure BY UI

Now over again correct click on stored procedure and select Execute stored procedure…

Equally you can see in the prototype, I passed @MemberID parameter value = four.

RUN DELETE stored procedure Past MANUALLY (CODING)

EXEC stpDeleteMemberByMemberID  2

OUTPUT

Y'all can come across in paradigm MemberID = 4 tape has been deleted successfully.

In this article, we saw how to create stored procedures in a SQL Server database for inserting, updating, and deleting records.

How To Write To Sp Register From C Variable Ccs,

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

Posted by: lukenrion1963.blogspot.com

Share this post