Part 2: Create a stored procedure using the product and product inventory table. This stored procedure should declare a variable and set it the count of all products in the product table. If the count is greater than or equal to 20 the stored procedure should display a message that says "Inventory count is greater than or equal to 20". Otherwise, it should say "Inventory count for the products is less than 20".

CREATE PROCEDURE Moreorless20ProductInventory
AS
BEGIN
    DECLARE @countprod INT

    SELECT @countprod = countprod 
    FROM
        (SELECT COUNT(*) 
         INTO countprod
         FROM Production.ProductInventory);

    IF @countprod >= 20 
    BEGIN
        SELECT 'The number of products is greater than or equal to 20' as col1;
    ELSE
        SELECT 'The number of products is less than 20' as col1;
    END IF;
END

Error:

Msg 156, Level 15, State 1, Procedure Moreorless20ProductInventory, Line 8 [Batch Start Line 0]
Incorrect syntax near the keyword 'INTO'.

Msg 156, Level 15, State 1, Procedure Moreorless20ProductInventory, Line 14 [Batch Start Line 0]
Incorrect syntax near the keyword 'ELSE'.

Msg 102, Level 15, State 1, Procedure Moreorless20ProductInventory, Line 16 [Batch Start Line 0]
Incorrect syntax near ';'

CodePudding user response：

INTO denotes creating a new table, not populating a variable. Try:

CREATE PROCEDURE dbo.Moreorless20ProductInventory
AS
BEGIN
  SET NOCOUNT ON;

  DECLARE @countprod int;

  SELECT @countprod = COUNT(*) FROM Production.ProductInventory;

  IF @countprod >= 20 
  BEGIN
    SELECT 'The number of products is greater than or equal to 20' as col1;
  END
  ELSE
  BEGIN
    SELECT 'The number of products is less than 20' as col1;
  END
END

Also a much more efficient way to get the total number of rows in a table is to use the metadata:

SELECT @countprod = SUM([rows]) FROM sys.partitions
  WHERE index_id IN (0, 1)
  AND [object_id] = OBJECT_ID(N'Production.ProductInventory');

See Bad habits: Counting rows the hard way.

CodePudding user response：

You can store the value to variable multiple ways:

1. ANSI WAY

DECLARE @countprod INT

SET  @countprod = (SELECT COUNT(*) 
         FROM Production.ProductInventory);

2. TSQL Way

DECLARE @countprod INT

SELECT @countprod = COUNT(*) FROM Production.ProductInventory;

3. T-SQL: Directly set value in DECLARE

DECLARE @countprod INT = (SELECT COUNT(*) FROM Production.ProductInventory);

I have written in detail about this, in this SO post: https://stackoverflow.com/a/62035659/634935