Correlated Query: It is special type of queries in sql server database.Just like an subQuery(query nested with another query) that values from the outer query in its whre clause.subquery is evaluated once each row proceed by outer query.
SELECT employee_number, name
FROM employee AS e1
WHERE salary > (SELECT avg(salary)
FROM employee
WHERE department = e1.department);
In the above query the outer query is,
SELECT employee_number, name
FROM employee AS e1 where salary>Stored Procedure:
Stored Procedure is a named group of sql statements that have been previously created and stored in server Database or pre compiled querys.
A).Advantages of stored procedure:
1).Reduces the burden of the database.
2).User will get quick response.
3).Application performance will be improved.
4).code re-usebility.
B).Syntax for creating stored procedure:
Here i need one procedure for inserting username and pwd values into tbluser table.
Syntax is:
create procedure usp_tbluser_insert
(@uname nvarchar(50),@pwd nvarchar(50))
as
begin
insert into tbluser values(@uname,@pwd)
end
C).How to Exexcute Stored Procdure
exec usp_tbldemo_insert 'harish', 'harish123'
