User Id :    Password :      New Member   Forgot Password  
Topic: SQL Server how to use Coalesce function
Mansi Gupta
01 Jun 2011 at 04:01 AM

Hi I would like to post a query which makes use of table records Ids and convert them into comma separated string. Below is the example given

 

DECLARE @MerchantIDs VARCHAR(100)

DECLARE @strMerchant VARCHAR(MAX)

SELECt @MerchantIDs=COALESCE(@MerchantIDs+',','') +CONVERT(VARCHAR(10),id)

FROM (SELECT distinct tm.id FROM tblMerchant tm WHERE tm.referralcode IS NULL) _RES

SET @MerchantIDs=@MerchantIDs+','

PRINT @MerchantIDs

WHILE CHARINDEX(',',@MerchantIDs,1)<>0

BEGIN

SET @strMerchant=SUBSTRING(@MerchantIDs,0,CHARINDEX(',',@MerchantIDs,1))

PRINT @strMerchant

UPDATE tblMerchant

SET referralcode = (SELECT 'RM' + SUBSTRING(CONVERT(VARCHAR(50),NEWID()),1,6))

WHERE id=@strMerchant

SET @MerchantIDs=SUBSTRING(@MerchantIDs,CHARINDEX(',',@MerchantIDs,1)+1,LEN(@MerchantIDs))

END

Re: SQL Server how to use Coalesce function
Sumit Gupta
10 Jul 2011 at 03:15 AM
Thanks for useful function.
Total Replies: 1
 
Featured Text Ad

Featured Ad