Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 SQL Server 2000 Forums
 Transact-SQL (2000)
 How to avoid from Cursor in query

Author  Topic 

mrsaif
Starting Member

43 Posts

Posted - 2006-10-19 : 08:12:48
I have query which returns 2 records. I want to concatenate the values of one of the columns value in one string. But without using cursor. is there any way.

the query return
select name from table where id=1 and id2=2
name
====
saif
asif

i want a query that returns 'saif OR asif'
but i dont want to user cursor for this bcz this query is in function and that fuction is called in a query that returns 20 record. i have seen that when my function uses cursor for this purpose it slows down my main query to 10 times . Please help me. if there is another way


Muhammad Saifullah

nr
SQLTeam MVY

12543 Posts

Posted - 2006-10-19 : 08:19:10
declare @s varchar(1000)
select @s = coalesce(@s + ' OR ','') + name
from table where id=1 and id2=2

But it sounds like your function is being called for each row so the solution would be to get rid of the function.

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.
Go to Top of Page

mrsaif
Starting Member

43 Posts

Posted - 2006-10-19 : 09:27:42
Thanks, but my problem is not solved yet, but my query imporves a litle bit now it is taking almost 7 seconds and have improved 2.5 secords :) . if i do not used these 2 functions which contais that previous logic than it takes 0.69 secods. By using above method it does not improve in great deal.
and it is neccessary to use fucntion for me bcz the resultnat row returns 2 records with one column , i concate these recrds in the main query that is why i am using function is there any other way to improve my query.
Here is my main query.
SELECT top 20 dbo.Products.ProductID, dbo.GetFieldColorForProducts(ProductID) AS FieldColorString, dbo.GetBorderColorForProducts(ProductID) AS BorderColorString, dbo.Collections.CollectionCode,
dbo.Products.Description, dbo.Designs.DesignCode, dbo.Countries.CountryCode, dbo.Ages.AgeCode
FROM dbo.Products
INNER JOIN dbo.Collections ON dbo.Collections.CollectionID = dbo.Products.CollectionIDFK
INNER JOIN dbo.Designs ON dbo.Products.DesignIDFK = dbo.Designs.DesignID
INNER JOIN dbo.ItemDetails ON dbo.ItemDetails.ItemIdFK = dbo.Products.ProductID AND dbo.ItemDetails.ProductTypeIDFK = 1 AND dbo.ItemDetails.LockingCodeIdFK <> 15
LEFT OUTER JOIN dbo.Locations ON dbo.Locations.LocationID = dbo.ItemDetails.LocationIDFK
BorderColor.ColorID = dbo.Products.BorderColorIDFK
FieldColor.ColorID = dbo.Products.FieldColorIDFK
INNER JOIN dbo.Countries ON dbo.Countries.CountryID = dbo.Products.OriginCountryIDFK
dbo.SizeCategories.SizeCategoryID = dbo.Products.SizeCategoryIDFK
INNER JOIN dbo.Ages ON dbo.Ages.AgeID = dbo.Products.AgeIDFK
INNER JOIN dbo.Shapes ON dbo.Products.ShapeIDFK = dbo.Shapes.ShapeId
LEFT OUTER JOIN dbo.LockingCodes ON dbo.LockingCodes.LockingCodeId = dbo.ItemDetails.LockingCodeIdFK '
dbo.ProductColors.ProductIDFK '
+ @whereStr + ' and Productid>@Productid
----------------------------------------
function GetBorderColorForProducts (@ProductID BIGINT) has following query

DECLARE @ColorDescriptions AS VARCHAR(1000)
SET @ColorDescriptions = ''
select @ColorDescriptions = coalesce(@ColorDescriptions + ' OR ','') + Colors.ColorDescription FROM ProductColors, Colors WHERE ProductColors.ProductIDFK = @ProductID AND ProductColors.ColorTypeID = 0 AND Colors.ColorID = ProductColors.ColorIDFK --Assuming 2 is for border color
RETURN @ColorDescriptions
----------------------------------
and function GetFieldColorForProducts (@ProductID BIGINT) has follwing qyery

DECLARE @ColorDescriptions AS VARCHAR(1000)
SET @ColorDescriptions = ''
select @ColorDescriptions = coalesce(@ColorDescriptions+' OR ','') +Colors.ColorDescription FROM ProductColors, Colors WHERE ProductColors.ProductIDFK = @ProductID AND ProductColors.ColorTypeID = 1 AND Colors.ColorID = ProductColors.ColorIDFK --Assuming 1 is for field color
return @ColorDescriptions

Muhammad Saifullah
Go to Top of Page

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2006-10-21 : 07:56:45
Do you need something like this?

http://sqljunkies.com/WebLog/amachanic/archive/2004/11/10/5065.aspx?Pending=true


Madhivanan

Failing to plan is Planning to fail
Go to Top of Page
   

- Advertisement -