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?
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
0
 
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
0
 
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
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

 
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
0

Experts Exchange Solution brought to you by ConnectWise

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.
0
 
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.
0
 
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.
0
 
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.
0
 
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 :-)
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.