store procedure , Varible used in SQL QUERY
What is stored procedure and why we use it?
A SQL stored procedure (SP) is a collection SQL statements and sql command logic, which is compiled and stored on the database. ... The main purpose of stored procedures to hide direct SQL queries from the code and improve performance of database operations such as select, update, and delete data
Where is a stored procedure stored?
Image result for what is store procedure
Within SQL Server Studio, stored procedures, or procedures for short, reside within any database, under the programmability subdirectory.
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.
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';
===============================================
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 OUTPUT
Code language: SQL (Structured Query Language) (sql)
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;
Comments
Post a Comment