Use Left Outer JOIN for joining tables AVMWeb_Items AVMWeb_Item_Resources INNER JOIN for joining tables AVMWeb_Items AVMWeb_ItemBrands AVMWeb_ItemSubCategorys
exec sp_executeSQL On Tue, Dec 1, 2009 at 4:31 PM, Kadir Avci <[email protected]> wrote: > Hello. I have an sql procedure and when I run it the time is 10 seconds. > I optimize it and now its take 1 second. But I have a problem. If item has > two or more pictures second procedure is not working properly. > How can I get only one item_resource in second procedure? > > *Here is my first sql procedure.* > ALTER PROCEDURE [dbo].[get_AVMWeb_Items_bySubCatID_ForCatContent] > @subcatID nvarchar (500), > @gosUrun nvarchar(50) > AS > BEGIN > DECLARE @sqlStr nvarchar(4000); > set @sqlStr = 'Select top ' + @gosUrun + 'AVMWeb_Items.*, ' + > '(Select subcatname from AVMWeb_ItemSubCategorys where > AVMWeb_ItemSubCategorys.id = AVMWeb_Items.subcatID) as subcatname, ' + > '(Select brandname from AVMWeb_ItemBrands where AVMWeb_ItemBrands.id = > AVMWeb_Items.brandID) as brandname, ' + > '(select top 1 deger from AVMWeb_Item_Resources where > AVMWeb_Item_Resources.itemID = AVMWeb_Items.id) as deger ' + > 'from AVMWeb_Items ' + > 'where AVMWeb_Items.subcatID = ''' + @subcatID + ''''; > exec (@sqlStr) > END > > > *Here is my second sql procedure.* > ALTER PROCEDURE [dbo].[get_AVMWeb_Items_bySubCatID_ForCatContent] > @subcatID nvarchar (500), > @gosUrun nvarchar(50) > AS > BEGIN > DECLARE @sqlStr nvarchar(4000); > set @sqlStr = 'Select top ' + @gosUrun + > 'AVMWeb_Items.* , ' + > 'AVMWeb_ItemSubCategorys.*, ' + > 'AVMWeb_ItemBrands.*,' + > 'AVMWeb_Item_Resources.* ' + > 'From AVMWeb_Items, AVMWeb_ItemSubCategorys, AVMWeb_ItemBrands, > AVMWeb_Item_Resources ' + > 'Where AVMWeb_ItemSubCategorys.id = AVMWeb_Items.subcatID and ' > + > 'AVMWeb_ItemBrands.id = AVMWeb_Items.brandID and ' + > 'AVMWeb_Item_Resources.itemID = AVMWeb_Items.id and ' + > 'AVMWeb_Items.subcatID = ''' + @subcatID + ''''; > exec (@sqlStr) > END > > > > Kadir Avcı > Software Developer | Freelance Web Designer > web: www.kad1r.com > twt: www.twitter.com/kad1r >
