asked on

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

Aneesh

U dont necessarily need a cursor , u can use a while loop

Aneesh

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

DingyS

ASKER

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

ASKER CERTIFIED SOLUTION

sasapopovic

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

sasapopovic

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 Perkins

>>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.

sureshkgupta1

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.

sasapopovic

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.

sasapopovic

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 :-)