Write a script that creates and calls a stored procedure named test. This stored

Question

Write a script that creates and calls a stored procedure named test. This stored procedure should declare a variable and set it to the count of all products in the Products table. If the count is greater than or equal to 7, the stored procedure should display a message that says, “The number of products is greater than or equal to 7”. Otherwise, it should say, “The number of products is less than 7”.

2. Write a script that creates and calls a stored procedure named test. This stored procedure should use two variables to store (1) the count of all of the products in the Products table and (2) the average list price for those products. If the product count is greater than or equal to 7, the stored procedure should display a result set that displays the values of both variables. Otherwise, the procedure should display a result set that displays a message that says, “The number of products is less than 7”.

3. Write a script that creates and calls a stored procedure named test. This procedure should calculate the common factors between 10 and 20. To find a common factor, you can use the modulo operator (%) to check whether a number can be evenly divided into both numbers. Then, this procedure should display a string that displays the common factors like this: Common factors of 10 and 20: 1 2 5 10

4. Write a script that creates and calls a stored procedure named test. This procedure should attempt to insert a new category named “Guitars” into the Categories table. If the insert is successful, the procedure should display this message: 1 row was inserted. If the update is unsuccessful, the procedure should display this message: Row was not inserted - duplicate entry.

Need in sql language.

Explanation / Answer

1.

/*creating a procedure */
CREATE PROCEDURE test
AS
Begin
declare @count int; -- declaring variable
set @count = (select count(1) from products) --assign value to variable
if (@count>=7) -- condition check
begin
print ('The number of products is greater than or equal to 7') --printing text
end
else
begin
print('The number of products is less than 7')
end
End
GO

/* Calling procedure */

exec test

2.

/*creating a procedure */
CREATE PROCEDURE test
AS
Begin
declare @count int; -- declaring variable
declare @listprice float;
set @count = (select count(1) from products) --assign value to variable
set @listprice = (select avg(listprice) from products) --assign value to variable
if (@count>=7) -- condition check
begin
print ('count of products is '+ cast(@count as varchar(10))+' and average list price is ' + cast(@listprice as varchar(10))) --printing text
end
else
begin
print('The number of products is less than 7')
end
End
GO

/*Calling procedure*/
exec test

3.

/*creating a procedure */
CREATE PROCEDURE test
AS
Begin
DECLARE @num1 int = 10;
DECLARE @num2 int = 20;
DECLARE @count INT = 1;
DECLARE @result varchar (max) = 'Common factors of '+cast(@num1 as varchar(2))+' and '+cast(@num2 as varchar(2))+':';
WHILE @count < @num1
BEGIN
IF (@num1 % @count = 0 and @num2 % @count = 0)
set @result = @result +' '+ cast(@count as varchar(2))
set @count = @count + 1
END
print (@result)
End
GO

/*Calling procedure*/
exec test

4.

/*creating a procedure */
CREATE PROCEDURE test
AS
BEGIN
BEGIN TRY -- Try catch block is the best option to handle errors
-- Insert the data
INSERT INTO Categories (CategoryName)
VALUES ('Guitars')
PRINT '1 row was inserted'
END TRY
BEGIN CATCH
PRINT 'Row was not inserted - duplicate entry';
END CATCH
END
GO

/*Calling procedure*/
exec test

Related Questions

Navigate

• Browse (All)
• Browse W
• Subjects

Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.