To retrieve 2nd Highest salary from table :
| |
|
|
| |
SELECT TOP 1 SALARY FROM EMP AS [MIN SALARY]
WHERE SALARY IN (SELECT TOP 2 SALARY FROM EMP ORDER BY SALARY DESC)ORDER BY SALARY ASC |
|
| |
OR |
|
| |
SELECT MIN(SALARY)AS [MIN SALARY] FROM EMP
WHERE SALARY IN (SELECT DISTINCT TOP 2 SALARY FROM EMP ORDER BY SALARY DESC) |
|
Eliminate Duplicate records
| |
CREATE TABLE Table1 (TableID INT, TableName VARCHAR2(10))
SELECT DISTINCT * INTO Table2
FROM Table1
DELETE Table1
INSERT INTO Table1 SELECT * FROM Table2
SELECT * FROM Tabel1
|
|
| |
What Triggers are Triggers are a special type of procedure that are fired automatically when an query is executed on a table or a view. Triggers provide a powerful way of control how action queries modify the data in your database. With the triggers you can enforce design rules, implement business logic and prevent data inconsistency with a flexibility that cant be duplicated any other way. Trigger Creation The CREATE TRIGGER statement provides for two types of triggers: AFTER triggers and INSTEAD OF triggers. Both types of triggers can be defined to fire for an insert, update, or delete operation. If an action query has an AFTER trigger, the trigger fires after the successful completion of action query. If an action query has an INSETEAD OF trigger the trigger is fired instead of the action query. In other words the action query is never executed.
| |
CREATE TRIGGER trigger_name ON {table_name|view_name} [WITH ENCRYPTION] {FOR|AFTER|INSTEAD OF} [INSERT] [,] [UPDATE] [,] [DELETE] As sql_statements |
FOR is same as AFTER but it is for backward compatibility. Each trigger is associated with the table or view names in the ON clause. Although each trigger is associated with a single table or view, a single table can have many number of AFTER triggers. Since two or more triggers on table can cause confusion to manage and to debug however its better to place all the related code in one trigger for each action. A view can’t have AFTER triggers.
| |
CREATE TRIGGER Authors_INSERT_UPDATE ON Auhtors AFTER INSERT,UPDATE AS UPDATE Authors SET ln_name = UPPER(ln_name) WHERE Author_id in (SELECT author_id from Inserted) |
The CREATE TRIGGER statement in the above example defines an AFTER trigger for the Authors table. In this case the trigger fires after an insert or update operation is performed on the table. If you closely observe in the trigger body we have used a sub query and a table named Inserted in from clause, this is a special table that’s created by SQL Server during an insert operation. It contains the rows that are being inserted into the table. This table exists while the trigger is executing, you can only refer to it in the trigger code. In addition to the inserted table you have one more table i.e. deleted which contains the information about the rows deleted. These tables are called Magic tables.
An AFTER trigger fires after the action query is executed. If the action query causes an error, the AFTER trigger never fires. AFTER triggers can be used to enforce referential integrity.
An INSTEAD of trigger can be associated with a table or view. However INSTEAD OF triggers are used most often to provide better control of updatable views.
INSTEAD OF trigger is executed instead of the action query that causes it to fire. Because the action query is never executed, the trigger typically contains code that performs the operation. Each table or view can have only one INSTEAD OF trigger for each type of action.
How to delete or Change a Trigger
To change the definition of a trigger you can use ALTER TRIGGER or else to drop trigger use DROP TRIGGER.
The syntax of the DROP triggers statement. DROP TRIGGER trigger_name [,…] The syntax of the ALTER TRIGGER statement
| |
ALTER TRIGGER trigger_name ON {table_name|view_name} [WITH ENCRYPTION] {FOR|AFTER|INSTEAD OF} [INSERT] [,] [UPDATE] [,] [DELETE] As sql_statements |
Use the triggers very efficiently otherwise it will end up with Performance issues.
|
It is a way to retrieve data form the database. Here are some frequently asked queries in SQL Server.
1)DELETING DUPLICATE RECORDS FORM THE TABLE
| |
/*
Suppose there are multiple duplicate records in the [ERecords] table whose structure is as follows
[ERecords](Ename Varchar Type, ESal Money Type)
Follow all the steps one by one
Step 1 -->
Adding a Column in [ERecords]
*/
Alter Table [ERecords]
Add [Id] Int Identity(1,1)
/*
Step 2 -->
Deleting duplicate records from [ERecords]
You should include all columns of the table [ERecords] in the 'Group By' Clause accept [Id]
*/
Delete From [ERecords]
Where [Id] Not In (Select Min([Id]) From [ERecords] Group By [Ename],[ESal])
/*
Step 3 -->
Droping the newly added column.
*/
Alter Table [ERecords]
Drop Column [Id]
/*
Step 4 -->
Testing the table for after the execution.
*/
Select * From [ERecords] /* Now the table [ERecords] contains the unique records.*/ |
2)GETTING THE Nth HIGHEST SALARY/NUMBERS ETC. ALONG WITH THE DETAILS :
| |
/*
Suppose there are multiple records in the [ERecords] table whose structure is as follows
[ERecords]( EId Int Type Primary Key, Ename Varchar Type, ESal Money Type )
First Way Is as (But feasible for only SQL Server)-->
Change the number after Keyword Top as 1,2,3... and get the
First, Second, Third .... Highest Salary respectively.
*/
Select * From [ERecords] Where [ESal] In
(Select Min([ESal]) From [ERecords] Where [ESal] In
(Select Distinct Top 2 [ESal] From [ERecords] Order By [ESal] Desc))
/*
Second Way Is as (Generalized for all Databases)-->
Change the number after Keyword Where in Inner Query
as 1,2,3... and get the First, Second, Third .... Highest Salary respectively.
e.g. (3 = (Select Count(Distinct ESal).... )
(1 = (Select Count(Distinct ESal).... ) etc.
*/
Select * From [ERecords] [E1] Where
(2 = (Select Count(Distinct [ESal]) From [ERecords] [E2] where [E1].[ESal] <= [E2].[ESal])) |
3) CREATING ROWNUM IN THE SQL QUERY IN SQL SERVER I.E. VIEWING SEQUENTIALLY NUMBERED RECORDS :
| |
/*
Suppose there are multiple records in the [ERecords] table whose structure is as follows
[ERecords]( EId Int Type Primary Key, Ename Varchar Type, ESal Money Type)
Limitations: The Table should have at least one unique column to compare in where clause of the inner query
*/
-- Creating RowNum In Ascending Order
Select RowNum = (Select Count(1) From [ERecords] Where [EId] <= [E].[EId]),
[E].[Ename], [E].[ESal]
From [Erecords] [E]
-- Creating RowNum In Descending Order
Select RowNum = (Select Count(1) From [ERecords] Where [EId] >= [E].[EId]),
[E].[Ename], [E].[ESal]
From [Erecords] [E]
|
4) RETRIEVING Nth ROW FROM THE TABLE :
| |
/*
Suppose there are multiple records in the [ERecords] table whose structure is as follows
[ERecords]( EId Int Type Primary Key, Ename Varchar Type, ESal Money Type)
Limitations : The Table should have at least one unique column to compare in where clause of the inner query
Change the number after Keyword Where in Inner Query
as 1,2,3... and get the First, Second, Third .... number of row respectly.
e.g. (2 = ((Select Count(1) From [ERecords] .... )
(4 = ((Select Count(1) From [ERecords] .... ) etc.
*/
Select *
From [Erecords] [E]
Where 2 = (Select Count(1) From [ERecords] Where [Eid] <= [E].[Eid])
|
5) RETRIEVING EVEN OR ODD NUMBER OF ROWS FROM THE TABLE :
| |
/*
Suppose there are multiple records in the [ERecords] table whose structure is as follows
[ERecords]( EId Int Type Primary Key, Ename Varchar Type, ESal Money Type)
Limitations : The Table should have at least one unique column to compare in where clause of the inner query
*/
-- For Even number of records
Select RowNum = (Select Count(1) From [ERecords] Where [EId] <= [E].[EId]),
[E].[EId], [E].[Ename], [E].[ESal]
From [Erecords] [E]
Where 0 = ((Select Count(1) From [ERecords] Where [Eid] <= [E].[Eid]) % 2)
-- For Odd number of records
Select RowNum = (Select Count(1) From [ERecords] Where [EId] <= [E].[EId]),
[E].[EId], [E].[Ename], [E].[ESal]
From [Erecords] [E]
Where 1 = ((Select Count(1) From [ERecords] Where [Eid] <= [E].[Eid]) % 2) |
6) PICKING OUT RENDOM DATA FORM THE TABLE FOR GAMBLING :
| |
/*
Suppose there are multiple records in the [ERecords] table whose structure is as follows
[ERecords]( EId Int Type Primary Key, Ename Varchar Type, ESal Money Type)
The query will pick random Ename form the table.
*/
Select Top 1 [EName] From [ERecords] Order By NewId() |
Join Fundamentals
Join Fundamentals...
By using joins, you can retrieve data from two or more tables based on logical relationships between the tables. Joins indicate how Microsoft® SQL Server™ 2000 should use data from one table to select the rows in another table.
A join condition defines the way two tables are related in a query by:
• Specifying the column from each table to be used for the join. A typical join condition specifies a foreign key from one table and its associated key in the other table.
• Specifying a logical operator (=, <>, and so on) to be used in comparing values from the columns.
Joins can be specified in either the FROM or WHERE clauses. The join conditions combine with the WHERE and HAVING search conditions to control the rows that are selected from the base tables referenced in the FROM clause.
Specifying the join conditions in the FROM clause helps separate them from any other search conditions that may be specified in a WHERE clause, and is the recommended method for specifying joins. A simplified SQL-92 FROM clause join syntax is:
FROM first_table join_type second_table [ON (join_condition)]
join_type specifies what kind of join is performed: an inner, outer, or cross join. join_condition defines the predicate to be evaluated for each pair of joined rows. This is an example of a FROM clause join specification:
| |
FROM Suppliers JOIN Products
ON (Suppliers.SupplierID = Products.SupplierID)
This is a simple SELECT statement using this join:
SELECT ProductID,
Suppliers.SupplierID,
CompanyName
FROM Suppliers JOIN Products
ON (Suppliers.SupplierID = Products.SupplierID)
WHERE UnitPrice > $10
AND CompanyName LIKE N'F%'
GO
|
The select returns the product and supplier information for any combination of parts supplied by a company for which the company name starts with the letter F and the price of the product is more than $10.
Using Joins
Join conditions can be specified in either the FROM or WHERE clauses; specifying them in the FROM clause is recommended. WHERE and HAVING clauses can also contain search conditions to further filter the rows selected by the join conditions.
Joins can be categorized as:
• Inner joins (the typical join operation, which uses some comparison operator like = or <>). These include equi-joins and natural joins.
Inner joins use a comparison operator to match rows from two tables based on the values in common columns from each table. For example, retrieving all rows where the student identification number is the same in both the students and courses tables.
• Outer joins. Outer joins can be a left, a right, or full outer join.
Outer joins are specified with one of the following sets of keywords when they are specified in the FROM clause:
• LEFT JOIN or LEFT OUTER JOIN
The result set of a left outer join includes all the rows from the left table specified in the LEFT OUTER clause, not just the ones in which the joined columns match. When a row in the left table has no matching rows in the right table, the associated result set row contains null values for all select list columns coming from the right table.
• RIGHT JOIN or RIGHT OUTER JOIN.
A right outer join is the reverse of a left outer join. All rows from the right table are returned. Null values are returned for the left table any time a right table row has no matching row in the left table.
• FULL JOIN or FULL OUTER JOIN.
A full outer join returns all rows in both the left and right tables. Any time a row has no match in the other table, the select list columns from the other table contain null values. When there is a match between the tables, the entire result set row contains data values from the base tables.
• Cross joins.
Cross joins return all rows from the left table, each row from the left table is combined with all rows from the right table. Cross joins are also called Cartesian products.
.NET Classes used :
|
| |
rnum=Math.round(Math.random() * 100000); document.write(''); |
Stored ProceduresWhat Stored Procedure means
A Stored procedure is a database object that contains one or more SQL statements. In this article you will get an idea on how to create and use stored procedures and also highlighted on how to use stored procedure.
The first time a stored procedure is executed; each SQL statement it contains is compiled and executed to create an execution plan. Then procedure is stored in compiled form with in the database. For each subsequent execution, the SQL statements are executed without compilation, because they are precompiled. This makes the execution of stored procedure faster than the execution of an equivalent SQL script.
To execute a stored procedure you can use EXEC statement.
| |
CREATE PROC spGetAuthors AS SELECT * FROM AUTHORS |
When you run this script in Pubs database you will get the following message in Query Analyzer.
The Command(s) completed successfully.
Now you are ready to call/execute this procedure from Query Analyzer.
This stored procedure creates a result set and returns to client.
You can call a stored procedure from within another stored procedure. You can even call a stored procedure from within itself. This technique is called a recursive call in programming. One of the advantages of using stored procedures is that application programmers and end users don’t need to know the structure of the database or how to code SQL. Another advantage of it is they can restrict and control access to a database.
Now days every one is familiar with SQL Injection Attack I think stored are the way this can be prevented from this malicious attack.
How to Create a Stored Procedure
When the CREATE PROCEDURE statement is executed, the syntax of the SQL statements within the procedure is checked. If you have made a coding error the system responds with an appropriate message and the procedure is not created.
The Syntax of the CREATE PROCEDURE statement
| |
CREATE {PROC|PROCEDURE} Procedure_name [Parameter_declaration] [WITH {RECOMPILE|ENCRYPTION|RECOMPILE, ENCRYPTION}] AS sql_statements
|
You can use CREATE PROCEDURE statement to create a stored procedure in the database. The name of the stored procedure can be up to 128 characters and is typically prefixed with the letters sp.
If you look at the above options like AS, RECOMPILE, ENCRYPTION these are having some significance meaning to it.
The AS clause contains the SQL statements to be executed by the stored procedure. Since a stored procedure must consist of single batch.
Recompile is used when you want to compile the stored procedure every time when you call. This comes into the picture when one doesn’t want to catch the execution plan of stored procedure in the database. Encryption implies that you want to hide this code so that no one can see it. This is very important when you want to distribute the code across the globe or when you want to sell this code to other vendors. But make sure you have original copy it; because once you encrypted it no one can decrypt it.
Apart from the stored procedure that store in the database a permanent entity you can create stored procedure as per you session. That means as long the as the session is alive then the stored procedure is available in the memory means in the database.
Once the session ends the stored procedure is vanished this actually depends on what type of stored procedure you have chosen to create it.
Stored procedure provide for two different types of parameters: input parameters and Output Parameters. An input Parameter is passed to the stored procedure from the calling program. An output parameter is returned to the calling program from the stored procedure. You can identify an output parameter with the OUTPUT keyword. If this keyword is omitted the parameter is assumed to be an input parameter.
You can declare an input parameter so that it requires a value or so that its value is optional. The value of a required parameter must be passed to the stored procedure from the calling program on an error occurs. The value of an optional parameter doesn’t need to be passed from the calling program. You identify an optional parameter by assigning a default value to it. Then if a value isn’t passed from the calling program, the default value is used. You can also use output parameter as input parameters. That is you can pass a value from the calling program to the stored procedure through an output parameter. However is not advisable to pass parameters to Output parameters.
The syntax for declaring the parameters
| |
Create procedure statement that uses an optional parameter.
CREATE PROC spGetAuthors @LastName varchar(50), @FirstName varchar(50) = ‘vijay’
AS SELECT @LastName= ln_Name FROM AUTHORS WHERE fn_name = @FirstName
|
A stored procedure can declare up to 2100 parameters. If you declare two or more parameters, the declarations must be separated by commas.
Calling stored procedure with Parameters
To pass parameter values to a stored procedure, you code the values in the EXEC statement after the procedure name. You can pass the parameters either by position or by name.
Passing parameters by Name:
Write the following code in Query Analyzer
| |
DECLARE @LN VARCHAR(100) EXEC spGetAuthors @FirstName = ‘krishna’, @LastName = @LN OUTPUT |
Passing parameters by Position:
| |
DECLARE @LN VARCHAR(100) EXEC spGetAuthors @LN OUTPUT, ‘krishna’ |
In fact you can use both notations to pass parameters to stored procedures when you are calling. To pass parameters by position, list them in the same order as they appear in the CREATE PROCEDURE statement and separate them with commas. When you use this technique, you can omit optional parameters only if they are declared after any required parameters.
To use an output parameter in the calling program, you must declare a variable to store its value. Then you use the name of the variable in the EXEC statement and you code the OUTPUT keyword after it to identify it as an output parameter.
Handling error in stored procedure
In addition to passing output parameters back to the calling program, stored procedures also pass back a return value. By default, this value is zero. If an error occurs during the execution of a stored procedure you may want to pass a value back to the calling environment that indicates the error that occurred. To do that you use the RETURN statement and the @@ERROR function.
The @@ERROR system function returns the error number that’s generated by the execution of the most recent SQL statement. If the value is zero, it means that no error has occurred. The stored procedure listed below uses this function to test whether a DELETE statement that deletes a row from authors table is successful.
CREATE PROC spDeleteAuthors @FirstName varchar(50)
As DECLARE @ErrorVar int
DELETE FROM AUTHORS WHERE fn_name = @FirstName
SET @ErrorVar = @ERROR IF @ErrorVar <> 0
BEGIN PRINT ‘An Unknown Error Occurred’ RETURN @ErrorVar END
RETURN statement immediately exists the procedure and returns an optional integer value to the calling environment. If you don’t specify the value in this statement the return value is zero.
How to delete or change a stored procedure
You use DROP PROC statement to delete one or more stored procedures from database. To redefine the stored procedure you use ALTER PROC.
The syntax of the DROP PROC statement
DROP {PROC|PROCEDURE} Procedure_name [, …] The syntax of the ALTER PROC statement
ALTER {PROC|PROCEDURE} Procedure_name [Parameter_declaration] [WITH {RECOMPILE|ENCRYPTION|RECOMPILE, ENCRYPTION}]
AS sql_statements
When you delete a procedure any security permission that are assigned to the procedure are also deleted. In that case you will want to use the ALTER PROC statement to modify the procedure and preserve permissions.
I will discuss the System stored procedure and other types of stored procedures in the Next article.
|
How to delete the rows which are duplicate (don’t delete both duplicate records).?
SET ROWCOUNT 1
DELETE yourtable FROM yourtable a WHERE (SELECT COUNT(*) FROM yourtable b
WHERE b.name1 = a.name1 AND b.age1 = a.age1) > 1 WHILE @@rowcount > 0
DELETE yourtable FROM yourtable a WHERE (SELECT COUNT(*) FROM yourtable b
WHERE b.name1 = a.name1 AND b.age1 = a.age1) > 1
SET ROWCOUNT 0 ?
Find top salary among two tables
SELECT TOP 1 sal FROM (SELECT MAX(sal) AS sal FROM sal1 UNION SELECT MAX(sal) AS sal FROM sal2) a ORDER BY sal DESC
How to find nth highest salary?
SELECT TOP 1 salary FROM (SELECT DISTINCT TOP n salary FROM employee ORDER BY salary DESC) a ORDER BY salary
How to know how many tables contains "Col1" as a column in a database?
SELECT COUNT(*) AS Counter FROM syscolumns WHERE (name = 'Col1')
Type of Joins :
have used the following 2 tables Employee and Department as examples.
Employee Table :- Department Table:-
EmployeeID EmployeeName DepartmentID DepartmentID DepartmentName
1 Smith 1 1 HR
2 Jack 2 2 Finance
3 Jones 2 3 Security
4 Andrews 3 4 Sports
5 Dave 5 5 HouseKeeping
6 Jospeh 6 Electrical
************************************************************************************************
Inner Join
An Inner Join will take two tables and join them together based on the values in common columns ( linking field ) from each table.
Example 1 :- To retrieve only the information about those employees who are assinged to a department.
Select Employee.EmployeeID,Employee.EmployeeName,Department.DepartmentName
From Employee Inner Join Department on Employee.DepartmentID = Department.DepartmentID
The ResultSet will be :-
EmployeeID EmployeeName DepartmentName
1 Smith HR
2 Jack Finance
3 Jones Finance
4 Andrews Security
5 Dave HouseKeeping
Example 2:- Retrieve only the information about departments to which atleast one employee is assigned.
Select Department.DepartmentID,Department.DepartmentName
From Department Inner Join Employee on Employee.DepartmentID = Department.DepartmentID
The ResultSet will be :-
DepartmentID DepartmentName
1 HR
2 Finance
3 Security
5 HouseKeeping
************************************************************************************************
Outer Joins :-
Outer joins can be a left, a right, or full outer join.
Left outer join selects all the rows from the left table specified in the LEFT OUTER JOIN clause, not just the ones in which the joined columns match.
Example 1:- To retrieve the information of all the employees along with their Department Name if they are assigned to any department.
Select Employee.EmployeeID,Employee.EmployeeName,Department.DepartmentName From Employee LEFT OUTER JOIN Department on Employee.DepartmentID = Department.DepartmentID
The ResultSet will be :-
EmployeeID EmployeeName DepartmentName
1 Smith HR
2 Jack Finance
3 Jones Finance
4 Andrews Security
5 Dave HouseKeeping
6 Jospeh
Right outer join selects all the rows from the right table specified in the RIGHT OUTER JOIN clause, not just the ones in which the joined columns match.
Example 2:- use Right Outer join to retrieve the information of all the departments along with the detail of EmployeeName belonging to each Department, if any is available.
Select Department.DepartmentID,Department.DepartmentName,Employee.EmployeeName
From Employee Outer Join Department on Employee.DepartmentID = Department.DepartmentID
The ResultSet will be :-
DepartmentID DepartmentName EmployeeName
1 HR Smith
2 Finance Jack
2 Finance Jones
3 Security Andrews
4 Sports NULL
5 HouseKeeping Dave
6 Electrical NULL
This query will result in Null value for Employee Name where no Employee is assigned to that department.
The advantages of stored procedures, in order by importance, are:
· Procedures are executed on the server so messages don't need to go back and forth to the client during the time the procedure is executed.
· Procedures are parsed once, and the result of the parsing is stored persistently, so there's no need to reparse for every execution.
· Procedures are in the catalog so they are retrievable, and procedures are subject to security provisions, in the same way as other SQL data.
· Procedures are in one place so code sharing is easy, and when changes happen there's no need to send code changes to clients.
Less Traffic
Stored procedures mean less message traffic between clients and servers. The client must send some sort of message to initiate the procedure, and the procedure must return some sort of result when the procedure is over, but that's all—no message passing occurs within the procedure. So a stored procedure that contains [n] statements will need only two messages, while an ODBC application that contains [n] statements will need (2 * n) messages. This factor is significant because a message takes at least a few milliseconds (on a TCP/IP connection to the same computer), and most likely a few centiseconds (on a LAN), or even a few deciseconds (on a WAN or Internet connection). Against this, you must set the cost of loading a stored procedure from disk the first time, which takes a few milliseconds. Calculating these factors together, we can say that stored procedures are faster than direct passing of SQL statements when (a) more than two SQL statements in the stored procedure are executed, and (b) the stored procedure is accessed so frequently that it might already be in the operating system's cache.
A stored procedure is not the only thing that leads to less traffic. You can sometimes achieve similar effects with views or constraints. And you must remember that not all application programs need messages over a network—they could be Common Gateway Interface (CGI) programs called by the application server.
A message is not the same thing as a fetch. Beware of twaddle like "if a database has many rows, then stored procedures are good because the excess rows won't be transferred over the network for processing"—the rows won't be transferred anyway if you use WHERE clauses in your SQL statements! However, data-change (INSERT/UPDATE/DELETE) statements can cause useless messages if the DBMS feels obliged to return the number of rows that were changed. That's an SQL Standard requirement, but only Microsoft does it, and the message can be suppressed by telling Microsoft: SET NOCOUNT ON.
At the start of this section, we gave you four advantages to using stored procedures. Traffic reduction is more important than the other three combined. If you're on a network, you need stored procedures, and traffic reduction is the reason.
Semi precompilation
The second advantage of stored procedures is that they're precompiled. This means that the DBMS only has to prepare a statement once, instead of preparing a statement every time it executes. To avoid building false hopes, we should emphasize that the precompilation is only partial, is only temporary, and is not a free lunch.
Let's take Informix as an example. We know that other DBMSs operate a bit differently, but they all operate under the same constraints. In a general fashion, here is what actually goes on.
When a CREATE PROCEDURE statement (for example, CREATE PROCEDURE Sp_proc1) is executed, Informix parses the statement and stores two things in the database catalog: a list of the objects (tables or other stored procedures) on which the procedure depends, and a list of tokens from the parsing. The token list is called pcode. It's a step away from an ASCII command but a long way from executable code. Pcode is somewhat like Java bytecode—it's interpretable, not executable. The pcode is kept in a BLOB field in the catalog's tables.
When EXECUTE PROCEDURE Sp_proc1 is run for the first time, Informix loads the procedure's pcode and makes a query plan. The query plan has to go in a cache, but the cache has only enough room for 16 query plans because query plans require a lot of RAM. If the cache already contains 16 query plans, Informix discards the least-recently-used query plan at this point. The precompilation advantage for stored procedures thus applies only to the last 16 procedures you've used. All other procedures must be reloaded and, in effect, precompiled again, because making the query plan is the bulk of the precompilation job.
When EXECUTE PROCEDURE Sp_proc1 is run and it's not the first time, Informix has a cached query plan ready to go. However, the DBMS must still check the dependency list because the stored procedure might refer to some object that has been altered or dropped since the first time the procedure was executed. The other thing that might have changed since the first time is the parameter values, so Informix reprocesses them too.
Now Informix locks the procedure. Usually stored procedures are not reentrant because some of the variable information is stored outside the user's area. By ensuring that only one job at a time can execute, Informix ensures that executions of stored procedures are serializable.
And then Informix actually does the job.
How to return value from stored procedure
This sample will show u the various ways that you can get output from the Stored Procedures |
CREATE PROC TestReturn (@InValue int) AS Return @Invalue*2 go CREATE PROC TestReturn1 (@InValue int) AS EXEC @InValue = TestReturn @InValue Return @InValue Declare @ReturnValue int EXEC @ReturnValue = TestReturn1 6 Select ReturnValue=@ReturnValue
Comparing SET ROWCOUNT and TOP |
.NET Classes used :
|
|
SET ROWCOUNT 7
SELECT TOP 7 CustomerId,CompanyName,ContactName,Country
FROM Customers
WHERE Country = 'USA'
ORDER BY Country DESC, ContactName
SET ROWCOUNT 0
Comparing SET ROWCOUNT and TOP
---------------------------------------------------------
The Main Place where SET ROWCOUNT has an advantage over TOP is in reusing code. The same query that is used to return all rows can be limited to a smaller number of rows with SET ROWCOUNT. If TOP were to be used, then two queries would have to be written to handle the TOP differently from the query that returns all row.
SET ROWCOUNT is limited to an absolute number of rows. so in those cases where a percentage of the result set is desired, TOP is the only choice. SET ROWCOUNT also suffers from the need to "turn off" this option when complete result sets are needed. Many application developers forget this, and subsequent queries may be misinterpreted by users who see a limited number of results instead of the complete result set. Also, SE ROWCOUNT only works with absolute numbers. If a percentage of the results is desired, then TOP must be used. |
To swap the values of two cols in db
DECLARE @tmp VARCHAR(50) UPDATE employees SET @tmp=first name, first name=last name, last name=@tmp
|
