?
Solved

SQL stored procedure + return parameters

Posted on 2006-03-30
14
Medium Priority
?
257 Views
Last Modified: 2008-02-01
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
0
Comment
Question by:DingyS
9 Comments
 
LVL 75

Expert Comment

by:Aneesh Retnakaran
ID: 16335282
U dont necessarily need a cursor , u can use a while loop
0
 
LVL 75

Expert Comment

by:Aneesh Retnakaran
ID: 16335340
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
 

Author Comment

by:DingyS
ID: 16335945
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
Fill in the form and get your FREE NFR key NOW!

Veeam is happy to provide a FREE NFR server license to certified engineers, trainers, and bloggers.  It allows for the non‑production use of Veeam Agent for Microsoft Windows. This license is valid for five workstations and two servers.

 
LVL 9

Accepted Solution

by:
sasapopovic earned 2000 total points
ID: 16337324
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
 
LVL 9

Expert Comment

by:sasapopovic
ID: 16337379
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
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 16337723
>>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
 
LVL 2

Expert Comment

by:sureshkgupta1
ID: 16338064
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
 
LVL 9

Expert Comment

by:sasapopovic
ID: 16339720
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
 
LVL 9

Expert Comment

by:sasapopovic
ID: 16774881
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

Featured Post

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In this series, we will discuss common questions received as a database Solutions Engineer at Percona. In this role, we speak with a wide array of MySQL and MongoDB users responsible for both extremely large and complex environments to smaller singl…
What we learned in Webroot's webinar on multi-vector protection.
This is a high-level webinar that covers the history of enterprise open source database use. It addresses both the advantages companies see in using open source database technologies, as well as the fears and reservations they might have. In this…
With just a little bit of  SQL and VBA, many doors open to cool things like synchronize a list box to display data relevant to other information on a form.  If you have never written code or looked at an SQL statement before, no problem! ...  give i…
Suggested Courses

850 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question