Store Procedure
CREATE PROCEDURE mytest2
AS
BEGIN
SELECT
name,
city
FROM
student
ORDER
BY
name;
END;
ALTER PROCEDURE mytest2(@name1 AS varchar(500))
AS
BEGIN
SELECT
name,
city
FROM
student
WHERE
name=
@name1
ORDER
BY
name;
END;
EXEC mytest2 'NILESH';
EXECUTE
teststudent;
Or
EXEC
teststudent;
Creating a simple stored procedure
The following SELECT statement returns a list of products from
the products table in the BikeStores sample database:
Student (
roll , name , city ) Table name
Step -1
SELECT
name,
city
FROM
student
ORDER BY
city;
step-2 create
procedure
CREATE PROCEDURE teststudent
AS
BEGIN
SELECT
name,
city
FROM
student
ORDER BY
city;
END;
Step -3 execute
procedure
EXECUTE
teststudent;
Or
EXEC
teststudent;
In this syntax:
- The uspProductList is the name of the stored procedure.
- The AS keyword separates the heading and the body of the
stored procedure.
- If
the stored procedure has one statement, the BEGIN and END keywords surrounding the statement are optional.
However, it is a good practice to include them to make the code clear.
Note that in addition to the CREATE PROCEDURE keywords,
you can use the CREATE PROC keywords to make the statement shorter.
Modifying a stored procedure
To modify an existing stored procedure, you use the ALTER PROCEDURE statement.
First, open the stored procedure to view its contents by
right-clicking the stored procedure name and select Modify menu
item:
ALTER PROCEDURE teststudent
AS
BEGIN
SELECT
name,
city
FROM
student
ORDER BY
city;
END;
EXEC teststudent
Example
SELECT
name,
city
FROM
student
ORDER BY
city;
CREATE PROCEDURE
teststudent
AS
BEGIN
SELECT
name,
city
FROM
student
ORDER BY
city;
END;
EXECUTE teststudent;
EXEC teststudent;
ALTER PROCEDURE teststudent
AS
BEGIN
SELECT
name,
city
FROM
student
ORDER BY
city;
END;
EXEC teststudent
·
Parameters – learn how to create stored procedures with
parameters, including optional parameters.
SELECT
name,
city
FROM
student
ORDER BY
city;
CREATE PROCEDURE teststudent1
AS
BEGIN
SELECT
name,
city
FROM
student
ORDER BY
city;
END;
ALTER PROCEDURE teststudent1(@sname AS varchar(500))
AS
BEGIN
SELECT
name,
city
FROM
student
WHERE
name=@sname
ORDER BY
name;
END;
EXEC teststudent1 'NILESH';
27/12/
2021
Multi
parameter Store Procedure
SELECT
name,
city
FROM
student
ORDER BY
city;
CREATE PROCEDURE teststudent1
AS
BEGIN
SELECT
name,
city
FROM
student
ORDER BY
city;
END;
ALTER PROCEDURE teststudent1(@sname AS varchar(500) , @city1
AS varchar(500))
AS
BEGIN
SELECT
name,
city
FROM
student
WHERE
name=@sname AND
city=@city1
ORDER BY
name;
END;
EXEC teststudent1 'NILESH' , 'JBP';
Using named parameters
In case stored procedures have multiple parameters, it is better
and more clear to execute the stored procedures using named parameters.
SELECT
name,
city
FROM
student
ORDER BY
city;
CREATE PROCEDURE teststudent1
AS
BEGIN
SELECT
name,
city
FROM
student
ORDER BY
city;
END;
ALTER PROCEDURE teststudent1(@sname AS varchar(500) , @city1 AS varchar(500))
AS
BEGIN
SELECT
name,
city
FROM
student
WHERE
name=@sname
AND city=@city1
ORDER
BY
name;
END;
EXEC teststudent1 @sname='NILESH' , @city1='NGP'
// THIS IS NAME PARAMETER
USING NUMERIC parameter
EXEC teststudent1 @sname=800 , @city1='NGP' // THIS IS NAME PARAMETER
Store
procedure using LIKE operator
ALTER PROCEDURE teststudent1(@sname AS varchar(500))
AS
BEGIN
SELECT
name,
city
FROM
student
WHERE
name LIKE '%' +
@sname + '%'
ORDER
BY
name;
END;
execute teststudent1 @sname='nil';
HOW
TO USE IF ___ELSE IN STORE PROCEDURE
ALTER PROCEDURE teststudent1(@sname AS varchar(500))
AS
BEGIN
SELECT
name,
city
FROM
student
WHERE
name=@sname
ORDER
BY
name;
if
@sname='NILESH'
BEGIN
PRINT
'TEACHER';
END
END;
EXEC
teststudent1 'NILESH'
Varible used in SQL QUERY ( select all & run )
DECLARE @name1 AS varchar(200);
SET @name1 ='nilesh';
SELECT
name,city
FROM
student
WHERE
name =
@name1
ORDER BY
Name
Set query result to varible ( select all & run )
DECLARE @count1 INT;
SET @count1 = (SELECT COUNT(*) FROM student );
print @count1;
PRINT 'The number of products is ' + CAST(@count1 AS VARCHAR(MAX));
Selecting a record into variables (select all & run )
DECLARE @name1 VARCHAR(MAX);
SELECT
@name1 =
name
FROM
student
WHERE
name='nilesh';
SELECT @name1 AS Student_Name;
Creating output parameters
SQL
Server enables us to provide many output parameters in a stored procedure.
These output parameters can be of any valid data type, such as integer, date,
or character. We can use the below syntax to create an output parameter in the
stored procedure:
To create an output parameter for a stored procedure, you use the following
syntax:
parameter_name data_type OUTPUTCode language: SQL (Structured Query Language) (sql)
A stored procedure can have many output parameters. In addition,
the output parameters can be in any valid data type e.g., integer, date, and varying character.
Note that the @@ROWCOUNT is
a system variable that returns the number of rows read by the previous
statement.
CREATE PROCEDURE output1
(
@name1 varchar(500),
@count1 INT
OUTPUT
)
AS
BEGIN
SELECT
name
FROM
student
WHERE
name = @name1;
SELECT
@count1 = @@ROWCOUNT;
END;
DECLARE @dispcount2 INT;
EXEC output1
@name1 = 'nilesh',
@count1 =
@dispcount2 OUTPUT;
SELECT @dispcount2 AS 'Number of products found';
Output parameter example -2
CREATE TABLE Employee (EmpID int identity(1,1),EmpName varchar(500))
CREATE PROCEDURE prooutput
(
@Ename varchar(50),
@EId int
output
)
AS
BEGIN
SET NOCOUNT ON INSERT INTO Employee (EmpName) VALUES (@Ename)
SELECT @EId= SCOPE_IDENTITY()
END
declare @EmpID INT
EXEC prooutput 'Andrew', @EmpID OUTPUT
SELECT @EmpID
select * from Employee
output
parameter example -3 ( imp )
create PROCEDURE
stupro1 (@StudentCount
INT OUTPUT)
AS
BEGIN
SELECT @StudentCount =
COUNT(name)FROM student;
END;
DECLARE
@TotalStudents INT
EXEC stupro1 @TotalStudents
OUTPUT
PRINT
@TotalStudents;
Rename procedure
sp_rename 'OLD','NEW'
Drop procedure
DROP PROCEDURE <NAME>
List
all procedure
SELECT * FROM sys.procedures;
Looping
while loop
DECLARE @counter INT = 1;
WHILE @counter <= 5
BEGIN
PRINT
@counter;
SET
@counter = @counter +
1;
END
SQL Server BREAK statement example
The following example illustrates how to use
the BREAK statement:
DECLARE @counter INT = 0;
WHILE @counter <= 5
BEGIN
SET
@counter = @counter +
1;
IF
@counter = 4
BREAK;
PRINT
@counter;
END
SQL Server CONTINUE example
The following example illustrates how the CONTINUE statement works.
DECLARE @counter INT = 0;
WHILE @counter < 5
BEGIN
SET
@counter = @counter +
1;
IF
@counter = 3
CONTINUE;
PRINT
@counter;
END
SQL Server CROSS JOIN
CROSS JOIN produces the result by combining all possibilities
of the two or more tables that contain every row from all contributing tables.
In other words, it returns
the result by generating a paired combination of each row of all tables
participating in the join operation. This join is sometimes
also termed CARTESIAN
JOIN, which provides the Cartesian product of all associated
tables.
1.
SELECT column_lists
2.
FROM table1
3.
CROSS JOIN table2;
SELECT *
FROM inventa
CROSS JOIN products;
Cross join
for specific coloumn
SELECT inventa.pname,inventa.rate , products.product_id,products.category
FROM inventa
CROSS JOIN products;
No comments:
Post a Comment