Stored Proc  
Author Message
marvidsson





PostPosted: Fri Nov 05 12:35:03 CST 2004 Top

SQL Server Developer >> Stored Proc

Hi,

I have an sql in a stored procedure but I am getting an erro.

@NumOfCust int --Parameter











C.Addr1, C.Addr2, C.City --etc




@NumOfCustomer and add 50 it works


Thanks
Dib

SQL Server225  
 
 
sqlnr





PostPosted: Fri Nov 05 12:35:03 CST 2004 Top

SQL Server Developer >> Stored Proc top does not allow a variable. you can use set rowcount instead


SELECT t.SumOfSales, t.CustId, C.CustName, C.Contact,
C.Addr1, C.Addr2, C.City --etc
set rowcount 0




> Hi,
>
> I have an sql in a stored procedure but I am getting an erro.
>

>
>

>

>
>
>
>
>

> C.Addr1, C.Addr2, C.City --etc
>
>
>


>
>
> Thanks
> Dib
>
>
>
 
 
AlejandroMesa





PostPosted: Fri Nov 05 12:39:02 CST 2004 Top

SQL Server Developer >> Stored Proc It does not allow a variable. You can use SET ROWCOUNT if you want to pass a
variable or use some kind of ranking.

Example:

use northwind
go






select orderid, orderdate from orders order by orderId asc
set rowcount 0

-- or

select
orderid,
orderdate
from
orders a
where

order by
1



AMB



> Hi,
>
> I have an sql in a stored procedure but I am getting an erro.
>

>
>

>

>
>
>
>
>

> C.Addr1, C.Addr2, C.City --etc
>
>
>


>
>
> Thanks
> Dib
>
>
>
 
 
Dib





PostPosted: Fri Nov 05 12:40:25 CST 2004 Top

SQL Server Developer >> Stored Proc Yes the rowcount works, but I am looking to get the highest sale amount by
the number the user input. example 50 or 100

The rowCount gives the 50 or so rows which is can contain 0 $ amount..


Thanks



> top does not allow a variable. you can use set rowcount instead
>

> SELECT t.SumOfSales, t.CustId, C.CustName, C.Contact,
> C.Addr1, C.Addr2, C.City --etc
> set rowcount 0
>
>

>
> > Hi,
> >
> > I have an sql in a stored procedure but I am getting an erro.
> >

> >
> >

> >

> >
> >
> >
> >
> >

> > C.Addr1, C.Addr2, C.City --etc
> >
> >
> >


> >
> >
> > Thanks
> > Dib
> >
> >
> >


 
 
Aaron





PostPosted: Fri Nov 05 14:21:01 CST 2004 Top

SQL Server Developer >> Stored Proc > Yes the rowcount works, but I am looking to get the highest sale amount by
> the number the user input. example 50 or 100

You need to use ORDER BY, I presume. TOP and ROWCOUNT merely limit the
resultset to n rows. If you want a SPECIFIC set of n rows, you need to tell
SQL Server which n you want.

--
http://www.aspfaq.com/
(Reverse address to reply.)


 
 
Dib





PostPosted: Fri Nov 05 14:39:02 CST 2004 Top

SQL Server Developer >> Stored Proc Thanks.

I put the RowCount just before the Select and it worked.

Dib



> It does not allow a variable. You can use SET ROWCOUNT if you want to pass
a
> variable or use some kind of ranking.
>
> Example:
>
> use northwind
> go
>

>

>

> select orderid, orderdate from orders order by orderId asc
> set rowcount 0
>
> -- or
>
> select
> orderid,
> orderdate
> from
> orders a
> where

> order by
> 1
>
>
>
> AMB
>

>
> > Hi,
> >
> > I have an sql in a stored procedure but I am getting an erro.
> >

> >
> >

> >

> >
> >
> >
> >
> >

> > C.Addr1, C.Addr2, C.City --etc
> >
> >
> >


> >
> >
> > Thanks
> > Dib
> >
> >
> >


 
 
Dib





PostPosted: Fri Nov 05 14:39:30 CST 2004 Top

SQL Server Developer >> Stored Proc Thanks
Dib



> > Yes the rowcount works, but I am looking to get the highest sale amount
by
> > the number the user input. example 50 or 100
>
> You need to use ORDER BY, I presume. TOP and ROWCOUNT merely limit the
> resultset to n rows. If you want a SPECIFIC set of n rows, you need to
tell
> SQL Server which n you want.
>
> --
> http://www.aspfaq.com/
> (Reverse address to reply.)
>
>


 
 
Aaron





PostPosted: Fri Nov 05 15:20:07 CST 2004 Top

SQL Server Developer >> Stored Proc > I put the RowCount just before the Select and it worked.

Make sure to put SET ROWCOUNT 0 right after the SELECT.

--
http://www.aspfaq.com/
(Reverse address to reply.)


 
 
Dib





PostPosted: Fri Nov 05 15:50:46 CST 2004 Top

SQL Server Developer >> Stored Proc Thanks
Dib



> > I put the RowCount just before the Select and it worked.
>
> Make sure to put SET ROWCOUNT 0 right after the SELECT.
>
> --
> http://www.aspfaq.com/
> (Reverse address to reply.)
>
>