The out parameters in SQL Server, when used in Stored Procedures, allow developers to pass a value in the database to the front-end controls like label. They are most commonly used in web application development.
Let us discuss how to create and use them in ASP.Net with a practical example. First we will design our database.

  • Create a database in SQL Server.
  • Let us create a table with three columns, say username, password and confirmation password.

Create table logintable(username varchar(max),password varchar(max),confirmpassword varchar(max))

  • Let us create our Stored Procedure.

Here we have created a Stored Procedure named usplogintable with username, password and confirmpassword as input parameters. The next variable that I have created is the @error variable of varchar type. You can see the keyword "out" near the varchar. Yes, your guess is correct, the keyword "out" stands for the output parameter in SQL Server.

We will execute this Stored Procedure as a batch so we have begins and ends. Then, "set nocount on" avoids returning the number of rows affected.
The if condition checks whether the username exists in a database and if the answer for it is yes, the @error variable is set with the username already taken or it inserts the values into the table and sets the @error variable as the username inserted.

Executing the Stored Procedure also requres a different style. First you need to declare a variable, you need to specify the output parameter on execution and you need to write a select query at the end to make it execute. Here is my sample for the preceding sp.

I have inserted the table with the values markus, mark and confirm password as mark.

  • I can guess what you are thinking. “How can I use it in my server-side code?”. Yeah, I am an ASP.Net developer and I have the solution for this. Here are the ways.

Additionally, you must also open your Visual Studio or press Ctrl+r and type devenv.
Create an ASP.Net web application with the framework being above 2.0. First create a form in ASP.Net with three labels and three textboxes with names as username.password and confirmpassword as shown in the screen below.

Okay. Let me take you through a tour of the server-side code on it. I will use ADO.Net here for the database connectivity. I will add my logic on my button click. Add using statements for the namespaces System.data and System.Data.SqlClient since these are not the default namespaces in .Net.

Add the following code by double-clicking the submit button.

  • Here I have created the connection string in my fashion and you can use your own style in your application as usual.
  • Thats it. We are done. Press Ctrl+F5.
  • If you provide the inserted usename.
  • And if you provide a new username, yuppy, it is inserted.

Best SQL Server 2014 Hosting Recommendation

ASPHostPortal.com provides our customers with Plesk Panel, one of the most popular and stable control panels for Windows hosting, as free. You could also see the latest .NET framework, a crazy amount of functionality as well as Large disk space, bandwidth, MSSQL databases and more. All those give people the convenience to build up a powerful site in SQL Server 2014 . We offers Windows hosting starts from $5/month only. We also guarantees 30 days money back and guarantee 99.9% uptime. If you need a reliable affordable SQL SERVER 2014 Hosting, we should be your best choice.