SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 Site Related Forums
 Article Discussion
 Article: Dynamic SQL or How do I SELECT TOP @var records?
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

AskSQLTeam
Ask SQLTeam Question

USA
0 Posts

Posted - 07/31/2000 :  10:55:47  Show Profile  Visit AskSQLTeam's Homepage  Reply with Quote
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.

Anonymous
Starting Member

0 Posts

Posted - 12/27/2000 :  12:36:18  Show Profile  Reply with Quote
Storing the result of an exec()

When I use this approach, the result of an exec() is being printed to the screen. How would I store it in a variable?

Thanks, E

Go to Top of Page

Peter Dutch
Posting Yak Master

Netherlands
127 Posts

Posted - 03/29/2002 :  09:24:42  Show Profile  Reply with Quote
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...

Go to Top of Page

robvolk
Most Valuable Yak

USA
15678 Posts

Posted - 03/29/2002 :  11:09:30  Show Profile  Visit robvolk's Homepage  Reply with Quote
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.

Go to Top of Page

fishlens
Starting Member

USA
1 Posts

Posted - 09/24/2002 :  23:03:55  Show Profile  Visit fishlens's Homepage  Reply with 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)

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




Go to Top of Page

jgoemat
Starting Member

2 Posts

Posted - 06/11/2003 :  17:13:38  Show Profile  Reply with Quote
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
Go to Top of Page

nr
SQLTeam MVY

United Kingdom
12543 Posts

Posted - 06/11/2003 :  17:41:17  Show Profile  Visit nr's Homepage  Reply with Quote
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.
Go to Top of Page

yuvalz
Starting Member

USA
2 Posts

Posted - 08/29/2003 :  09:53:43  Show Profile  Reply with Quote
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?
Go to Top of Page

yuvalz
Starting Member

USA
2 Posts

Posted - 08/29/2003 :  09:56:32  Show Profile  Reply with Quote
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?
Go to Top of Page

robvolk
Most Valuable Yak

USA
15678 Posts

Posted - 08/30/2003 :  10:41:56  Show Profile  Visit robvolk's Homepage  Reply with Quote
You can't use TOP with a variable. Use SET ROWCOUNT instead. Books Online has the details.
Go to Top of Page

cyberjessy
Starting Member

1 Posts

Posted - 06/14/2005 :  03:58:32  Show Profile  Reply with Quote
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)
Go to Top of Page

gusti
Starting Member

1 Posts

Posted - 11/26/2006 :  20:51:19  Show Profile  Reply with Quote
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
Go to Top of Page

harsh_athalye
Flowing Fount of Yak Knowledge

India
5581 Posts

Posted - 11/26/2006 :  23:34:22  Show Profile  Visit harsh_athalye's Homepage  Click to see harsh_athalye's MSN Messenger address  Send harsh_athalye a Yahoo! Message  Reply with Quote
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"
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.11 seconds. Powered By: Snitz Forums 2000