Logo

Navigation
  • Home
  • Services
    • ERP Solutions
    • Implementation Solutions
    • Support and Maintenance Solutions
    • Custom Solutions
    • Upgrade Solutions
    • Training and Mentoring
    • Web Solutions
    • Production Support
    • Architecture Designing
    • Independent Validation and Testing Services
    • Infrastructure Management
  • Expertise
    • Microsoft Development Expertise
    • Mobile Development
    • SQL Server Database and BI
    • SAP BI, SAP Hana, SAP BO
    • Oracle and BI
    • Oracle RAC
  • Technical Training
    • Learn Data Management
      • Business Intelligence
      • Data Mining
      • Data Modeling
      • Data Warehousing
      • Disaster Recovery
    • Learn Concepts
      • Application Development
      • Client Server
      • Cloud Computing Tutorials
      • Cluster Computing
      • CRM Tutorial
      • EDI Tutorials
      • ERP Tutorials
      • NLP
      • OOPS
      • Concepts
      • SOA Tutorial
      • Supply Chain
      • Technology Trends
      • UML
      • Virtualization
      • Web 2.0
    • Learn Java
      • JavaScript Tutorial
      • JSP Tutorials
      • J2EE
    • Learn Microsoft
      • MSAS
      • ASP.NET
      • ASP.NET 2.0
      • C Sharp
      • MS Project Training
      • Silverlight
      • SQL Server 2005
      • VB.NET 2005
    • Learn Networking
      • Networking
      • Wireless
    • Learn Oracle
      • Oracle 10g
      • PL/SQL
      • Oracle 11g Tutorials
      • Oracle 9i
      • Oracle Apps
    • Learn Programming
      • Ajax Tutorial
      • C Language
      • C++ Tutorials
      • CSS Tutorial
      • CSS3 Tutorial
      • JavaScript Tutorial
      • jQuery Tutorial
      • MainFrame
      • PHP Tutorial
      • VBScript Tutorial
      • XML Tutorial
    • Learn Software Testing
      • Software Testing Types
      • SQA
      • Testing
  • Career Training
    • Career Improvement
      • Career Articles
      • Certification Articles
      • Conflict Management
      • Core Skills
      • Decision Making
      • Entrepreneurship
      • Goal Setting
      • Life Skills
      • Performance Development
      • Personal Excellence
      • Personality Development
      • Problem Solving
      • Relationship Management
      • Self Confidence
      • Self Supervision
      • Social Networking
      • Strategic Planning
      • Time Management
    • Education Help
      • Career Tracks
      • Essay Writing
      • Internship Tips
      • Online Education
      • Scholarships
      • Student Loans
    • Managerial Skills
      • Business Communication
      • Business Networking
      • Facilitator Skills
      • Managing Change
      • Marketing Management
      • Meeting Management
      • Process Management
      • Project Management
      • Project Management Life Cycle
      • Project Management Process
      • Project Risk Management
      • Relationship Management
      • Task Management
      • Team Building
      • Virtual Team Management
    • Essential Life Skills
      • Anger Management
      • Anxiety Management
      • Attitude Development
      • Coaching and Mentoring
      • Emotional Intelligence
      • Stress Management
      • Positive Thinking
    • Communication Skills
      • Conversation Skills
      • Cross Culture Competence
      • English Vocabulary
      • Listening Skills
      • Public Speaking Skills
      • Questioning Skills
    • Soft Skills
      • Assertive Skills
      • Influence Skills
      • Leadership Skills
      • Memory Skills
      • People Skills
      • Presentation Skills
    • Finding a Job
      • Etiquette Tips
      • Group Discussions
      • HR Interviews
      • Interview Notes
      • Job Search Tips
      • Resume Tips
      • Sample Resumes
 

SQL Server Stored Procedures

By Exforsys | on July 11, 2005 |
VB.NET 2005

In this tutorial you will learn further more about Access and Manipulate SQL Server data – Using Stored Procedures, Creating a Stored Procedure, Running Stored Procedures from .NET, Using Parameters in Stored Procedures, The @@IDENTITY Variable

Access and Manipulate SQL Server data – Using Stored Procedures

Using Stored Procedures

Stored procedures are an important aspect in all database programs. VB.NET applications are no exceptions to this rule. Stored procedures enable users change the business logic without actually tinkering with the application. SQL Server 2005’s (formerly code named Yukon) integration with the .NET CLR makes it possible for developers to author stored procedures, triggers, user defined functions, and create other database objects using a managed language such as VB.NET. This excellent feature provides a number of benefits, including increased productivity, significant performance gains, and the ability to leverage the features of .NET Code Access Security to prevent assemblies from performing certain operations.

Creating a Stored Procedure

Stored procedures for SQL Server 2005 databases can now be written in managed code. SQL Stored procedures can be created by adding Stored Procedure items to SQL Server projects. Once the stored procedure created in managed code, is deployed it can be executed like any other stored procedure. Let us understand this process by working on an example.

1. Create a new Project and

2. Choose Windows application.

3. In the solution explorer right click and add new item.

4. Choose Stored Procedure in the dialog box.

5. Replace the code with the one given below:

Click here to view sample code

The stored procedure has been created. But what does it do? Press F5 to execute the procedure and look at the output.

 

 

 

 

 

 

 

Using Parameters in Stored Procedures

Parameters allow you to create flexible SQL routines that use values provided at run time. Because the parameters can be changed each time the procedure runs, users should avoid creating one SQL routine for each value that has to be matched. For example, in a Microsoft SQL Server database, we can add a @ProductCategoryID parameter to a stored procedure that searches the Production.productCategory table for Product category name. We can then run the stored procedure each time we want to specify a different ProductCategoryID. When parameters are used in stored procedures or user defined functions it will:

  • Return a status value to another SQL routine to indicate success or failure and the reason for the failure.
  • Pass parameter values to other functions or stored procedures.

The @@IDENTITY Variable

@@IDENTITY is a Global Variable used in SQL Sever. The value of this global @@Identity variable is set to the value of the identity chosen for the row inserted after performing an insert into a table with a column that is an identity. In cases where multiple rows are inserted, the last value will be set. Let us see an illustration:

create table t(
keyvalue int identity(1,1),
datavalue char(10)
)

when these statements are run the value 1 will be printed and then in the next run value 2 will be printed. This is because users tend to make the common mistake of not assigning a local variable immediately on insert.

insert into t (datavalue) values (‘hello’)
print @@Identity

The following code will also not work and it will also degrade performance when the number of records increase.

declare @Inserted_Key int
insert into t (datavalue) values (‘hello’)
select @Inserted_Key = max(keyvalue) from t

Secondly there is no gurantee that correct value of the column would be obtained to the variable, as the SQL Server is a multi-User Server. Alternatively, we can use the following kind of implementation to give a desirable performance:

declare @Inserted_Key int
insert into t (datavalue) values (‘hello’)
set @Inserted_Key = @@Identity

The identity inserted will be saved in a local variable.

Note: The user must have Visual Studio Beta 2 and SQL Server 2005 with Adventure works.mdb to see the output.

The deployment unit for managed code is called an assembly. An assembly is packaged as a DLL or executable (EXE) file. While an executable can run on its own, a DLL must be hosted in an existing application. SQL Server can load and host managed DLL assemblies. To load an assembly into SQL Server, we need to use the Create Assembly statement.

CREATE ASSEMBLY InsertPrCategory

FROM (Path and assembly name.)

Running Stored Procedure from .NET

In Solution Explorer, expand the TestScripts folder and double-click the Test.sql file. Replace the code in the Test.sql file with the following code:

EXEC InsertPrCategoryName ‘Aerospace’

SELECT * from Production.ProductCategory where name = ‘Aerospace’

Press F5 to build, deploy, and debug the stored procedure.

To run a stored procedure:

In Server Explorer, expand the Stored Procedures folder.

(i) Right-click the name of the stored procedure that you want to run.

(ii) Choose Execute on the shortcut menu. The Output window shows the status of the stored procedure.

(iii) If the stored procedure contains parameters, the Run Stored Procedure dialog box prompts you for the parameter values. In the Value column, type a value for each parameter that appears in the Name column, and then click OK. For details about parameters, see Using Parameters in Stored Procedures and User-Defined Functions.

(iv) The Output window is updated with status information as the stored procedure is run. The Output window also displays result sets and return values for the stored procedure.

« « Working with File System in .NET
ADO.NET Object Model » »

Author Description

Avatar

Editorial Team at Exforsys is a team of IT Consulting and Training team led by Chandra Vennapoosa.

Free Training

RSSSubscribe 394 Followers
  • Popular
  • Recent
  • Working with Legacy Code and COM Components

    July 30, 2005 - 0 Comment
  • Implementing Inheritance

    June 5, 2005 - 0 Comment
  • VB.NET Creating and Managing Components Part 1

    July 6, 2005 - 0 Comment
  • .NET Common Windows Forms Controls Part 1

    June 26, 2005 - 0 Comment
  • Data Manipulation with ADO.NET

    July 15, 2005 - 0 Comment
  • Windows Application Testing

    July 30, 2005 - 0 Comment
  • Visual Studio.NET Namespaces

    June 7, 2005 - 0 Comment
  • VB.NET Creating and Managing Components Part 2

    July 6, 2005 - 0 Comment
  • DomainUpDown and NumericUpDown Controls

    June 27, 2005 - 0 Comment
  • Using XML Data

    July 14, 2005 - 0 Comment
  • Microsoft .NET Creating Installation Components

    August 10, 2005 - 0 Comment
  • Shared Assembly

    August 9, 2005 - 0 Comment
  • The File Types Editor

    August 9, 2005 - 0 Comment
  • Tracing VB.NET Windows Application

    August 9, 2005 - 0 Comment
  • VB.NET Windows Application Testing

    August 9, 2005 - 0 Comment
  • The Registry Editor in Visual Studio.NET 2005

    August 4, 2005 - 0 Comment
  • Customizing Setup Project in Visual Studio.NET 2005

    August 4, 2005 - 0 Comment
  • Deploying Windows Applications In Visual Studio.NET 2005

    August 3, 2005 - 0 Comment
  • Debugging Windows Applications In Visual Studio.NET 2005

    August 3, 2005 - 0 Comment
  • Working with Legacy Code and COM Components

    July 30, 2005 - 0 Comment

Exforsys e-Newsletter

ebook
 

Related Articles

  • Microsoft .NET Creating Installation Components
  • Shared Assembly
  • The File Types Editor
  • Tracing VB.NET Windows Application
  • VB.NET Windows Application Testing

Latest Articles

  • Project Management Techniques
  • Product Development Best Practices
  • Importance of Quality Data Management
  • How to Maximize Quality Assurance
  • Utilizing Effective Quality Assurance Strategies
  • Sitemap
  • Privacy Policy
  • DMCA
  • Trademark Information
  • Contact Us
© 2023. All Rights Reserved.IT Training and Consulting
This website uses cookies to improve your experience. We'll assume you're ok with this, but you can opt-out if you wish.AcceptReject Read More
Privacy & Cookies Policy

Privacy Overview

This website uses cookies to improve your experience while you navigate through the website. Out of these, the cookies that are categorized as necessary are stored on your browser as they are essential for the working of basic functionalities of the website. We also use third-party cookies that help us analyze and understand how you use this website. These cookies will be stored in your browser only with your consent. You also have the option to opt-out of these cookies. But opting out of some of these cookies may affect your browsing experience.
Necessary
Always Enabled
Necessary cookies are absolutely essential for the website to function properly. This category only includes cookies that ensures basic functionalities and security features of the website. These cookies do not store any personal information.
Non-necessary
Any cookies that may not be particularly necessary for the website to function and is used specifically to collect user personal data via analytics, ads, other embedded contents are termed as non-necessary cookies. It is mandatory to procure user consent prior to running these cookies on your website.
SAVE & ACCEPT