[DotNetDevelopment] SQL Procedure problem

[Kadir Avci]

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