CODEWITHSUNDEEP
sqlsql-server
EInherjar
EInherjar

Reputation: 339

Procedure returning multiple tables

I am learning SQL on SQL Server, and got the AdventureWorks DB. I got around to writing a procedure, but somehow it returns an infinite amount of tables, where only the first table of them is correct.

CREATE PROCEDURE SalesLT.Search
    @Name VARCHAR(40) = NULL
AS
BEGIN
    SELECT  
        Product.Name, ProductModel.Name AS ModelName, 
        ProductDescription.Description
    FROM 
        SalesLT.Product,  
        SalesLT.ProductModel,
        SalesLT.ProductDescription,
        SalesLT.ProductModelProductDescription
    WHERE 
        SalesLT.Product.ProductModelID = SalesLT.ProductModel.ProductModelID 
        AND SalesLT.Product.ProductModelID = SalesLT.ProductModelProductDescription.ProductModelID
        AND ProductModelProductDescription.ProductDescriptionID = SalesLT.ProductDescription.ProductDescriptionID
        AND Product.Name LIKE '%'+ISNULL(@Name,Product.Name)+'%'
END;

Execute SalesLT.Search

What exactly am I doing wrong? I can't seem to figure it out.

enter image description here

This is what I get when I run it.

Upvotes: 0

Views: 72

Answers (1)

Squirrel
Squirrel

Reputation: 24763

PUT a GO at the end of the stored procedure. Else you are doing a recursive stored procedure. The Execute SalesLT.Search become part of the stored procedure. The GO is a separator (refer to https://learn.microsoft.com/en-us/sql/t-sql/language-elements/sql-server-utilities-statements-go?view=sql-server-2017)

CREATE PROCEDURE SalesLT.Search
    @Name VARCHAR(40) = NULL
AS
BEGIN
Select Product.Name, ProductModel.Name AS ModelName, ProductDescription.Description
FROM SalesLT.Product, SalesLT.ProductModel,SalesLT.ProductDescription,SalesLT.ProductModelProductDescription
WHERE SalesLT.Product.ProductModelID=SalesLT.ProductModel.ProductModelID 
AND SalesLT.Product.ProductModelID = SalesLT.ProductModelProductDescription.ProductModelID
AND SalesLT.ProductModelProductDescription.ProductDescriptionID=SalesLT.ProductDescription.ProductDescriptionID
AND Product.Name LIKE '%'+ISNULL(@Name,Product.Name)+'%'
END;

GO

Execute SalesLT.Search

Upvotes: 1

Related Questions