W3. SQL Stored Procedures

Question 1

Q: What is a stored procedure in SQL?

Answer 1

A: It’s a prepared SQL code that can be saved and reused, allowing you to execute complex queries repeatedly.

Question 2

Q: Why would you use a stored procedure?

Answer 2

A: To avoid rewriting SQL queries and to execute complex SQL statements with a single call.

Question 3

Q: Can you pass parameters to a stored procedure?

Answer 3

A: Yes, parameters can be passed so the procedure can operate based on specific values.

Question 4

Q: Write the syntax to create a stored procedure.

Answer 4

CREATE PROCEDURE procedure_name
AS
sql_statement
GO;

Question 5

Q: How do you execute a stored procedure?

Answer 5

A: Use EXEC procedure_name;

Question 6

Q: Write a stored procedure to select all records from Customers, named “SelectAllCustomers”.

Answer 6

CREATE PROCEDURE SelectAllCustomers
AS
SELECT * FROM Customers
GO;

Question 7

Q: How would you execute the stored procedure “SelectAllCustomers”?

Answer 7

EXEC SelectAllCustomers;

Question 8

Q: How do you create a stored procedure with one parameter?

Answer 8

A: Define the parameter in the procedure, e.g.,

CREATE PROCEDURE procedure_name @Parameter datatype
AS
sql_statement;

Question 9

Q: Write a stored procedure to select customers from a specific city, using @City as a parameter.

Answer 9

CREATE PROCEDURE SelectAllCustomers @City nvarchar(30)
AS
SELECT * FROM Customers WHERE City = @City
GO;

Question 10

Q: How would you execute the “SelectAllCustomers” procedure with @City = ‘London’?

Answer 10

EXEC SelectAllCustomers @City = ‘London’;

Question 11

Q: How do you create a stored procedure with multiple parameters?

Answer 11

A: Define each parameter with a datatype, separated by commas, e.g.,

CREATE PROCEDURE procedure_name @Param1 datatype, @Param2 datatype;

Question 12

Q: Write a stored procedure to select customers from a specific city and postal code, using @City and @PostalCode as parameters.

Answer 12

CREATE PROCEDURE SelectAllCustomers @City nvarchar(30), @PostalCode nvarchar(10)
AS
SELECT * FROM Customers WHERE City = @City AND PostalCode = @PostalCode
GO;

Question 13

Q: How would you execute the “SelectAllCustomers” procedure with @City = ‘London’ and @PostalCode = ‘WA1 1DP’?

Answer 13

EXEC SelectAllCustomers @City = ‘London’, @PostalCode = ‘WA1 1DP’;