1

I'm trying to Create a stored procedure but there is a field actually exist in another field but its id is used as foreign key in current table, SQL Query is:

Create Procedure sp_Select
As
Begin
 SELECT [ProductID]
 ,[Name]
 ,SELECT tblCategories.CategoryName 
 FROM tblCategories INNER JOIN tblProducts 
 ON tblCategories.CategoryId = tblProducts.CategoryID;
 ,[Model]
 ,Cast([RAM] As nvarchar(10)) +' '+[RAMMemUnit] As RAM
 ,Cast([SecondryMem] As nvarchar(10)) + ' ' + [SecondMemUnit] As Memory
 ,[OSVersion]
 ,[Processor]
 ,CAST([Battery] As nvarchar(10)) +' '+ [BatteryType] As Battery
 ,UnitsInStock AS 'Quantity In Stock'
 FROM [tblProducts]
End

Error in this following portion: SELECT tblCategories.CategoryName FROM tblCategories INNER JOIN tblProducts ON tblCategories.CategoryId = tblProducts.CategoryID;

Someone help me please.

McNets
24k11 gold badges51 silver badges90 bronze badges
asked Mar 29, 2017 at 2:48

1 Answer 1

1

There are actually two problems with your first query. The first is that you have an extra semi-colon for the subquery where you are getting the category name. The second is that if that query returns more than one row then you will receive an error. I've corrected it for you to eliminate both of those issues.

CREATE PROCEDURE sp_Select
AS
BEGIN
 SELECT [ProductID]
 ,[Name]
 , CategoryName = (SELECT TOP 1 tblCategories.CategoryName
 FROM tblCategories
 WHERE tblCategories.CategoryId = tblProducts.CategoryID)
 ,[Model]
 , Cast([RAM] AS NVARCHAR(10)) + ' ' + [RAMMemUnit] AS RAM
 ,Cast([SecondryMem] AS NVARCHAR(10)) + ' ' + [SecondMemUnit] AS Memory
 ,[OSVersion]
 ,[Processor]
 ,CAST([Battery] AS NVARCHAR(10)) + ' ' + [BatteryType] AS Battery
 ,UnitsInStock AS 'Quantity In Stock'
 FROM [tblProducts];
END

However, queries that have sub-queries like the above generally perform worse than ones that can achieve the same results with a sub-query. It lets you access other fields from the tblCategories table without incurring additional sub-query costs, and you can more easily search for products that are in particular categories.

Some of the comments have mentioned changing the join from using a LEFT OUTER to be an INNER join. I chose LEFT OUTER initially because that more closely matched the result set you would have received in your first query. Essentially, it would have NULL's in place where the CategoryName would be assuming you had Products without Categories. An INNER JOIN would eliminate products that had no matching category.

CREATE PROCEDURE sp_Select
AS
BEGIN
 SELECT P.[ProductID]
 ,P.[Name]
 , C.CategoryName
 ,P.[Model]
 , Cast(P.[RAM] AS NVARCHAR(10)) + ' ' + P.[RAMMemUnit] AS RAM
 ,Cast(P.[SecondryMem] AS NVARCHAR(10)) + ' ' + P.[SecondMemUnit] AS Memory
 ,P.[OSVersion]
 ,P.[Processor]
 ,CAST(P.[Battery] AS NVARCHAR(10)) + ' ' + P.[BatteryType] AS Battery
 ,P.UnitsInStock AS 'Quantity In Stock'
 FROM [tblProducts] AS P
 LEFT OUTER JOIN tblCategories AS C ON C.CategoryId = P.CategoryID;
END
answered Mar 29, 2017 at 2:53
8
  • 1
    I don't think there's any guarantee that replacing a subquery with a left outer join will make things faster. Commented Mar 29, 2017 at 2:55
  • The corrected subquery needs more correction: to become correlated. As it is, it will return more than 1 rows and throw error (unless there is only 1 product). Commented Mar 29, 2017 at 7:51
  • So Can you please correct it.? Commented Mar 29, 2017 at 11:14
  • @MuhazzabChouhadry - The final query deals with it, by removing the subquery. The INNER JOIN provides the correlation mentioned. If you really want to use the subquery, replace INNER JOIN tblProducts ON with WHERE in the first version of the query in Jonathan's answer. Commented Mar 29, 2017 at 18:34
  • @RDFozz I think this is also working fine, What is the benefit of using this query.? Commented Mar 30, 2017 at 3:11

Your Answer

Draft saved
Draft discarded

Sign up or log in

Sign up using Google
Sign up using Email and Password

Post as a guest

Required, but never shown

Post as a guest

Required, but never shown

By clicking "Post Your Answer", you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.