limit rowset return
-
HI
Does anyone know how to limit the number of rows returned by a query??? Say - return the first 5 results from the following query.. I need to specify my number of results returned through a variable on my VB form..SELECT employees.lastname, employees.firstname,
employees.ssn
FROM employees INNER JOIN
picks ON employees.employeeid = picks.employeeid
WHERE (picks.diff > 60) AND (employees.poolname = ?)
I'm stumped.
thanks!
kristin
Kristin
-
First, I do not know why you are posting in the administration forum.
second,
if you want a variable amount of records to be displayed. Like a web search "show 25" or "show 50"
you have 2 options
1. parameterize the return number, build a dynamic query, return only the specified number.
Use Northwind
Create Proc CustomerSearch
@returnNumber int,
@SearchString Varchar(50)
as
Declare @sql varchar(500)
set @sql = 'Select top ' + Cast(@returnNumber as Varchar) + ' * from Customers where CompanyName like''%' + @searchString + '%'''
Execute(@sql)
2. Return the max number and then only show the top N in your application.
Create Proc CustomerSearch
@SearchString Varchar(50)
as
Select top 50 from Customers where CompanyName like '%' + @searchString + '%'
and then in the vb side limit your view
HTH
tal McMahon
-
Assuming the number of results desired is stored within @MaxResults you can use the following:
set ROWCOUNT @MaxResults
You could use the TOP option as well, but since it is a variable number you would need to run it within dynamic SQL which isn't good.
-
wow, super sorry if I offended anyone by posting in the wrong forum, my mistake, won't happen again.
Thanks for the help anyway! I'll post in the other forum.
regards
Kristin
-
No blood, no foul, Kristin...
I'm sure his comment was taken out of context, and was meant simply as a gentle reminder. After all, I am darn near positive nobody would take the effort and burn the calories involved with getting upset over such a thing.
aka "Paul"
Non est ei similis.Any man who can drive safely while kissing a pretty girl is simply not giving the kiss the attention it deserves. ~ Albert Einstein
-
nice hat paul, thanks
did you give me that nice rookie pic? hahahah!Kristin
-
Heck No, Kristen...I can't do nothin' like that...I only have three posts
...and thanks!
aka "Paul"
Non est ei similis.Any man who can drive safely while kissing a pretty girl is simply not giving the kiss the attention it deserves. ~ Albert Einstein
-
hahahah, touche! And quick question, as long as I have your
In your first code example, can I use a variable like that in the query builder? I write all my queries there, and call with a command in VB. I have no idea how write a dynamic query and display it on a vb form.
Kristin
-
Well,
In My Opinion you are opening a big kettle of fish by passing the queries to the sql server. Among them:
1. Your SQL server is vulnerable to ad hoc queries as all of your tables have to grant select,update,delete permissions to your application Login. This opens the door for SQL injection.
2. Your Procedures have to be compiled By SQL every time they run.
3. You have to write the same query several times.....Change One You Change them all.
4. You cannot have a "Super Procedure" that Returns Multiple recordsets based on data retrieved in the procedure.
5. If you should have to run a cursor (ewww), it means multiple calls to the database.
well you see what i mean.
now to answer your question,
In reality you are building a query every time you call the database so just make your SQL statement reflect how many you need:
dim searchstring as string
dim returncount as integer
dim sql as string
sql="Select top " & returncount.tostring & " * from Customers where CompanyName like '%'" + @searchString + "'%'"
watch the single double quotes on that....
hope that helps
Tal McMahon
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply