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 2005 Forums
 Transact-SQL (2005)
 select various rows into 1 output parameter
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

mike13
Posting Yak Master

Netherlands
214 Posts

Posted - 05/22/2013 :  11:26:22  Show Profile  Click to see mike13's MSN Messenger address  Reply with Quote
Hi all,


i want to select various rows into 1 output parameter.

I got this code, but for some reason i just get 1 record

SELECT DISTINCT @imageslist = isnull(@imageslist,'') + convert(varchar(255),T_Product_Main.Image,103) +',' + convert(varchar(255),T_Product_Description.Name,103) +'-'

FROM T_Product_CategorySub

what i'm doing wrong? ;-)

James K
Flowing Fount of Yak Knowledge

3649 Posts

Posted - 05/22/2013 :  12:17:44  Show Profile  Reply with Quote
quote:
Originally posted by mike13

Hi all,


i want to select various rows into 1 output parameter.

I got this code, but for some reason i just get 1 record

SELECT DISTINCT @imageslist = isnull(@imageslist,'') + convert(varchar(255),T_Product_Main.Image,103) +',' + convert(varchar(255),T_Product_Description.Name,103) +'-'

FROM T_Product_CategorySub

what i'm doing wrong? ;-)

You wanted to get "1 output parameter" and that is what it is doing, isn't it? How many do you need to get? That select statement by itself won't send anything to the output. So if you need to see the output, you have to have a "SELECT @imageslist" statement.


Also, remove the DISTINCT.

Edited by - James K on 05/22/2013 12:19:57
Go to Top of Page

mike13
Posting Yak Master

Netherlands
214 Posts

Posted - 05/22/2013 :  12:34:13  Show Profile  Click to see mike13's MSN Messenger address  Reply with Quote
i want it to return a string like
image.jpg,the image name-image2.jpg,the image name2-image3.jpg,the image name3-

but it only returns
image3.jpg,the image name3-
it must be overwriting
Go to Top of Page

James K
Flowing Fount of Yak Knowledge

3649 Posts

Posted - 05/22/2013 :  13:18:56  Show Profile  Reply with Quote
You should not use DISTINCT keyword. If you want only distinct entries in the final output, use a subquery as shown in example 3 below.
CREATE TABLE #tmp(col1 VARCHAR(32), col2 VARCHAR(32));
INSERT INTO #tmp VALUES ('A','B'),('C','D'),('C','D');

-- 1 This returns a,b,c,d,c,d
DECLARE @imageslist VARCHAR(256);
SELECT @imageslist = ISNULL(@imageslist,'') + col1 + ',' + col2 +',' FROM #tmp;
SELECT @imageslist;
GO

-- 2. This returns only one pair, most likely C,D.
DECLARE @imageslist VARCHAR(256);
SELECT DISTINCT @imageslist = ISNULL(@imageslist,'') + col1 + ',' + col2 +',' FROM #tmp;
SELECT @imageslist;
GO

-- 3. If you want distinct, use that in a suquery like this
DECLARE @imageslist VARCHAR(256);
SELECT @imageslist = ISNULL(@imageslist,'') + col1 + ',' + col2 +',' 
	FROM (SELECT DISTINCT col1, col2 FROM #tmp) s;
SELECT @imageslist;

DROP TABLE #tmp;
Go to Top of Page

mike13
Posting Yak Master

Netherlands
214 Posts

Posted - 05/22/2013 :  13:27:58  Show Profile  Click to see mike13's MSN Messenger address  Reply with Quote
Thanks!!
It was the distinct!
that did the trick, didn't know you could do a subquery like that

Go to Top of Page

James K
Flowing Fount of Yak Knowledge

3649 Posts

Posted - 05/22/2013 :  13:50:07  Show Profile  Reply with Quote
You are very welcome - glad to help.

You can use a subquery in most of the places where you can use a base table or view.
Go to Top of Page

Lamprey
Flowing Fount of Yak Knowledge

4614 Posts

Posted - 05/22/2013 :  16:57:32  Show Profile  Reply with Quote
quote:
Originally posted by mike13

Thanks!!
It was the distinct!
that did the trick, didn't know you could do a subquery like that

In the interest of proper terminology it's actually called a Derived Table or Inline View. :)
Go to Top of Page

James K
Flowing Fount of Yak Knowledge

3649 Posts

Posted - 05/22/2013 :  17:59:34  Show Profile  Reply with Quote
quote:
Originally posted by Lamprey

quote:
Originally posted by mike13

Thanks!!
It was the distinct!
that did the trick, didn't know you could do a subquery like that

In the interest of proper terminology it's actually called a Derived Table or Inline View. :)

Those were the words I was looking for!! Thanks Lamprey
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.25 seconds. Powered By: Snitz Forums 2000