Why are you using SQL like that? You should avoid the use of dynamic SQL
like this as it performs comparatively poorly.
If you are using SQL 2005 you can parameterise the TOP query (SELECT TOP
@topcount....)
If you are using SQL 2000 or before (why?) you can use the SET ROWCOUNT
@topcount pattern.
So you could rewrite your query as (assuming SQL2005 or later)
ALTER PROCEDURE [dbo].[get_AVMWeb_Items_bySubCatID_ForCatContent]
@subcatID nvarchar (500),
@gosUrun nvarchar(50)
AS
BEGIN
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
And the second procedure as
ALTER PROCEDURE [dbo].[get_AVMWeb_Items_bySubCatID_ForCatContent]
@subcatID nvarchar (500),
@gosUrun nvarchar(50)
AS
BEGIN
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 + ;
When you say "second procedure is not working" what do you mean? It looks to
me like you should be using a combination of INNER and LEFT JOINs to achieve
what you want.
On Tue, Dec 1, 2009 at 11:01 AM, Kadir Avci <avcika...@gmail.com> 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
>
