SELECT SqlCommand

Question

I know this is a simple question, but I can't get it to work.

This is my query in my SqlCommand:

SELECT * FROM zipcode  WHERE city LIKE @prefixtext + '%' ;

I only want 10 results, every other answer suggests

SELECT TOP 10 * FROM zipcode  WHERE city LIKE @prefixtext + '%' ;
SELECT * FROM zipcode  WHERE city LIKE @prefixtext + '%'  LIMIT 10 ;

both do not work

I'm likely wrong on this since I'm not very familiar with SQL Server, but I don't think that + is concatenation. — Corbin, Jun 07 '12 at 02:55
Is `zipcode` really your table name, or the name of the column you want to retrieve? Basic query format is `SELECT column_name` (or `*` for all columns) ` FROM table_name WHERE ...` — bfavaretto, Jun 07 '12 at 02:55
so what does the the error say ? Are you sure the computer has gas in it? Is it plugged in ? Maybe there's a bug... Seriously, you need to provide more inormation about what is wrong than just "both do not work" ... Are you getting 11 records instead of 10 ? or are you getting records from the wrong server ? or from the wrong table? no records at all? — Charles Bretana, Jun 07 '12 at 02:58
TOP 10 is SQL Server specific. LIMIT 10 works on MySQL (maybe also Oracle?). Which DB are you using? What error or incorrect result do you get? — Eric J., Jun 07 '12 at 02:58
SELECT TOP 10 * FROM zipcode WHERE city LIKE @prefixtext + '%' would definitely work in MSSQL, limit 10 is not MSSQL keyword. What is the error here? — fenix2222, Jun 07 '12 at 03:01
ok, error with 'LIMIT 10' - SELECT TOP 10 returning nothing , has to be bad query , thank you — Scott Selby, Jun 07 '12 at 03:03
try replacing star with actual column name you want to return. When you remove TOP 10 from query does it work? — fenix2222, Jun 07 '12 at 03:04
Could you show the code where you populate the `SqlCommand` object? — rikitikitik, Jun 07 '12 at 03:11

score 2 · Accepted Answer · answered Jun 07 '12 at 04:49

I believe these are all correct.

Oracle:

select * from zipcode where city like @prefixtext + '%' and rownum <=10

SQL Server/Sybase:

select top 10 * from zipcode where city like @prefixtext + '%'

DB2/PostgreSQL:

select * from zipcode where city like @prefixtext || '%' fetch first 10 rows only

MySQL:

select * from zipcode where city like @prefixtext + '%' limit 10

score 0 · Answer 2 · edited Jun 07 '12 at 09:53

0

declare @like varchar(50)

set @like = @prefixtext + '%';

SELECT TOP 10 * FROM zipcode  WHERE city LIKE @like

edited Jun 07 '12 at 09:53

Gonzalo.-

answered Jun 07 '12 at 04:41

vijay

score -1 · Answer 3 · edited Sep 11 '12 at 10:11

-1

Select * from zipcode where city like @prefixtext + '%'

edited Sep 11 '12 at 10:11

j0k

answered Sep 11 '12 at 09:57

vivek

3 Answers3