Stored procedure optional parameter bit

How to return Output parameter from Stored Procedure in ASP. Net using C and VB. Here Mudassar Ahmed Khan has explained how to use and return value from Stored Procedure using Output Parameter in ASP.

For this article I a Table named Fruits is used which contains FruitId and FruitName columns. The name of the Fruit is fetched using Output Parameter in SQL Server Stored Procedure in ASP. In this article I will explain how to use and return value from Stored Procedure using Output Parameter in ASP. For this example I have created a database named FruitsDB which has a table named Fruits with the schema as follows.

In the Fruits Table I have inserted few records as shown below. Following is the connection string defined in the Connection Strings section of the Web. You need to modify it as per your SQL Server Instance and Database name. The HTML Markup consists of an ASP.

Setting up Stored Procedures with Optional Parameters and Using a Strongly Typed TableAdapter to Execute the said Stored Procedure - CodeProject

Net TextBox using which the Fruit Id will be passed to the SQL Server Stored Procedure, an ASP. Net Label within which the name of the Fruit fetched using Output Parameter will be displayed and finally an ASP.

Net Button control to trigger the process of fetching the Fruit Name. The SQL Server Stored Procedure accepts the following two Parameters. FruitId — This is an INPUT Parameter used to pass the Id of the Fruit.

FruitName — This is an OUTPUT Parameter used to fetch the Name of the Fruit based on its FruitId. Output Parameter is identified by the keyword OUTPUT. FruitName VARCHAR 30 OUTPUT. SET NOCOUNT ON. You will need to import the following namespaces. Return Output parameter from Stored Procedure in ASP. The following event handler is executed when the Button is clicked, it simply makes a database call to the stored procedure GetFruitName discussed earlier.

First the Input Parameter FruitId is added along with its Value i. Next the second Parameter FruitName is added. Since FruitName is an Output Parameter we cannot use AddWithValue function hence it is added using the Add method of SqlCommand Parameter class with its Data Type and Size specified.

Once the FruitName Parameter is added, then its Direction is set to Output since by default the Direction of all Parameter is Input.

Once the Stored Procedure is executed, the value fetched from the Stored Procedure is stored in the Value property of the FruitName Output Parameter. Finally the name of the Fruit is displayed on page using Label control. AddWithValue " FruitId"int. Add " FruitName"SqlDbType. Parameters[ " FruitName" ]. Protected Sub Submit sender As Objecte As EventArgs.

Using con As New SqlConnection constring. Using cmd As New SqlCommand "GetFruitName"con. AddWithValue " FruitId"Integer. Using Parameterized queries to prevent SQL Injection Attacks in SQL Server Here Mudassar Ahmed Khan has explained how to use parameterized queries to query SQL Server stored procedure optional parameter bit in order to avoid SQL Injection attacks. Calling Update SQL Server Stored Procedures using ADO.

Net Here Mudassar Ahmed Khan has explained how to call stored procedures that update the data in SQL Server Database using ADO. Calling Insert SQL Server Stored Procedures using ADO.

Net Here Mudassar Ahmed Khan has explained how to call Insert SQL Server Stored Procedures using ADO. Net in as ASP. Calling Select SQL Server Stored Procedures using ADO. Net Here Mudassar Ahmed Khan has explained how to call Select SQL Server Stored Procedures using ADO. Net Web Application and also how to bind the data returned by the stored procedures to AS.

Tip Pass table name dynamically to SQL Server query or stored procedure Here Mudassar Ahmed Khan has explained how to dynamically pass table name to SQL Query or Stored Procedure in SQL Server Using Stored Procedures in SQL Server Database Here Mudassar Ahmed Khan has explained how to create modify s&p 500 trading strategy delete Stored Etoro binary options signals in Microsoft How to make money origami spider Server Database.

Net here Mudassar Ahmed Khan explain Parameterized Queries ADO. Exception - Variable names must be unique within a query batch or stored procedure Stored procedure optional parameter bit Mudassar Ahmed Khan has explained the Exception The variable name has already been declared. Variable names must be unique within a query batch or stored procedure in ASP. Pass function as argument or parameter in JavaScript Here Mudassar Ahmed Khan has explained how to pass JavaScript function or method as parameter argument to another JavaScript function or method.

Custom Paging in ASP. Net GridView using SQL Server Stored Procedure Here Mudassar Ahmed Khan has explained how to implement Custom Paging in ASP. Net GridView using SQL Server Stored Procedure. Pass Additional Parameter to WebMethod using ContextKey Here Mudassar Ahmed Khan has explained how to pass additional parameter to Web Service or WebMethod for ASP.

Net AJAX AutoCompleteExtender using ContextKey property. Select SQL Server Indicator for metatrader binary options alerts Procedures using ASP. Net Example Here Mudassar Ahmed Khan has explained how to call Select SQL Server Stored Procedures using ADO.

Net Web Application and also how to bind the data returned by the stored procedures to ASP. Bind data to ASP. Net GridView using Stored Procedure Here Mudassar Ahmed Khan has explained how to bind data to GridView using Stored Procedure in ASP. Net SqlDataSource pass value to SelectParameter using QueryString Parameter Example Here Mudassar Ahmed Khan has explained with an example and attached sample code, how to to pass value to SelectParameter of SqlDataSource using QueryString Parameter in the URL and filter the result of SqlDataSource in ASP.

How to write a stored procedure in SQL Server Management Studio Here Mudassar Ahmed Khan has explained with an example, how to create modify and delete Stored Procedures in Microsoft SQL Server Database using SQL Server Management Studio. Thank you for the feedback. The comment is now awaiting moderation. You will be notified via email when the author replies to your comment. Please select a comment to reply.

You can add your comment about this article using the form below. Make sure you provide a valid email address else you won't be notified when the author replies to your comment Please note that all comments are moderated and will be deleted if they are Not relavant to the article Spam Advertising campaigns or links to other sites Abusive content. Please do not post code, scripts or snippets.

Required Invalid Email Address. Required Invalid security code. Hi, This is Mudassar Khan. Now you can ask questions directly to me on my new site ASPForums. It is a ASP. Net Question Answer site, specially started to allow visitors of this site ask their doubts and queries. Just click the Ask Now button and ask your question now. The code samples and API available at www.

You are free to use it for commercial as well as non-commercial use at your own risk, but you cannot use it for posting on blogs or other tutorial websites similar to www. All the code samples and API provided by the authors are solely their creation and neither the author nor the site are responsible if it does not work as intended. I agree to the above terms.

What our readers say. Harsha Chowdary For freshers like me your articles are easier to understand.

Using Dynamic SQL in Stored Procedures - ronoxivipyr.web.fc2.com

Levitoso You are the best Programmer thanks. I find the format of your tutorials helpful and surprisingly easy to understand. Keep up the good work.

Asif Butt Your articles help a lot. I am from Pakistan and makes me happy to see anyone from India ahead of others.

And even apart from thatyou seem to have made some top of the list place for yourself and that itself commands congratulations. Kundan U R REALY GREAT.