how to write this procedure? - SQL Server Forums

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)

how to write this procedure?

Author

Topic

mnttr
Starting Member

9 Posts

Posted - 2002-09-17 : 08:17:04

table
t1
(id int identity,
value int )

i want to write a procedure like:

create proc proc1(@n int,@value int)
as
---get the random @n record and sum(value)=@value and return the all records id

Page47
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2002-09-17 : 08:27:20

Dynamic SQL or How do I SELECT TOP @var records?

Jay White
{0}

mnttr
Starting Member

9 Posts

Posted - 2002-09-17 : 08:37:16

Page47 :thank you!
but how to do?

mnttr
Starting Member

9 Posts

Posted - 2002-09-17 : 08:48:14

I have write a procedure :

create proc proc1(@n int,@value int)
as
begin
declare @a int
while @a<>@value
begin
exec('select top '+cast(@n varchar(10))+' into ##temp from t1 order by newid()')
select @a=sum(value) from ##temp
if @a<>@value
drop table ##temp
end
end

---But it is to slowly. Who can help me?

Page47
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2002-09-17 : 08:51:11


create proc proc1
    @n int,
    @value int out
as
set nocount on

declare
    @vSQL nvarchar(2000),
    @params nvarchar(500)

select
    @vSQL =
    N'select
        @v = sum(qty)
    from
        (select top ' + convert(nvarchar,@n) + N'
            qty
        from
            dbo.t1
        order by
            newid() ) as a',
    @params = 
    N'@v int out'

exec master.dbo.sp_executesql
    @vSQL,
    @params,
    @value OUT
go

Jay White
{0}

Edited by - Page47 on 09/17/2002 08:52:52

mnttr
Starting Member

9 Posts

Posted - 2002-09-17 : 09:01:17

Page47 :no!
i want to use the @n and thne @value to get the random records

Page47
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2002-09-17 : 09:20:41

"No!", huh ... I should have known better than to try to help you out ...

Why don't you clarify your requirements a bit by providing actual DDL, some sample data and a sample of the expected results of this proc. That way, the next fool that comes along to try to help doesn't waste his/her time writing free code for you only to be told "No!" ...

Jay White
{0}

mnttr
Starting Member

9 Posts

Posted - 2002-09-17 : 09:30:32

Page47
I am sorry! Can you help me again?

robvolk
Most Valuable Yak

15732 Posts

Posted - 2002-09-17 : 09:58:36

quote:
Page47
I am sorry! Can you help me again?

Nope, I think you lost him. Can't say I blame him either. Chalk this one up as a learning experience.

I suggest you take his advice to heart and learn to be courteous at all times, even if you didn't intend to sound otherwise.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources