Using return values and recordsets

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

Using return values and recordsets

Author

Topic

RoLYroLLs
Constraint Violating Yak Guru

255 Posts

Posted - 2004-07-27 : 15:39:57

Hello all. I have a sproc (below) and was wondering if there was a way I can get the return value before the recordset:

CREATE procedure proc_ADSelectToShow
	@ClientInvoiceDetailTypeID int
as
begin
	select 
		ID
		, ADID
		, ADImage
		, ADTitle
		, ADText
		, ADLink
		, ADLinkExtra
	from 
		AD 
		inner join ClientInvoiceDetail on AD.ClientInvoiceDetailID = ClientInvoiceDetail.ClientInvoiceDetailID 
		inner join ClientInvoice on ClientInvoiceDetail.ClientInvoiceID = ClientInvoice.ClientInvoiceID 
		inner join ClientInvoiceDetailTypePrice on 
		ClientInvoiceDetail.ClassInvoiceDetailTypePriceID = ClientInvoiceDetailTypePrice.ClientInvoiceDetailTypePriceID 
		inner join ClientInvoiceDetailType on 
		ClientInvoiceDetailTypePrice.ClientInvoiceDetailTypeID = ClientInvoiceDetailType.ClientInvoiceDetailTypeID
	where 
		ClientInvoiceDetailType.ClientInvoiceDetailTypeID = @ClientInvoiceDetailTypeID
		and (AD.ADApproved = 1) 
		and (AD.ADActive = 1) 
		and (ClientInvoiceDetail.ClientInvoiceDetailExpired = 0) 
		and (ClientInvoiceDetail.ClientInvoiceDetailPaymentDue = 0) 
		and (ClientInvoiceDetail.ClientInvoiceDetailCancel = 0) 
		and (ClientInvoice.ClientInvoiceVoid = 0) 
	
	return @@rowcount
end
GO

What I'm doing here is trying to get a list of possible ADs to display, generate a random number, n, form 0 to @@rowcount, then move the recordset n times. And display the AD. I know this is inefficient but I had this with Access and had this working with two queries. And while I will not use this method, instead I will just randomly pick the AD at the server level (wondering how I'mma do that) and send the result. I am asking this question in case I may want to use this @@rowcount for some other process in the future.

BTW: any help with generating the randomly displayed AD will be helpful too.

Thanks.

--Edited to remove the sideways scrolling

- RoLY roLLs

RoLYroLLs
Constraint Violating Yak Guru

255 Posts

Posted - 2004-07-27 : 16:28:20

Ok, I think my brain fried too early today.

I am trying to get this sproc to return only one record...a randomly chosen one. I have a sproc that returns a number between two number I supply it:

CREATE procedure proc_RandomNumber
	@LowerBound bigint,
	@UpperBound bigint
as
begin
	declare @RetVal bigint
	select @RetVal = cast(((@UpperBound - @LowerBound + 1) * rand() + @LowerBound) as bigint)
	
	return @RetVal
	--or
	--select @RetVal

	--don't know yet, if it is better to use 'return' or 'select'
	--any light on this issue will be appreciated
end
GO

The problem I have is once I have the random number, which will be the row I want. The random number I ask for is between 1 and the number of rows using the 'count' function. But I'm not sure how to move thru a recordset in T-SQL (if possible, which I doubt.)

CREATE procedure proc_ADSelectToShow
	@ClientInvoiceDetailTypeID int
	, @ID int output
	, @ADID uniqueidentifier output
	, @ADTitle nvarchar(50) output
	, @ADText nvarchar(75) output
	, @ADLink varchar(255) output
	, @ADLinkExtra varchar(255) output
	, @ADImage varchar(255) output
	, @ClientInvoiceDetailTypeSize varchar(25) output
as
begin
	declare @Count int
	select 
		@Count = count(AD.ID)
	from 
		AD 
		inner join ClientInvoiceDetail on AD.ClientInvoiceDetailID = ClientInvoiceDetail.ClientInvoiceDetailID 
		inner join ClientInvoice on ClientInvoiceDetail.ClientInvoiceID = ClientInvoice.ClientInvoiceID 
		inner join ClientInvoiceDetailTypePrice on 
		ClientInvoiceDetail.ClassInvoiceDetailTypePriceID = ClientInvoiceDetailTypePrice.ClientInvoiceDetailTypePriceID 
		inner join ClientInvoiceDetailType on 
		ClientInvoiceDetailTypePrice.ClientInvoiceDetailTypeID = ClientInvoiceDetailType.ClientInvoiceDetailTypeID
	where 
		ClientInvoiceDetailType.ClientInvoiceDetailTypeID = @ClientInvoiceDetailTypeID
		and (AD.ADApproved = 1) 
		and (AD.ADActive = 1) 
		and (ClientInvoiceDetail.ClientInvoiceDetailExpired = 0) 
		and (ClientInvoiceDetail.ClientInvoiceDetailPaymentDue = 0) 
		and (ClientInvoiceDetail.ClientInvoiceDetailCancel = 0) 
		and (ClientInvoice.ClientInvoiceVoid = 0) 
	
	declare @Random int
	exec @Random = proc_RandomNumber(1,@Count)
	--I'm stuck here.
end
GO

The next statment would be to output the one row I want in which the 'where' clause should be the same. I'm trying to see if I can accomplish this with just one select statement, but it's not an issue if I can't. I hope someone can shed some light on any part of this.

--Edited to remove sideways scroling

- RoLY roLLs

RoLYroLLs
Constraint Violating Yak Guru

255 Posts

Posted - 2004-07-27 : 17:46:02

Hmm. I'm going to do more research on this, but I just read a topic which had something about FETCH NEXT... Can someone explain that to me in plain english while I research it in BOL and surely find it to be all technical.

Thanks.

- RoLY roLLs

RoLYroLLs
Constraint Violating Yak Guru

255 Posts

Posted - 2004-07-27 : 20:17:11

Well looks like I learned something new today...cursors! I thought It may have been something else than what it was and never thought I'd need it and it worked out great for what I needed to do. I am posting my code incase anyone has any improvements they can help me add to it to maybe make it perform better, if possible.

CREATE procedure proc_ADSelectToShow
	@ClientInvoiceDetailTypeID int
	, @ADID int output
	, @ID uniqueidentifier output
	, @ADTitle nvarchar(50) output
	, @ADText nvarchar(75) output
	, @ADLink varchar(255) output
	, @ADLinkExtra varchar(255) output
	, @ADImage varchar(255) output
	, @ClientInvoiceDetailTypeSize varchar(25) output
as
begin
	declare @Count int
	select 
		@Count = count(AD.ADID)
	from 
		AD 
		inner join ClientInvoiceDetail on AD.ClientInvoiceDetailID = ClientInvoiceDetail.ClientInvoiceDetailID 
		inner join ClientInvoice on ClientInvoiceDetail.ClientInvoiceID = ClientInvoice.ClientInvoiceID 
		inner join ClientInvoiceDetailTypePrice on 
		ClientInvoiceDetail.ClassInvoiceDetailTypePriceID = ClientInvoiceDetailTypePrice.ClientInvoiceDetailTypePriceID 
		inner join ClientInvoiceDetailType on 
		ClientInvoiceDetailTypePrice.ClientInvoiceDetailTypeID = ClientInvoiceDetailType.ClientInvoiceDetailTypeID
	where 
		ClientInvoiceDetailType.ClientInvoiceDetailTypeID = @ClientInvoiceDetailTypeID
		and (AD.ADApproved = 1) 
		and (AD.ADActive = 1) 
		and (ClientInvoiceDetail.ClientInvoiceDetailExpired = 0) 
		and (ClientInvoiceDetail.ClientInvoiceDetailPaymentDue = 0) 
		and (ClientInvoiceDetail.ClientInvoiceDetailCancel = 0) 
		and (ClientInvoice.ClientInvoiceVoid = 0) 
	
	declare @Random int
	exec @Random = proc_RandomNumber 1,@Count
	
	declare ad_cursor scroll cursor for
	select 
		AD.ADID
		, AD.ID
		, AD.ADTitle
		, AD.ADText
		, AD.ADLink
		, AD.ADLinkExtra
		, AD.ADImage
		, ClientInvoiceDetailType.ClientInvoiceDetailTypeSize
	from 
		AD 
		inner join ClientInvoiceDetail on AD.ClientInvoiceDetailID = ClientInvoiceDetail.ClientInvoiceDetailID 
		inner join ClientInvoice on ClientInvoiceDetail.ClientInvoiceID = ClientInvoice.ClientInvoiceID 
		inner join ClientInvoiceDetailTypePrice on 
		ClientInvoiceDetail.ClassInvoiceDetailTypePriceID = ClientInvoiceDetailTypePrice.ClientInvoiceDetailTypePriceID 
		inner join ClientInvoiceDetailType on 
		ClientInvoiceDetailTypePrice.ClientInvoiceDetailTypeID = ClientInvoiceDetailType.ClientInvoiceDetailTypeID
	where 
		ClientInvoiceDetailType.ClientInvoiceDetailTypeID = @ClientInvoiceDetailTypeID
		and (AD.ADApproved = 1) 
		and (AD.ADActive = 1) 
		and (ClientInvoiceDetail.ClientInvoiceDetailExpired = 0) 
		and (ClientInvoiceDetail.ClientInvoiceDetailPaymentDue = 0) 
		and (ClientInvoiceDetail.ClientInvoiceDetailCancel = 0) 
		and (ClientInvoice.ClientInvoiceVoid = 0) 
	
	open ad_cursor
	
	fetch absolute 
		@Random 
	from 
		ad_cursor
	into 
		@ADID
		, @ID
		, @ADTitle
		, @ADText
		, @ADLink
		, @ADLinkExtra
		, @ADImage
		, @ClientInvoiceDetailTypeSize
	
	close ad_cursor
	deallocate ad_cursor
	
	return @Random
end
GO

Not too familiar with performace issue, if any, with cursors. If there are, please let me know, like if maybe cursors are not good for over 10,000 records or whatnot. Thanks.

- RoLY roLLs

Pat Phelan
Posting Yak Master

187 Posts

Posted - 2004-07-27 : 22:51:29

In MS-SQL, Cursors are occaisionally necessary but they should be an absolute last resort. I use a cursor only when I don't have another choice that will get the job done.

In this case, I'd prefetch the rowcount as you've done, then done something that would resemble:

SELECT TOP 1 *
   FROM (SELECT TOP N PERCENT this, that, theOtherThing
      FROM myTable
      ORDER BY this DESC) AS a
   ORDER BY this

-PatP

Itinerant Curmudgeon

RoLYroLLs
Constraint Violating Yak Guru

255 Posts

Posted - 2004-07-28 : 12:21:51

I have read so far in and around this forum that using TOP cannot be dynamic, correct? So how do I get a different record for each execution using your method and without dynamically creating the sql statement if I can't change N?

Thanks.

- RoLY roLLs

Pat Phelan
Posting Yak Master

187 Posts

Posted - 2004-07-28 : 14:56:09

To do something really kinky like provide an example:

DECLARE @cRow		VARCHAR(10)
SET @cRow = 52

EXECUTE ('SELECT TOP 1 *
   FROM (SELECT TOP ' + @cRow + ' 
      AD.ADID
,     AD.ID
,     AD.ADTitle
,     AD.ADText
,     AD.ADLink
,     AD.ADLinkExtra
,     AD.ADImage
,     ClientInvoiceDetailType.ClientInvoiceDetailTypeSize
         FROM 
            AD 
            inner join ClientInvoiceDetail
               ON AD.ClientInvoiceDetailID 
=                 ClientInvoiceDetail.ClientInvoiceDetailID 
            inner join ClientInvoice
               on ClientInvoiceDetail.ClientInvoiceID 
=                 ClientInvoice.ClientInvoiceID 
            inner join ClientInvoiceDetailTypePrice
               on ClientInvoiceDetail.ClassInvoiceDetailTypePriceID 
=                 ClientInvoiceDetailTypePrice.ClientInvoiceDetailTypePriceID 
            inner join ClientInvoiceDetailType
               on ClientInvoiceDetailTypePrice.ClientInvoiceDetailTypeID 
=                 ClientInvoiceDetailType.ClientInvoiceDetailTypeID
         WHERE ClientInvoiceDetailType.ClientInvoiceDetailTypeID 
=              @ClientInvoiceDetailTypeID
            and (AD.ADApproved = 1) 
            and (AD.ADActive = 1) 
            and (ClientInvoiceDetail.ClientInvoiceDetailExpired = 0) 
            and (ClientInvoiceDetail.ClientInvoiceDetailPaymentDue = 0) 
            and (ClientInvoiceDetail.ClientInvoiceDetailCancel = 0) 
            and (ClientInvoice.ClientInvoiceVoid = 0) 
         ORDER BY AD.ADID)
   ORDER BY AD.ADID DESC)
   ')

-PatP

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2004-07-29 : 07:55:09

if i understand your problem correctly wouldn't this be something you need?

declare @RowNum int
set @RowNum = 20
SET ROWCOUNT @RowNum
select *
into #temp
from table

select top 1 * from #temp
order by newid()

Go with the flow & have fun! Else fight the flow :)

RoLYroLLs
Constraint Violating Yak Guru

255 Posts

Posted - 2004-07-29 : 12:51:20

quote:
Originally posted by spirit1

if i understand your problem correctly wouldn't this be something you need?

declare @RowNum int
set @RowNum = 20
SET ROWCOUNT @RowNum
select *
into #temp
from table

select top 1 * from #temp
order by newid()

Go with the flow & have fun! Else fight the flow :)

That seems good enough, but I read that temp tables could have performance issues or something. Is it better to use temp tebles vs cursors? and Why?

- RoLY roLLs

Pat Phelan
Posting Yak Master

187 Posts

Posted - 2004-07-29 : 14:39:00

My proposed solution doesn't use temp tables. As a general rule, temp tables are preferred to cursors, although there are exceptions to that rule.

-PatP

RoLYroLLs
Constraint Violating Yak Guru

255 Posts

Posted - 2004-07-29 : 15:23:23

Thanks for the rule, pat. As far as your example...I prefer not to use dynamic sql, tho. But I will if I have no other choice. Thanks

- RoLY roLLs

kselvia
Aged Yak Warrior

526 Posts

Posted - 2004-07-29 : 19:16:02

I don't quite see exactly this above so forgive me if it's implied someplace, but you should be able to select a random row with


SELECT TOP 1 newid(), ..rest of columns...
FROM Mytable
Order By 1

without a temp table.

--Ken
Your Kung-Fu is not strong. -- 'The Core'

RoLYroLLs
Constraint Violating Yak Guru

255 Posts

Posted - 2004-07-29 : 19:59:42

wow! that seems to work wonderful! you're a genuis!

- RoLY roLLs

RoLYroLLs
Constraint Violating Yak Guru

255 Posts

Posted - 2004-07-29 : 20:01:05

btw" just for knowledge, what is it that makes that work...like that 'order by 1'. How's that work?

- RoLY roLLs

kselvia
Aged Yak Warrior

526 Posts

Posted - 2004-07-29 : 20:36:24

1 means the first column of the result set (newid() in this case). I'm no genuis, I've just learned a lot here @ SQLTeam :)

It's not going to be the most efficient way to do what you want in the long run because it actually has to scan the whole table. Your rand() function

while 1 = 1
begin
select * from MyTable where ID = dbo.MyRand((select min(id) from MyTable), (select max(id) from MyTable))
If @@rowcount > 0 BREAK
END

or something like that is proabably better performance wise. That assumes not too many gaps in your ID column. It will requery if it misses. If there are a lot of gaps, it would be better to create a new column in your table that IS contiguous that you refresh with an incrementing counter daily.

Declare @Counter int
SET @counter = 0
Update AD
set @counter = lookup = @counter + 1

You could also create a seperate lookup table that is built daily

Select lookupID = identity(int,1,1), ID INTO ADLookup From AD

Then use;

Select * from AD, ADLookup where AD.ID = Lookup.ID and lookupID = dbo.MyRand(1, (select count(1) from ADlookup))

--Ken
Your Kung-Fu is not strong. -- 'The Core'

RoLYroLLs
Constraint Violating Yak Guru

255 Posts

Posted - 2004-07-29 : 20:53:57

yeah one problem with the first one, is that the ad must be active, approved, and made sure that payment has been made for it, and has not expired (if it does), so for sure there will be gaps in the long run. I'll take a look at the lookup suggestion you offered. Thanks.

- RoLY roLLs

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2004-07-30 : 05:29:40

here is the same thing without the temp table:

SET ROWCOUNT 20
select top 1 *
from (select * from scmSupplyPlanPositions SPP) a
order by newid()

it has only one tablescan... :)

kselvia:
it similar to your thing, i just hate to have any more coulumns in the select than really needed :))))

Go with the flow & have fun! Else fight the flow :)

RoLYroLLs
Constraint Violating Yak Guru

255 Posts

Posted - 2004-07-30 : 10:18:41

hey spirit, that's exactly how i made it, and was about to ask a question on the set rowcount part. The thing is I just need one record returned and i know the set rowcount limits to result to the number of records, but my question concerning the set rowcount is: say at set rowcount 20 and order by newid(), would the rowcount of 20 only look at the first 20 records, before or after the order by? If it's after, then I can set rowcount to 1, can't l? without the top 1 part. I'll test this out tho.

- RoLY roLLs

RoLYroLLs
Constraint Violating Yak Guru

255 Posts

Posted - 2004-07-30 : 10:22:58

Here's what I got so far, and it works great: (seemingly)

	set rowcount 1
	select
		@ADID = AD.ADID
		, @ADUID = dbo.DeFormatGUID(AD.ADUID)
		, @ADTitle = AD.ADTitle
		, @ADText = AD.ADText
		, @ADLink = AD.ADLink
		, @ADLinkExtra = AD.ADLinkExtra
		, @ADImage = AD.ADImage
		, @ClientInvoiceDetailTypeSize = ClientInvoiceDetailType.ClientInvoiceDetailTypeSize
	from
		AD
		inner join ClientInvoiceDetail on AD.ClientInvoiceDetailID = ClientInvoiceDetail.ClientInvoiceDetailID
		inner join ClientInvoice on ClientInvoiceDetail.ClientInvoiceID = ClientInvoice.ClientInvoiceID
		inner join ClientInvoiceDetailTypePrice on ClientInvoiceDetail.ClientInvoiceDetailTypePriceID = 
		ClientInvoiceDetailTypePrice.ClientInvoiceDetailTypePriceID
		inner join ClientInvoiceDetailType on ClientInvoiceDetailTypePrice.ClientInvoiceDetailTypeID = 
		ClientInvoiceDetailType.ClientInvoiceDetailTypeID
	where
		(ClientInvoiceDetailType.ClientInvoiceDetailTypeID = @ClientInvoiceDetailTypeID)
		and (AD.ADApproved = 1)
		and (AD.ADActive = 1)
		and (ClientInvoiceDetail.ClientInvoiceDetailExpired = 0)
		and (ClientInvoiceDetail.ClientInvoiceDetailPaymentDue = 0)
		and (ClientInvoiceDetail.ClientInvoiceDetailCancel = 0)
		and (ClientInvoice.ClientInvoiceVoid = 0)
	order by
		newid()

- RoLY roLLs

RoLYroLLs
Constraint Violating Yak Guru

255 Posts

Posted - 2004-07-30 : 10:24:31

with that, the i assume the set rowcount is after the order by (as I thought i should). anyone see any performance issues in the long run?

- RoLY roLLs

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2004-07-30 : 10:30:28

rowcount is executed last. first the query is processed, then rowcount applied.

you can do it the way you're doing it now :))) and i don't see anything wrong with it performace wise....
maybe someone else can.... :)))

Go with the flow & have fun! Else fight the flow :)

Next Page

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources