Solution for "n" in "SELECT top N *" statement cannot be a variable

Some time ago, want to write a return to a specified number of records procedure, the code is as follows:

CREATE PROCEDURE Getsomearticles

@cnt int = 1

As

SELECT Top @cnt * from articles

Results found unable to run, always have problems, baffled, and then asked the teacher to know, SELECT top n Here the parameter n can only be a constant, cannot be a variable.

If you define an SQL statement directly in your program and then execute it, you can achieve the desired result:

int cnt = 10;
String strSQL = "SELECT top" + CNT. ToString () + "* from articles";

I wrote it myself. A class of operations databases that apply factory design patterns
A method of encapsulating n multi-operation database
and realize the database independence
That is, you can use the same code to manipulate access, MS SQL SERVER, MySQL
In theory can also achieve the operation of Oracle, but did not use it, so temporarily did not realize, hehe
Commdb db = Createdb.create ();
Performs a database operation, returning a dataset
DataSet ds = db. Execsql (strSQL);
But I do have the need to use stored procedures to achieve, this can do. Fortunately, there is a dynamic execution of SQL (I do not know whether this statement is accurate, for the moment), so we can write the stored procedure:

CREATE PROCEDURE Getsomearticles

@cnt int = 1

As

EXEC ("select top" + @cnt + "* FROM articles")

This solves the problem that n can only be a constant in select top N ^_^

In addition, to achieve similar functionality in MySQL, you can write this:

DELIMITER $$

CREATE PROCEDURE ' Getsomearticles ' (in CNT int)
BEGIN
Set @stmt = concat (' SELECT * from articles LIMIT 0,? ');
Prepare S1 from @stmt;
Set @s1 = CNT;
Execute S1 using @s1;
deallocate prepare S1;
end$$

DELIMITER;