Stored Pocedure With Parameter
You Worry Me • Nathaniel Rateliff & The Night Sweats • 2018
• T-Sql
Stored Procedures are useful with parameters. The result will change based on the values of the parameters.
The parameters are declared just after the CREATE PROCEDURE nameProcedure with the characters @ then the variable name within space bettween them and finally the type. Example @City nvarchar(30). Each declaration are separed by comma.
-- create the stored procedure
CREATE PROCEDURE SelectAllCustomers
-- declare the variables name to fill with values
@City nvarchar(30),
@Age tinyint
AS
SELECT * FROM Customers
-- pass value in the WHERE clause
WHERE CityName = @City
AND AgeCustomer = @Age
GO;
-- Execute the SP with value
EXEC SelectAllCustomers @City = 'Paris', @Age >= 18;
EXEC SelectAllCustomers @City = 'Marseille', @Age >= 20;
Tips
With multiple parameters, it is better using named parameters. With one or two parameters, no need to add the name EXEC SelectAllCustomers 'Marseille', 20.
Do you whant default parameters value?
CREATE PROCEDURE SelectAllCustomers
-- declare the variables name to fill with values
@City nvarchar(30), @Age tinyint =20
With ALTER PROCEDURE, it’s possible to change the query. Copy the query, add the modifications and done! You can execute the new SP.
To rename a stored procedure using T-SQL, use system stored procedure sp_rename.
sp_rename 'GetProductDesc','GetProductDesc_new'
With while and Begin
CREATE PROCEDURE NOCountTest(@N INT)
AS
DECLARE @NumberofRecords INT;
SET @NumberofRecords = 0;
WHILE @NumberofRecords < @N
BEGIN
SET @NumberofRecords = @NumberofRecords + 1;
END;
GO;
Note – It is possible to create stored procedures that reference nonexistent tables. This feature allows you to debug procedure code without creating the underlying tables first, or even connecting to the target server.
CREATE PROCEDURE increase_budget
@percent INT(5)
AS UPDATE project
SET budget = budget + budget*@percent/100;
Create output parameters
First, create the query with the output variable
-- create the stored procedure
CREATE PROCEDURE SelectAllCustomersByCity
-- declare the variable name to fill with value
@City nvarchar(30),
-- create the output parameter to store the bumbers of customers found
@customer_count int OUTPUT
AS
BEGIN
SELECT * FROM Customers
-- pass value in the WHERE clause
WHERE CityName = @City;
-- assign the number of row returned by the query to the parameter
SELECT @customer_count = @@ROWCOUNT;
END;
-- Execute the SP with value
EXEC SelectAllCustomers @City = 'Paris', @Age >= 18;
EXEC SelectAllCustomers @City = 'Marseille', @Age >= 20;
Then, call the SP with the output parameter.
-- Declare variables to store the values returned by the SP
DECLARE @count int;
-- Use the variables in the SP call.
EXEC SelectAllCustomersByCity
@City = 'Paris',
@customer_count = @count OUTPUT;
-- Get the results fronm the variable
SELECT @count AS 'Number of customers found'
And for the record, I’ll deal with this later.
- encrypted stored procedure. The WHAT?
- temporary procedure