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.
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=2name ====saifasifi 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 wayMuhammad Saifullah |
|
nr
SQLTeam MVY
12543 Posts |
Posted - 2006-10-19 : 08:19:10
|
declare @s varchar(1000)select @s = coalesce(@s + ' OR ','') + namefrom table where id=1 and id2=2But 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. |
|
|
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.AgeCodeFROM 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 queryDECLARE @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 qyeryDECLARE @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 @ColorDescriptionsMuhammad Saifullah |
|
|
madhivanan
Premature Yak Congratulator
22864 Posts |
|
|
|
|
|
|