Sunday, February 12, 2012

Collecttion SQL


What is a self join? Explain it with an example.

Self join is just like any other join, except that two instances of the same table will be joined in the query. Here is an example: Employees table which contains rows for normal employees as well as managers. So, to find out the managers of all the employees, you need a self join.

CREATE TABLE emp
(
empid int,
mgrid int,
empname char(10)
)

INSERT emp SELECT 1,2,'Vyas'
INSERT emp SELECT 2,3,'Mohan'
INSERT emp SELECT 3,NULL,'Shobha'
INSERT emp SELECT 4,2,'Shridhar'
INSERT emp SELECT 5,2,'Sourabh'

SELECT t1.empname [Employee], t2.empname [Manager]
FROM emp t1, emp t2
WHERE t1.mgrid = t2.empid
Define candidate key, alternate key, composite key.

candidate key is one that can identify each row of a table uniquely. Generally a candidate key becomes the primary key of the table. If the table has more than one candidate key, one of them will become the primary key, and the rest are called alternate keys.

A key formed by combining at least two or more columns is called composite key.

What is database replication? What are the different types of replication you can set up in SQL Server?

Replication is the process of copying/moving data between databases on the same or different servers. SQL Server supports the following types of replication scenarios:

    * Snapshot replication
    * Transactional replication (with immediate updating subscribers, with queued updating subscribers)
    * Merge replication


GET EMPLOYEE SALARY DETAILS



select *from EMPTB e where 2=(select count(distinct salary) 
from EMPTB m where m.salary>e.salary)

What is the use of CASCADE CONSTRAINTS?  
we cant able to delete a record from the parent table if there is some corresponding record exists in the child table.

for this we are using cascading constraint. with the help of cascading constraint we can able to delete a record from the parent table as well as all the corresponding records from the child table.even we can set some null values.

Thursday, February 9, 2012

File Extension Return


 private string ReturnExtension(string fileExtension)
    {
        switch (fileExtension)
        {
            case ".htm":
            case ".html":
            case ".log":
                return "text/HTML";
            case ".txt":
                return "text/plain";
            case ".doc":
                return "application/ms-word";
            case ".tiff":
            case ".tif":
                return "image/tiff";
            case ".asf":
                return "video/x-ms-asf";
            case ".avi":
                return "video/avi";
            case ".zip":
                return "application/zip";
            case ".xls":
            case ".csv":
                return "application/vnd.ms-excel";
            case ".gif":
                return "image/gif";
            case ".jpg":
            case "jpeg":
                return "image/jpeg";
            case ".bmp":
                return "image/bmp";
            case ".wav":
                return "audio/wav";
            case ".mp3":
                return "audio/mpeg3";
            case ".mpg":
            case "mpeg":
                return "video/mpeg";
            case ".rtf":
                return "application/rtf";
            case ".asp":
                return "text/asp";
            case ".pdf":
                return "application/pdf";
            case ".fdf":
                return "application/vnd.fdf";
            case ".ppt":
                return "application/mspowerpoint";
            case ".dwg":
                return "image/vnd.dwg";
            case ".msg":
                return "application/msoutlook";
            case ".xml":
            case ".sdxl":
                return "application/xml";
            case ".xdp":
                return "application/vnd.adobe.xdp+xml";
            default:
                return "application/octet-stream";
        }
    }

Friday, February 3, 2012

To Get all Tables in Oracle With count


Select 'Select '''||table_name||' : ''||count(*) from '||table_name||';',
    to_char(sysdate, 'YYYYMMDDHH24MISS') d, user u
from user_tables
order by table_name


Tuesday, January 31, 2012

session not using in windows forms

Windows Form app are stateful application, therefore maintaince of state is not required. If you want to share information across forms then you can have a static class/ variables/ for the same.


What is Abstract Class?


Abstract class is a class that can not be instantiated, it exists extensively for inheritance and it must be inherited. There are scenarios in which it is useful to define classes that is not intended to instantiate; because such classes normally are used as base-classes in inheritance hierarchies, we call such classes abstract classes. 

Abstract classes cannot be used to instantiate objects; because abstract classes are incomplete, it may contain only definition of the properties or methods and derived classes that inherit this implements it's properties or methods. 

Static, Value Types & interface doesn't support abstract modifiers. Static members cannot be abstract. Classes with abstract member must also be abstract. 

Abstract classes are classes that contain one or more abstract methods (methods without implementation). An abstract method is a method that is declared, but doesn't contain implementation (like method declaration in the interface). Abstract classes can't be instantiated, and require subclasses to provide implementations for the abstract methods. This class must be inhertied. This class is mostly used as a base class.



Questions on Abstract class are very frequently asked in interviews:) Apart from interviews Abstract class is also very important to know when you are designing or working on a real time applications that needs proper design. I am not expert in this however trying to explain what I know out of my limited knowledge. This article tries to cover Abstract class, Abstract method, Abstract property and difference between abstract method and virtual method.

Abstract class is a class that can not be instantiated. To use it you need to inherit it. This class can be used as a base class where you can define certain method that must be implemented in derived class (Class that is going to inherit it) along with that you can also define certain methods that is frequently used and can be directly used by Derived class or overriden in the derived class if needed.
In the abstract class, you can define following:
  1. Normal property - this property is similar to any other property we define in a normal class
  2. Abstract property - this will have only get and set accessor but no implementation.
  3. Normal method - this method is similar to any other method that you define in a normal class
  4. Abstract method - this will not have any implementation
  5. Virtual method - this will have implementation but can also be overridden in the derived class to provide additional logic or completely replace its logic

Abstract Classes: Classes which cannot be instantiated. This means one cannot make a object of this class or in other way cannot create object by saying ClassAbs abs = new ClassAbs(); where ClassAbs is abstract class. 
Abstract classes contains have one or more abstarct methods, ie method body only no implementation. 
Interfaces: These are same as abstract classes only difference is we can only define method definition and no implementation. 
When to use wot depends on various reasons. One being design choice. 
One reason for using abstarct classes is we can code common 
functionality and force our developer to use it. I can have a complete 
class but I can still mark the class as abstract. 
Developing by interface helps in object based communication.
Abstract Class
In order to explain Abstract class, I am going to take a simple example of ParentClass class that has all methods and properties explained above and it looks like below
ParentClass.cs
using System;

using System.Data;

using System.Configuration;

using System.Web;

using System.Web.Security;

using System.Web.UI;

using System.Web.UI.WebControls;

using System.Web.UI.WebControls.WebParts;

using System.Web.UI.HtmlControls;

/// <summary>

/// Summary description for ParentClass

/// </summary>

public abstract class ParentClass

{
public ParentClass()
{

}
private int age = 0;
// Normal property
public int Age_Normal
{
get
{return age;
}
set
{age = value;
}
}
// Abstract property
public abstract string Address_Abstract
{
get;
set;
}

// Normal Methods
public string GetName(string firstName, string lastName)
{
return "My Name is : " + GetName_Virtual(firstName, lastName);
}
public int Divide_NotAbstract(int a, int b)
{
return a / b;
}
// Abstract Methods
public abstract int Add_Abstract(int a, int b);
public abstract int Subtract_Abstract(int a, int b);

// Virtual method
public virtual string GetName_Virtual(string firstName, string lastName)
{
return firstName + " " + lastName;
}
}
Get solutions of .NET problems with video explanations, .pdf and source code in .NET How to's.
As you can see, the first property I have above is Age_Normal, this is a normal property similar to other property that we define in the class that has its implementation as well and it can be simply accessed by the instance of the dervied class.
Next we have an Abstract property Address_Abstract that has only get and set accessor and no implementation.
Next we have normal method that is similar to any other method we define in a normal class.
Next we have Abstract methods that contains abstract keyword in its definition, this doesn't have any implementation. (Defining abstract methods and properties are similar to defining properties and methods in an interface). One thing to note is that an abstract methods or properties can only be defined in Abstract class, you can't define them in a normal class.
Next we have a virtual method that let us use its logic directly or also allow us to completely override its logic.
Derived Class
Below is my derived class (that is inheriting above abstract class), its name is DerivedClass (Some of the method or property names may look strange, I have just kept this for easy understanding, please bear with me).
DerivedClass.cs
using System;

using System.Data;

using System.Configuration;

using System.Web;

using System.Web.Security;

using System.Web.UI;

using System.Web.UI.WebControls;

using System.Web.UI.WebControls.WebParts;

using System.Web.UI.HtmlControls;

/// <summary>

/// Summary description for Calculate

/// </summary>

public class DerivedClass : ParentClass

{
private string address = string.Empty;
public DerivedClass()
{
}
// override the abstract property
public override string Address_Abstract
{
get
{return address;
}
set
{address = value;
}
}
// override the abstract method
public override int Add_Abstract(int a, int b)
{
return a + b;
}
public override int Subtract_Abstract(int a, int b)
{
return a - b;
}
// override virtual method
public override string GetName_Virtual(string firstName, string lastName)
{
return "(Overriden) Name: " + firstName + " " + lastName;
}
// hide normal method of the abstract class
public new string Divide_NotAbstract(int a, int b)
{
int d = a/b;
return "The division is: " + d.ToString();
}
// use abstract property to retrieve its value
public string GetAddress()
{
return Address_Abstract;
}
}
In the above class I am doing following:
First, I am overriding the Address_Abstract property, as I had declared it as Abstract property. Note that to implement the abstract property or method, you need to use override keyword.
Next, I am overriding two methods Add_Abstract and Sub_Abstract as these methods are declared as Abstract method.
Next I had declared GetName_Virtual method as virtual method so I have freedom to either override it or use it as it is. If we want to use the original method, we can choose not to override it but if we want to modify its logic then we will have to override it. I preferred to override so I have redefined my logic here.
Next I have made Divide_NoAbstract method of Parent class as hidden by specifying new keyword in the definition of derived class. Even if I have made the original parent class method as hidden if at some place we want to use the orginial abstract class method, we can use that, I will show you how to use that later.
How to use Derived and Abstract class methods
Below is the code that shows how to use above abstract class and derived class methods or properties.
DerivedClass c = new DerivedClass();

Response.Write("<b>Abstract method - Sum: </b>" + c.Add_Abstract(50, 30).ToString() + "<br />");

Response.Write("<b>Abstract method - Subtract: </b>" + c.Subtract_Abstract(50, 30).ToString() + "<br />");

Response.Write("<b>Virtual Method - GetName_Virtual: </b>" + c.GetName_Virtual("SHEO", "NARAYAN") + "<br />");

Response.Write("<b>Normal Public Method - GetName: </b>" + c.GetName("SHEO", "NARAYAN") + "<br />");

Response.Write("<b>Normal Public Method being hidden using new keyword - Divide_NotAbstract: </b>" + c.Divide_NotAbstract(50, 30).ToString() + "<br />");

ParentClass p = new DerivedClass();

Response.Write("<b>Normal Public Method from Abstract Class - Divide_NotAbstract: </b>" + p.Divide_NotAbstract(50, 30).ToString() + "<br />");

c.Address_Abstract = "Sheo Narayan, Hitec City, Hyderabad.";

Response.Write("<b>Normal Public method accessing <br />overriden Abstract Property - GetAddress: </b>" + c.GetAddress() + "<br />");
Above code snippet will give following results

Abstract method - Sum: 80
Abstract method - Subtract: 20
Virtual Method - GetName_Virtual: (Overriden) Name: SHEO NARAYAN
Normal Public Method - GetName: My Name is : (Overriden) Name: SHEO NARAYAN
Normal Public Method being hidden using new keyword - Divide_NotAbstract: The division is: 1
Normal Public Method from Abstract Class - Divide_NotAbstract: 1
Normal Public method accessing
overriden Abstract Property - GetAddress: 
Sheo Narayan, Hitec City, Hyderabad.


In the above code snippet, first I instantiated the DerivedClass method and start calling the both Abstract methods Add_Abstract and Subtract_Abstractthat were implemented in the Derived class.
Next I have called the Virtual method of the Abstract class that was overriden in the Derived class. You can see the implementation of GetName_Virtualmethod in the Derived class that is prefixing "(Overriden) Name" with the result.
Next line is calling a normal method GetName that was defined in the Parent abstract class.
Next line is calling the method Divide_NoAbstract that is hiding the main normal method Divide_NoAbstract of Parent class by specifying the new keyword in its definition in the derived class.
Now lets suppose, you already have made the Parent class method (as explained in the above line) hidden but still in a certain scenario, you want to call the Parent class method. To do that, we need to instantiate the Derived Class by specifying it as the ParentClass by writing it as ParentClass p = new DerivedClass();. This is giving me the reference of Parent class and when called Divide_NoAbstract method, this will call the Parent classDivide_NoAbstract method not Derived class method.
The very next line is setting the property of the Derived class that is nothing but the implementation of the ParentClass Address_Abstract property and calling the Derived class GetAddress method that is simply returning this property value.
Conclusion
In this article I tried to show a practical example of how to use Abstract class and what are differnt things that we can define into it. I also tried to show how to work with methods and properties of Abstract class in different scenarios. Hope this article will be useful for the readers. Please subscribefor the subsequent articles alert directly in your email. NOTE: This article was written in 3 breaks, please let me know if somewhere the continuation is broken. Thanks
http://www.dotnetfunda.com/interview/showcatquestion.aspx?category=42

D/f sql functions and stored procedures


we cant use DML operations in functions where as it is possible in sp.
Procedure can return zero or n values whereas function can return one value which is mandatory.
error handling can be done in sp, but not possible in function.
functions can be called from select statements, where clause and case but not possible in sp.
Procedures can have input,output parameters for it whereas functions can have only input parameters
Functions can be called from procedure whereas procedures cannot be called from function.
We can go for transaction management in procedure whereas we can't go in function.


1. Functions are compiled and executed at run time.
Stored procedures are stored in parsed and compiled format in the database.

2. Functions cannot affect the state of the database which means we cannot perform insert,delete,update and create operations on the database.
Stored Procedures can affect the state of the database by using insert,delete,update and create operations.

3 Functions are basically used to compute values. We passes some parameters to functions as input and then it performs some operations on the parameter and return output.
Stored procedures are basically used to process the task.

4.Function can not change server environment and our operating system environment.
Stored procedures can change server environment and our operating system environment.

5.Functions can not be invoked from SQL Statements. Execute. SELECT
operating system can be invoked from SQL Statements. Execute. SELECT

6.Functions can run an executable file from SQL SELECT or an action query.
operating system use Execute or Exec to run
 

Monday, January 30, 2012

Common Table Expressions(CTE)


If you think only .NET languages are the only thing that is getting evolved day by day while SQL Syntaxes remains constant, you might have to think it again. SQL Server is gradually coming with evolution day by day with some alternative syntaxes of writing SQL Statements to make more complex SQL statements easier to understand and maintainable in form of CTE or Common Table Expressions. In this post I will try to cover some of the benefits that we get when working with CTE.

When dealing with Sub-queries, it is often required that you need to select a part of the data from a sub query or even join data from a query with some other tables. In that case either you have option to name your sub-queries with an alias or to use it directly. Gradually your requirement is getting more and more complex and your query would look unmaintainable at any time. CTE allows you to define the subquery at once, name it using an alias and later call the same data using the alias just like what you do with a normal table. CTE is standard ansi sql standard.
Say for instance you have a query like this :
SELECT * FROM  (
 SELECT A.Address, E.Name, E.Age From Address A
 Inner join Emp E on E.EID = A.EID) T
WHERE T.Age > 50
ORDER BY T.NAME
The query looks really a mess. Even if I need to write something that wraps around the entire query, it would gradually become unreadable. CTE allows you to generate Tables beforehand and use it later when we actually bind the data into the output.
Rewriting the query using CTE expressions would look like :
With T(Address, Name, Age)  --Column names for Temporary table
AS
(
SELECT A.Address, E.Name, E.Age from Address A
INNER JOIN EMP E ON E.EID = A.EID
)
SELECT * FROM T  --SELECT or USE CTE temporary Table
WHERE T.Age > 50
ORDER BY T.NAME
Yes as you can see, the second query is much more readable using CTE. You can specify as many query expressions as you want and the final query which will output the data to the external environment will eventually gets reference to all of them. For instance :
With T1(Address, Name, Age)  --Column names for Temporary table
AS
(
SELECT A.Address, E.Name, E.Age from Address A
INNER JOIN EMP E ON E.EID = A.EID
),
T2(Name, Desig)
AS
(
SELECT NAME, DESIG FROM Designation)
SELECT T1.*, T2.Desig FROM T1  --SELECT or USE CTE temporary Table
WHERE T1.Age > 50 AND T1.Name = T2.Name
ORDER BY T1.NAME
So the queries are separated using commas. So basically you can pass as many query as you want and these queries will act as a subqueries, getting you the data and name it as a temporary table in the query.
According to the syntax, the CTE starts with a With clause. You can specify the column names in braces, but it is not mandatory.
Lets create one cool CTE for you finally :
WITH ShowMessage(STATEMENT, LENGTH)
AS
(
SELECT STATEMENT = CAST('I Love ' AS VARCHAR(150)), LEN('I Love ')
UNION ALL
SELECT
 CAST(STATEMENT + 'DAILY DOTNET TIPS! ' AS VARCHAR(150))
 , LEN(STATEMENT) FROM ShowMessage
WHERE LENGTH < 100
)
SELECT STATEMENT, LENGTH FROM ShowMessage
So this will produce something like this :

Please Note: Always consider starting your CTE with a semicolon when you have more than one query in a batch to ensure the with statement is not considered as a part of the previous query.

Friday, January 27, 2012

Writing a Stored Procedure


Virtually all ASP.NET applications of interest work with database data at some level, and one of the most common databases used in ASP.NET applications is Microsoft's ownSQL Server database. With relational databases like SQL, commands are issued through the SQL syntax, which includes SELECTINSERTUPDATE, and DELETE statements, among others. One way to issue a command to a database from an ASP.NET application is to craft the SQL query in the application itself. Such queries are often called ad-hoc queries. The primary downside of ad-hoc queries is that they are hard to maintain - if you need to change your query you need to edit the string in your application, recompile, and redeploy.
A better approach, in my opinion, is to use stored procedures. Stored procedures are pre-compiled functions that reside on the database server that can be invoked by name. This is similar to compartmentalizing programmatic functionality into methods. Stored procedures are not only more updateable than their ad-hoc counterpart, but also can be utilized by other applications. For example, you might have both an ASP.NET application and a Web services application that is driven on data from the same database. If you hard code your SQL queries in your source code, any changes will now require modifications in two places (as well as two places that now require recompilation and redeployment). However, by using stored procedures there's a single point that needs modification. (The debate between stored procedures and ad-hoc queries has been done in much greater detail in other venues; see Rob Howard's blog entry Don't use stored procedures yet? Must be suffering from NIHS (Not Invented Here Syndrome) for a pro-stored procedures slant, and Frans Bouma's entry Stored Procedures are Bad, M'Kay? for a look at why stored procedures aren't the end-all answer.)
Stored procedures typically return resultsets, such as the results of a SELECT query. However, there are times when you may be getting back just scalar data from a stored procedure. For example, you might have a stored procedure that returns just the account balance for a particular customer, or one that returns the average age of all users in your database. When calling a stored procedure that INSERTs a new record into a table with an IDENTITY field, you may want to get back the ID for the newly inserted row.
There are a couple of ways to get back scalar data from a SQL Server stored procedure. In this article we'll look at these various techniques along with how to fetch back the returned data in your ASP.NET code. Read on to learn more!

Returning Data with a SELECT Statement


Typically data is returned from a stored procedure using a SELECT statement, and typically the data returned is a resultset, consisting of multiple fields and records. For example, a stored procedure might be created to get all products in inventory, which might be accessed through the SQL query:
CREATE PROCEDURE store_GetInventory AS

SELECT InventoryID, ProductName, Price, UnitsOnStock
FROM store_Inventory

However, there's no reason why you can't return a simple scalar value. For example, if you were interested in the average price of all items in inventory - i.e., just a simple number, like $11.92 - you could return this scalar data using a SELECT statement:

CREATE PROCEDURE store_GetAverageInventoryPrice AS

SELECT AVG(Price) AS AveragePrice
FROM store_Inventory

Similarly, in stored procedures that insert a new record into a table that has an IDENTITY field, you can get the ID value of the newly inserted record through theSCOPE_IDENTITY() function. So, after INSERTing the new record you can simply return the value like so:

CREATE PROCEDURE store_AddNewInventoryItem
(
 @ProductName nvarchar(50),
 @Price   money
) AS

-- INSERT the new record
INSERT INTO store_Inventory(ProductName, Price)
VALUES(@ProductName, @Price)

-- Now return the InventoryID of the newly inserted record
SELECT SCOPE_IDENTITY()

When returning scalar through a SELECT statement you can retrieve the data using the exact same technique used to retrieve a resultset. That is, you can, if you want, use a DataReader, DataTable, or DataSet. The only thing to keep in mind is that you're results will contain only one row with only one field. The following code would call thestore_GetAverageInventoryPrice and grab back the scalar result:

Dim myConnection as New SqlConnection(connection string)
myConnection.Open()

Dim myCommand as New SqlCommand("store_GetAverageInventoryPrice", myConnection)
myCommand.CommandType = CommandType.StoredProcedure

Dim reader as SqlDataReader = myCommand.ExecuteReader()

'Read in the first record and grab the first column
Dim avgPrice as Decimal
If reader.Read() Then
  avgPrice = Convert.ToDouble(reader("AveragePrice"))
End If

This is a bit of overkill, though, thanks to the DataCommand's ExecuteScalar() method. The ExecuteScalar() method can be used in place of the ExecuteReader(), the difference being ExecuteScalar() returns a single Object instance as opposed to a DataReader. Using ExecuteScalar() the code would be simplified to:

Dim myConnection as New SqlConnection(connection string)
myConnection.Open()

Dim myCommand as New SqlCommand("store_GetAverageInventoryPrice", myConnection)
myCommand.CommandType = CommandType.StoredProcedure

Dim avgPriceObject as Decimal = Convert.ToDecimal(myCommand.ExecuteScalar())

(The above omits a check to see if the result is NULL. If there were no rows in store_Inventory or no rows with a non-NULL Price, the returned Object would be equal toDBNull.Value. Ideally you would either add such a check to the above code or edit the stored procedure to use ISNULL to convert any NULL result into a number (i.e., SELECT ISNULL(AVG(Price), 0.0) ...).)
While the SELECT method just discussed provides an easy way to return a scalar value from a stored procedure it only works if the scalar value is the sole piece of data you want to return from the stored procedure. There are times, however, where you want to return a full resultset from the stored procedure along with some scalar value. The remaining two approaches we'll be looking at in this article address how to accomplish this feat.
Using Output Parameters One way to retrieve scalar data in addition to a standard resultset from a stored procedure is to use one or more output parameters. An output parameter is a parameter that is passed into the SQL stored procedure, but whose value can be set in the stored procedure. This assigned parameter, then, is readable back from the application that called the stored procedure.
To use an output parameter you need to indicate that the parameter is intended for output via the OUTPUT keyword. The following snippet shows a stored procedure that returns the set of inventory items through a SELECT statement and uses an output parameter to return the average price:

CREATE PROCEDURE store_GetInventoryWithAveragePrice
(
 @AveragePrice money OUTPUT
)
AS

SET @AveragePrice = (SELECT AVG(Price) FROM store_Inventory)

SELECT InventoryID, ProductName, Price, UnitsOnStock
FROM store_Inventory

To access the value of an output parameter from your ASP.NET application you need to create a parameter object whose Direction property is set to Output. After you call the stored procedure the output parameter's value is accessible through the Value property, as the following code illustrates:

Dim myConnection as New SqlConnection(connection string)
myConnection.Open()

Dim myCommand as New SqlCommand("store_GetInventoryWithAveragePrice", myConnection)
myCommand.CommandType = CommandType.StoredProcedure

'Create a SqlParameter object to hold the output parameter value
Dim avgPriceParam as New SqlParameter("@AveragePrice", SqlDbType.Money)

'IMPORTANT - must set Direction as Output
avgPriceParam.Direction = ParameterDirection.Output

'Finally, add the parameter to the Command's Parameters collection
myCommand.Parameters.Add(avgPriceParam)

'Call the sproc...
Dim reader as SqlDataReader = myCommand.ExecuteReader()

'Now you can grab the output parameter's value...
Dim avgPrice as Decimal = Convert.ToDecimal(avgPriceParam.Value)

(The same issue regarding NULLs applies here as in the previous example...)
You are not limited to a single output parameter; additionally, you can have stored procedures with both input and output parameters.

Using a Return Value


The final technique I want to talk about for returning scalar values from a stored procedure is using return values. Whenever a stored procedure finishes executing, it always returns a return value. This return value is, by default, 0. You can use the RETURN statement yourself, however, to return a scalar integer value. For example, let's revisit thestore_AddNewInventoryItem, but modify it to return the ID of the newly inserted row as a return value.
CREATE PROCEDURE store_AddNewInventoryItem
(
 @ProductName nvarchar(50),
 @Price   money
) AS

-- INSERT the new record
INSERT INTO store_Inventory(ProductName, Price)
VALUES(@ProductName, @Price)

-- Now return the InventoryID of the newly inserted record
RETURN SCOPE_IDENTITY()

Note that the SCOPE_IDENTITY() value is being return via a RETURN statement now, whereas in the earlier example we used a SELECT.
To retrieve the return value from a stored procedure use the same technique as with output parameters, the only difference being that you should use a Direction value ofReturnValue, as the following code snippet illustrates:

Dim myConnection as New SqlConnection(connection string)
myConnection.Open()

Dim myCommand as New SqlCommand("store_GetInventoryWithAveragePrice", myConnection)
myCommand.CommandType = CommandType.StoredProcedure

'Create a SqlParameter object to hold the output parameter value
Dim retValParam as New SqlParameter("@RETURN_VALUE", SqlDbType.Int)

'IMPORTANT - must set Direction as ReturnValue
retValParam.Direction = ParameterDirection.ReturnValue

'Finally, add the parameter to the Command's Parameters collection
myCommand.Parameters.Add(retValParam)

'Call the sproc...
Dim reader as SqlDataReader = myCommand.ExecuteReader()

'Now you can grab the output parameter's value...
Dim retValParam as Integer = Convert.ToInt32(retValParam.Value)

That's all there is to it! As I mentioned earlier, you can only return integer values through the stored procedure's return type.

Conclusion


In this article we examined three ways to pass back scalar data from a stored procedure, along with the necessary code to process the returned value. You can use a SELECTstatement, output parameter, or return value (assuming you want to pass back an integer value). When returning a scalar value via a SELECT statement you can read the resulting value using the ExecuteScalar() method. For output parameters and return values you need to create a parameter object with the proper Direction property value. Then, after you call the stored procedure, you can access the retrieved value through the parameter's Value property.