Article: Dynamic SQL or How do I SELECT TOP @var records? - SQL Server Forums

Home | Weblogs | Forums

Site Sponsored By: SQLDSC - SQL Server Desired State Configuration

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

Site Related Forums

Article Discussion

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

Author

Topic

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2000-07-31 : 10:55:47

Ben writes "I'm trying to create a stored procedure where I can send "sp_GetTopRecordSet 25" and it will return a recordset of the top 25 records of my query, like: "SELECT TOP @n * FROM MyTable ORDER BY DateColumn" Now, why won't this work?"

Article Link.

Peter Dutch
Posting Yak Master

127 Posts

Posted - 2002-03-29 : 09:24:42

SET ROWCOUNT doesn't work when you have an Order By clause.

You get the first 25 records, not the 25 records ordered by the column you specified in the Order By clause.
Check BOL for more information...

robvolk
Most Valuable Yak

15732 Posts

Posted - 2002-03-29 : 11:09:30

quote:
SET ROWCOUNT doesn't work when you have an Order By clause.

Works fine for me. SQL Server 2000. I'm positive I've done this in SQL 6.5 too, and it worked fine.

fishlens
Starting Member

1 Post

Posted - 2002-09-24 : 23:03:55

I made a SP that accepts a Select Statement(as a varchar string) and then executes it. The Dynamnic SP works great, however,
it won't work with the correct Select Statement Syntax;
it only works with BAD SYNTAX.

Correct / Normal Syntax (has apostrophe's around fields)
Select * from Table Where LNAME = 'Jones'

The Stored Procedure only works if I do this:
Select * from Table Where LNAME = Jones
(without apostrophe's or chr(39's)

It really makes no sense. Does anyone here know how this
could work without proper syntax and won't work with correct
syntax?

.................................
I'm on SQL 2000.

Here's my SP:

CREATE PROCEDURE pagex
@sqlx varchar(1000)
AS
declare @vSQL varchar(1000)
select @vSQL = @sqlx
Execute (@vSQL)
.............................

Here's my ASP (short version)

cxstring="my connection string >> it's a dsn connection"
set cx = Server.CreateObject("ADODB.Connection")
cx.open cxstring
set rs = Server.CreateObject("ADODB.Recordset")

sp = "pagex" ' name of my SP

metx=cstr("1187") ' for my Metro Field which is a string in KY table
lastnum="21" ' for the numx field which is INT in the KY table

sqlx= "SELECT top 20 * FROM KY where metro = "
sqlx=sqlx & metx
sqlx=sqlx & " AND numx > "
sqlx=sqlx & cint(lastnum)
sqlx=sqlx & " order by numx"

sp=sp & " " & chr(39) & sqlx & chr(39)

set rs = cx.Execute(sp) 'execute stored procedure

......................................

Anyway, like I said, it works damn good, but i just can't understand why I don't need to format my SQLX variable using apostrophe's
around the metx field like this:
chr(39) & metx & chr(39)

Thanks for any help. (sorry this is so long)

David
Bashful.com

jgoemat
Starting Member

2 Posts

Posted - 2003-06-11 : 17:13:38

quote:

I made a SP that accepts a Select Statement(as a varchar string) and then executes it. The Dynamnic SP works great, however,
it won't work with the correct Select Statement Syntax;
it only works with BAD SYNTAX.

Correct / Normal Syntax (has apostrophe's around fields)
Select * from Table Where LNAME = 'Jones'

The Stored Procedure only works if I do this:
Select * from Table Where LNAME = Jones
(without apostrophe's or chr(39's)

...

David
Bashful.com

Why it works how you do it: It looks like your "LNAME = Jones" example is made up. In your actual code, you're using a number, not a string. If you query a string column for a numerical value, SQL Server will attempt to convert those strings to numbers. Try to insert a non-numeric value into the metro column like 'jdkd' and I bet you will get an error when you run the query saying SQL Server couldn't convert it.

Why it doesn't work with apostrophes: The problem is you are enclosing the sql in apostrophes to pass it as a string to your stored procedure. If you have a variable in apostrophes in there, it thinks the string ends at the first apostrophe. Example:
exec pagex 'SELECT top 20 * FROM KY where metro = '1187''

To SQL, it looks like the command "exec pagex" executed with a string 'SELECT top 20 * FROM KY where metro = ', then the characters 1187 where they don't belong, followed by a blank string. In order to get this to work for columns with non-number strings in them, double any apostrophe's inside of the string. To see what happens, try this in query analyzer:
SELECT 'Jason''s dog can''t roll over'

So to make it work for you, surround your metx variable with DOUBLE apostrophes.

Jason Goemaat
sqlteam@goemaat.com

nr
SQLTeam MVY

12543 Posts

Posted - 2003-06-11 : 17:41:17

set rowcount has always worked the same as top.
At one time there was a mistake in bol saying that it was applied before the order by clause but that has been corrected now.

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

yuvalz
Starting Member

2 Posts

Posted - 2003-08-29 : 09:53:43

I am trying to use a string in a variable to let me use the TOP function and a variable value within a user-defined function (UDF). The problem there is that the UDF does not allow you to execute a string using EXECUTE...
Any ideas how this can be done?

yuvalz
Starting Member

2 Posts

Posted - 2003-08-29 : 09:56:32

I was attempting to use this technique to allow me to use the TOP function with a variable, letting me select as many rows as the variable specifies. The problem is that I am attempting to use a query-in-a-string inside a user defined function (UDF). UDFs apparently do not allow you to run the EXECUTE commaand. Any ideas about how I can make this work? Create a stored procedure for each statement?

robvolk
Most Valuable Yak

15732 Posts

Posted - 2003-08-30 : 10:41:56

You can't use TOP with a variable. Use SET ROWCOUNT instead. Books Online has the details.

cyberjessy
Starting Member

1 Post

Posted - 2005-06-14 : 03:58:32

You can do it Sql Server 2005, but not in earlier versions.

DECLARE @n int
SET @n = 100
select top (@n) * from [SomeTableName]

Jeswin P.
(jeswin #at# process64.com)

gusti
Starting Member

1 Post

Posted - 2006-11-26 : 20:51:19

What can i do if i'm using SQL in C# through "Microsoft dBase Driver (*.dbf)" to acces a dbf
the bd cas 400.000 records
how can i get it to retrive rows between 45,106 for example

"SELECT TOP @n * FROM db" won't help
in fact i want to get the same effect i get form using "Limit a,b" in a mysql statement

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2006-11-26 : 23:34:22

quote:
What can i do if i'm using SQL in C# through "Microsoft dBase Driver (*.dbf)" to acces a dbf

Which database software you are talking about?
This all discussion applies to SQL Server 2000 and 2005.

Harsh Athalye
India.
"Nothing is Impossible"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources