Work around for returning an array in a stored procedure

[mbro]

Hi,

I'm just starting out with SQL programming and would like a way to return an array in a stored procedure. Currently my stored procedure looks as follows:

Alter Procedure prcGetJobDrawings
@JobNoItemNo varchar(15),
@DrawNoItemNo varchar(15) output
AS

IF (SELECT COUNT(JobNoItemNo) FROM tblJobDraw WHERE JobNoItemNo = @JobNoItemNo) = 0
BEGIN
SELECT @DrawNoItemNo = NULL
END
ELSE
SELECT @DrawNoItemNo = DrawNoItemNo
FROM tblJobDraw
WHERE JobNoItemNo = @JobNoItemNo


This returns only the one value for DrawNoItemNo but now I'm facing situations where there are multiple DrawNoItemNo for each JobNoItemNo. How can I get it to return an array of these values? I'm calling this procedure through a seperate VB application and will be using the array in VB app if this helps.

Thanks for the help,
Mike

 

[SQLDenis]

What about?
Alter Procedure prcGetJobDrawings
@JobNoItemNo varchar(15)
AS

IF (SELECT COUNT(JobNoItemNo) FROM tblJobDraw WHERE JobNoItemNo = @JobNoItemNo) = 0
BEGIN
SELECT NULL
END
ELSE
SELECT DrawNoItemNo
FROM tblJobDraw
WHERE JobNoItemNo = @JobNoItemNo

Get rid of the ouput parameter and return a resultset
you can probably get rid of the if and select NULL part also

Denis The SQL Menace
SQL blog:

http://sqlservercode.blogspot.com/

Personal Blog:

http://otherthingsnow.blogspot.com/

 

[gmmastros]

You can't return an array, but you can return a comma delimited string (and then use VB's split function to get your array).

Alter Procedure prcGetJobDrawings
    @JobNoItemNo        varchar(15),
    @DrawNoItemNo    varchar(8000)    output
AS

Select @DrawNoItemNo = @DrawNoItemNo + ',' + @DrawNoItemNo
From   tblJobDraw
Where  JobNoItemNo = @JobNoItemNo

If Left(@DrawNoItemNo, 1) = ','
  Select @DrawNoItemNo = Right(@DrawNoItemNo, Len(@DrawNoItemNo) - 2)

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[mbro]

Thanks, both solutions will work out great.

Cheers,
Mike

 

[druer]

Option 1: You could modify @DrawNoItemNo to a varchar(8000) and simply concatenate the values you want to return through it with some delimiter that your VB code can then parse.

Option 2: You could create a User Defined Function instead of using the Stored Procedure and return a TABLE variable as the result so you can have as many as you want and then have your VB code process the resulting TABLE.

 

[mbro]

Hi,

I'm actually having a problem with the code correction to my initial question...

Alter Procedure prcGetJobDrawings
@JobNoItemNo varchar(15),
@DrawNoItemNo varchar(8000) output
AS

Select @DrawNoItemNo = @DrawNoItemNo + ',' + @DrawNoItemNo
From tblJobDraw
Where JobNoItemNo = @JobNoItemNo

If Left(@DrawNoItemNo, 1) = ','
Select @DrawNoItemNo = Right(@DrawNoItemNo, Len(@DrawNoItemNo) - 2)


I changed the select command so that the second entry didn't have the @ symbol but it still returns Null each time. I've checked my test table to ensure it's correct and have run my previous version to see that it would select at least one and does.

If anyone can spot the error that'd be great.

Cheers,
Mike

 

[gmmastros]

Sorry. I missed that. The problem is that when you add NULL to anything, you always get null. To correct this, you need to initialize the variable

Alter Procedure prcGetJobDrawings
    @JobNoItemNo        varchar(15),
    @DrawNoItemNo    varchar(8000)    output
AS

[red]Set @DrawNoItemNo = ''[/red]

Select @DrawNoItemNo = @DrawNoItemNo + ',' + @DrawNoItemNo
From   tblJobDraw
Where  JobNoItemNo = @JobNoItemNo

If Left(@DrawNoItemNo, 1) = ','
  Select @DrawNoItemNo = Right(@DrawNoItemNo, Len(@DrawNoItemNo) - 1)

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[SQLDenis]

A typo
Select @DrawNoItemNo = @DrawNoItemNo + ',' + @DrawNoItemNo
should be
Select @DrawNoItemNo = @DrawNoItemNo + ',' + DrawNoItemNo



Denis The SQL Menace
SQL blog:

http://sqlservercode.blogspot.com/

Personal Blog:

http://otherthingsnow.blogspot.com/

 

[gmmastros]

I quess I'm not having a good day. Thanks for pointing that out Denis.

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[mbro]

Thanks, I caught the error mentioned in the second post but missed the first one entirely. It actually works better for me as:

Select @DrawNoItemNo = DrawNoItemNo + ',' + @DrawNoItemNo

Once again thanks for the help both of you.

Cheers,
Mike