Set Rowcount question.

[Ruairi]

I have this statement inside a stored procedure (SQL 6.5 VB6 client using ADO through ODBC)

set rowcount 1 
-- begin search, normal log
select @tsortsrec = @logssortsrec
--Added 5/30/2001 
-- skip the grade-incrementing search if there is no price with this species, sort, vbd, contractor, length
select @lenforanygrade = (select rec from lendiamlen where vbdpricerec = @vbdpricerec and 
			  contractortyperec = @tcontractortyperec and 
			  speciesrec = @speciesrec and 
			  sortsrec = @tsortsrec and 
			  length1 <= @len and 
                          length2 >= @len)

I know it's not legal to assign a querey that returns multiple values to a variable but in this case the set rowcount 1 should take care of that, shouldn't it? I'm getting an error returned 'subquerey returned more than one value' but i can't tell where the error is happening because debugging of SP's in 6.5 really, really sucks.
TIA
Ruairi

Could your manufacturing facility benefit from real time process monitoring? Would you like your employees to be able to see up to the minute goal and actual production?
For innovative, low cost solutions check out my website.

http://www.plccontroltechnologies.com

 

[Cagliostro]

Use top 1 as:

select @lenforanygrade = (select top 1 rec from lendiamlen where vbdpricerec = @vbdpricerec and
contractortyperec .......
John Fill

ivfmd@mail.md

 

[IonelBurtan]

You can use top 1 as JohnFill says, or any other agregate function (e.g. MIN, MAX, SUM etc) applied to the value returned, to make sure that you will get only 1 result from the subquery.

Hope of being of some help, s-)
Blessed is he who in the name of justice and good will, shepards the week through the valley of darknees...

http://home.domaindlx.com/ionelb