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 2005 Forums
 Transact-SQL (2005)
 select various rows into 1 output parameter

Author  Topic 

mike13
Posting Yak Master

219 Posts

Posted - 2013-05-22 : 11:26:22
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
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2013-05-22 : 12:17:44
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.
Go to Top of Page

mike13
Posting Yak Master

219 Posts

Posted - 2013-05-22 : 12:34:13
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
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2013-05-22 : 13:18:56
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

219 Posts

Posted - 2013-05-22 : 13:27:58
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
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2013-05-22 : 13:50:07
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
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2013-05-22 : 16:57:32
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
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2013-05-22 : 17:59:34
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
   

- Advertisement -