SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2000 Forums
 Transact-SQL (2000)
 How to avoid from Cursor in query
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

mrsaif
Starting Member

43 Posts

Posted - 10/19/2006 :  08:12:48  Show Profile  Send mrsaif a Yahoo! Message  Reply with Quote
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

United Kingdom
12543 Posts

Posted - 10/19/2006 :  08:19:10  Show Profile  Visit nr's Homepage  Reply with Quote
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.

Edited by - nr on 10/19/2006 08:20:15
Go to Top of Page

mrsaif
Starting Member

43 Posts

Posted - 10/19/2006 :  09:27:42  Show Profile  Send mrsaif a Yahoo! Message  Reply with Quote
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

India
22744 Posts

Posted - 10/21/2006 :  07:56:45  Show Profile  Send madhivanan a Yahoo! Message  Reply with Quote
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
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.12 seconds. Powered By: Snitz Forums 2000