SQL stored procedure + return parameters

Hi

I have a select statement
select A1 from From tableA  whre A2=12345

which returns 2 or more rows from the table

I need to concatnate these values and pass out as a parameter from the stored procedure.

I am trying to do it with cursors and having a tough time.
\
Is there anyone who could help me with this.

seems simple one stored procedure with one input parameter and one output parameter and use cursor to fetch values in the output parameter. BUT I AM STRUGGLING

Thanks for your help in advance.

DingyS
DingySAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Aneesh RetnakaranDatabase AdministratorCommented:
U dont necessarily need a cursor , u can use a while loop
Aneesh RetnakaranDatabase AdministratorCommented:
create table #in ( i int identity, j varchar(10))

INSERT INTO #in
SELECT  
DECLARE @i int
SELECT @i =1

declare @str varchar(3000)
SELECT @str = ''

WHILE EXISTS (SELECT 1 FROM #in WHERE i > 0)
BEGIN
    SELECT @str = @str +' '+j
        FROM #in WHERE i = @i
       
    DELETE from #in
    WHERE i = @i
   
    SELECT @i = @i +1

END

SELECT @str
DROP table #in
DingySAuthor Commented:
The user whom I log on as to the database does not have create tab;le priviledges.

I would prefer to use a stored procedure with input and out put parameters.
DingyS
Big Business Goals? Which KPIs Will Help You

The most successful MSPs rely on metrics – known as key performance indicators (KPIs) – for making informed decisions that help their businesses thrive, rather than just survive. This eBook provides an overview of the most important KPIs used by top MSPs.

sasapopovicCommented:
Try this:

create procedure myproc
(
   @a2 varchar(10),
   @outvar varchar(1000) output
)
as
   declare @currA varchar(100)
   declare resultsCursor cursor
   for select a1 from tableA where a2 = @a2
   open resultsCursor
   fetch next from resultsCursor into @currA

   while @@fetch_status = 0 begin
      set @outvar = coalesce(@outvar, '') + @currA

      fetch next from resultsCursor into @currA
   end
   close resultsCursor
   deallocate resultsCursor
go


I hope this will help you.

Regards,
Sasa

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
sasapopovicCommented:
I forgot to mention that you can try above procedure with next query:

declare @outvar varchar(1000)
exec myproc '12345', @outvar = @outvar output

I was also not sure if your parameters are int or varchar types so I put varchar. You can easilly change that to the right type.
Anthony PerkinsCommented:
>>The user whom I log on as to the database does not have create tab;le priviledges. <<
I expect you will find they can in fact create temporary tables.  there is also no need to resort to CURSORs unless performance is not an issue.  For a faster solution post the structure of your tables and the stored procedure.
sureshkgupta1Commented:
Try creating stored proc like this:

CREATE PROC spGetA1List ( @vA1 AS VARCHAR(100), @vA1List AS VARCHAR(1000) OUTPUT)
AS
DECLARE A1_cursor CURSOR FOR
SELECT A1  From tableA where A2 = @vA1

OPEN A1_cursor

FETCH NEXT FROM A1_cursor
INTO @vA1

IF @@FETCH_STATUS = 0
BEGIN
      SET @vA1List = @vA1
      
      FETCH NEXT FROM A1_cursor
      INTO @vA1

      WHILE @@FETCH_STATUS = 0
      BEGIN
                  SET @vA1List = @vA1List + ', ' +       @vA1

                  FETCH NEXT FROM A1_cursor
                  INTO @vA1
      END
END

CLOSE A1_cursor
DEALLOCATE A1_cursor

Regards,
Suresh.
sasapopovicCommented:
sureshkgupta1,

Can you please explain me what is the difference between your procedure and the one I wrote? When I use someone's idea I at least mention that I did that.
sasapopovicCommented:
Maybe I'm not the right person to judge but I think my answer is what DingyS looked for.
sureshkgupta1 just rewrote my procedure and didn't even mention that...

I don't care if you refund points to DingyS but would not like to see that someone else received my points :-)
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Databases

From novice to tech pro — start learning today.