none
SQL Procedure needed

    Question

  • Hi I have this below query, but I want to make a stored procedure and call from frontend in asp.net

    Sql server 2008 Query

    SELECT Product.Name, Category.Name AS CategoryName, Product.Id, Manufacturer.Name AS ManufacturerName, Product.Price  FROM Product INNER JOIN Category ON Product.categoryID = Category.Id INNER JOIN Manufacturer ON Product.ManufacturerID = Manufacturer.Id WHERE (Product.Deleted = 0)

    I have another tables like crosssell, relatedproducts in the above query, based on the product id, I want to display Yes or No based on the value present by the ProductId in the crosssell and relatedproduct tables.

        Name        CategoryName          Id        ManufacturerName    Price               Crosssell    relatedproduct
       FLitem1     Batteries                 75513               abc              9876.0000            Yes                 No

    any help appreciated.. thanks

    Friday, September 20, 2013 3:49 AM

Answers

  • You can try something like this. (not tested)

    CREATE PROCEDURE MYPROC
    AS
    BEGIN
    	SELECT Product.Name, 
    	Category.Name AS CategoryName, 
    	Product.Id, 
    	Manufacturer.Name AS ManufacturerName, 
    	Product.Price,CASE
    	WHEN EXISTS(SELECT ID FROM Crosssell A WHERE A.ID= Product.ID) THEN 'YES'
    	ELSE 'NO' END AS   Crosssell,
    	WHEN EXISTS(SELECT ID FROM relatedproduct B WHERE B.ID= Product.ID) THEN 'YES'
    	ELSE 'NO' END AS   RelatedProduct,
    	FROM Product INNER JOIN Category 
    	ON Product.categoryID = Category.Id INNER JOIN Manufacturer 
    	ON Product.ManufacturerID = Manufacturer.Id 
    	WHERE (Product.Deleted = 0) 
    END


    Regards, RSingh

    • Marked as answer by bsa Friday, September 20, 2013 4:10 AM
    Friday, September 20, 2013 4:00 AM

All replies

  • You can try something like this. (not tested)

    CREATE PROCEDURE MYPROC
    AS
    BEGIN
    	SELECT Product.Name, 
    	Category.Name AS CategoryName, 
    	Product.Id, 
    	Manufacturer.Name AS ManufacturerName, 
    	Product.Price,CASE
    	WHEN EXISTS(SELECT ID FROM Crosssell A WHERE A.ID= Product.ID) THEN 'YES'
    	ELSE 'NO' END AS   Crosssell,
    	WHEN EXISTS(SELECT ID FROM relatedproduct B WHERE B.ID= Product.ID) THEN 'YES'
    	ELSE 'NO' END AS   RelatedProduct,
    	FROM Product INNER JOIN Category 
    	ON Product.categoryID = Category.Id INNER JOIN Manufacturer 
    	ON Product.ManufacturerID = Manufacturer.Id 
    	WHERE (Product.Deleted = 0) 
    END


    Regards, RSingh

    • Marked as answer by bsa Friday, September 20, 2013 4:10 AM
    Friday, September 20, 2013 4:00 AM
  • Please post DDL, so that people do not have to guess what the keys, constraints, Declarative Referential Integrity, data types, etc. in your schema are. Learn how to follow ISO-11179 data element naming conventions and formatting rules (what you posted is a mess). Temporal data should use ISO-8601 formats. Code should be in Standard SQL as much as possible and not local dialect. 

    This is minimal polite behavior on SQL forums. There is no such thing as a magical “id” transforms from one entity to another as it appears in a new table! There is no such thing as a “category_id” -- you can have a “<something>_category” or “<something>_id”, but that silly thing. 

    Categories are usually enforced with a CHECK() or with a REFERENCES, not with a join. 

    Since a table is a set, the name has to be a plural or collective noun. 

    We do not write SQL with bit flags! Your generic "deleted" is so fundamentally wrong. That was assembly language or 1960's COBOL! Did you know that manufacturers are identified by a DUNS? Products use the GTIN. Have you done any research before writing SQL? 

    >> I have another tables like cross_sell, related_products in the above query, based on the GTIN, I want to display Yes or No based on the value present by the GTIN in the cross_sell and related_products tables. <<

    It would be polite to post that DDL. Your yes/no is just more assembly language programming. First, here is guess at the DDL you did not provide. 

    CREATE TABLE Products
    (gtin CHAR(15) NOT NULL PRIMARY KEY,
     product_name VARCHAR(25) NOT NULL,
     product_category VARCHAR(10) NOT NULL
       CHECK (product_category IN ('batteries', .. ,) )
     manufacturer_duns CHAR(9) NOT NULL
       REFERENCES Manufacturers (manufacturer_duns),
     unit_price DECIMAL (12,4) NOT NULL
       CHECK (unit_price >= 0.0000)
    );
     
    We do not have the business rules for the Cross_Sell and Related_Products tables. My guess is that this is what mathematicians call equivalence classes. If you have designed the product_category encoding properly, then that might give you the related products. Have you used the Dewey Decimal Classification in a library? It is a hierarchical encoding that puts related subjects together. 

    The Cross_Sell table might look like this:

    CREATE TABLE Cross_Sells
    (sale_group INTEGER NOT NULL,
     gtin CHAR(15) NOT NULL 
       REFERENCES Products (gtin)
       ON DELETE CASCADE
       ON UPDATE CASCADE,
     PRIMARY KEY (sale_group, gtin));

    --CELKO-- Books in Celko Series for Morgan-Kaufmann Publishing: Analytics and OLAP in SQL / Data and Databases: Concepts in Practice Data / Measurements and Standards in SQL SQL for Smarties / SQL Programming Style / SQL Puzzles and Answers / Thinking in Sets / Trees and Hierarchies in SQL

    Friday, September 20, 2013 11:13 PM