Converting SQL syntax to MySQL syntax

 Hi, the following is a stored procedure written for SQL,  would this work in MySQL or do I have to change the syntax? If so would you please point me to the site that will show me how to convert it to one that is equivalent to the MySQL syntax?  Thank you in advance for your help.

CREATE PROCEDURE sp_PagedItems
    (
     @Page int,
     @RecsPerPage int
    )
AS

-- We don't want to return the # of rows inserted
-- into our temporary table, so turn NOCOUNT ON
SET NOCOUNT ON


--Create a temporary table
CREATE TABLE #TempItems
(
    ID int IDENTITY,
    Name varchar(50),
    Price currency
)


-- Insert the rows from tblItems into the temp. table
INSERT INTO #TempItems (Name, Price)
SELECT Name,Price FROM tblItem ORDER BY Price

-- Find out the first and last record we want
DECLARE @FirstRec int, @LastRec int
SELECT @FirstRec = (@Page - 1) * @RecsPerPage
SELECT @LastRec = (@Page * @RecsPerPage + 1)

-- Now, return the set of paged records, plus, an indiciation of we
-- have more records or not!
SELECT *,
       MoreRecords =
    (
     SELECT COUNT(*)
     FROM #TempItems TI
     WHERE TI.ID >= @LastRec
    )
FROM #TempItems
WHERE ID > @FirstRec AND ID < @LastRec


-- Turn NOCOUNT back OFF
SET NOCOUNT OFF

0
ProgMaster
8/1/2007 9:12:22 AM
asp.net.mysql 1929 articles. 0 followers. Follow

1 Replies
588 Views

Similar Articles

[PageSpeed] 8

not familiar with the #tablename identifier.. you may use `tablename` as an alternate of it to mysql...

as I've read your stored proc. I only see one unknown in it, just the # ...

for a guide, you can go to this Manual.. actually MySQL 5.0 Reference Manual.

http://dev.mysql.com/doc/refman/5.0/en/

 

Cheers,

CLIPER


------------------------------------------------
Believe nothing, no matter where you read it, or who said it, no matter if I have said it, unless it agrees with your own reason and your own commonsense. ---Buddha (563BC-483BC)
0
CLIPER
8/1/2007 1:05:31 PM
Reply:

Similar Artilces:

Err: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax
I get this error message in cmd.ExecuteNonQuery() "You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '//' at line 1" when I run the code below. The concept is to read some values from a databse and write the result into another databse if the condition is satisfied. Dim conString As StringDim myconn As MySqlConnection Dim sql As String conString = "Server=localhost; Database=xyz; Uid=root"myconn = New MySqlConnection(conString)   sql = "Select site_id, timestring...

SQL Syntax in DBD::mysql
------=_NextPart_000_000D_01C263B6.FA136D10 Content-Type: text/plain; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable I am trying to use the following code to insert data into a mysql = database:- # write the information to the database $sth=3D$dbh->prepare("INSERT INTO = images(name,desc,category,subcat1,subcat2,image) VALUES(?,?,?,?,?,?)"); $sth->bind_param(1,$name); $sth->bind_param(2,$desc); $sth->bind_param(3,$category); $sth->bind_param(4,$subcat1); $sth->bind_param(5,$subcat2); $sth->bind_param(6,$photo); ...

My sql syntax for MySQL got problem?
I am trying out dataset. My database is Mysql. I can't seem to preview my data and also a textbox for my to key in something in my parameter. Is there something wrong my sql below? SELECT clinic.* FROM clinic WHERE (state = '@state')My HomePage - www.findingsteve.net"It is more blessed to give than to receive." Acts 20: 35 (NIV, Bible)Progress - "Never discourage anyone...who continually makes progress, no matter how slow." Plato I don't know anything about MySQL, but as far as SQL goes, you would need to remove the single quotes from your WHERE clause as so: SE...

MYSQL syntax
I have the following statement to extract data from the last 5 years of a table: SELECT `invoice`.`Inv_Num`, `invoice`.`Cust_Num`, `invoice`.`Inv_Date`, `invoice`.`Vat_Rate`, `inv_list`.`Line_ID`, `inv_list`.`Quantity`, `inv_list`.`Sell_price`, `inv_list`.`Line_Total`, `inv_list`.`Description` FROM `invoice` Inner Join `inv_list` ON `invoice`.`Inv_Num` = `inv_list`.`Inv_Num` WHERE `inv_list`.`Description` LIKE '%parking charge%' AND `invoice`.`Inv_Num` > '670' I need to extract each months data and sum the line total. Is there a simple way to do this? ...

Converting from PHP/MySQL to .NET/SQL Server
Hello W are looking to convert several applications to .NET using SQL Server. I see where the migration assistant can be of great help with most of the code. I was wondering what would be the best approach to covert the SQL database syntax from MYSQL to SQL Server Thanks in advance for your help! Dave It depends on how you would like to do it. There are a few ways to go about it: A) Convert all applicable SQL to stored procedures. B) Convert most, but not all (some might be difficult) to stored procedures and leave the rest as they are. C) Leave them all. If you p...

You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version
hi,while i executing a query i dot the error message like this "You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version"can anyone help me   Can you give any other detail of your configuration? It seems the database path is not properly described.---Pasaban---MCTS...

How to convert this VB syntax into PB syntax ?
Hi all, this is the VB syntax (Lotus SameTime VB example) : Private MyCommunityService As CommunityService Set MyCommunityService = New CommunityService MyCommunityService.LoginByPassword Host, UserName, Password ---------------------------------------------------------------------------- -- and this is my PB9 conversion: long ll_status OLEObject loo_Community loo_Community = CREATE OLEObject ll_status = loo_Community.ConnectToNewObject("STComTk.CommunityService") // OK, ll_status =0 ll_status = loo_Community.LoginByPassword("Host"...

MySQL
Currently have a site coded in PHP but the customer wants to go to .NET. Can I use the existing MySQL DB or am I required to use MSSQL? Thanks, Ken Check out this forum which is specialized for this kind of questions: ASP.NET MySQL forumBart De Smet [MVP] Visit www.msdn.be, www.bartdesmet.net...

mysql insert syntax
I'm trying to copy some columns from oldtable to newtable using dbi dbd:mysql. My site is hosted by a third party who has not responded to my inquiries, so I don't know their setup exactly but its perl 5.6x on UNIX with Apache server. Earlier I was using the quote method for my insert statement but getting an error message that DBI:st was not installed. Since I can't do anything about that I rewrote the code as below but I cannot prepare. $@ is: Uncaught exception from user code: DBI prepare: invalid number of parameters: handle + 5 Usage: $h->prepare($statement...

Converting from MySQL to SQL
hi.. may i know how to convert from mysql to microsoft sql server ?? is it very troublesome ?? coz its quite a big project.. i need to convert frm mysql to sql.. is there any example or reference? thanks You can use DTS to import the data from MySQL to SQL Server. As mentioned, so long as you have an OLE DB driver for MySQL then it's very simple - you can use the DTS (Data Transformation Services) wizard in Enterprise Manager (the GUI front-end to SQL Server) to Import you schema (tables, data etc.) It will do all the hard work of mapping between datatypes etc. Se...

mysql syntax problem
Hi all when i query a mysql db within a perl script this works fine... $sth = $dbh->prepare ("SELECT venue from base1 WHERE op = 'K Trevan'"); this doesn't.. $sth = $dbh->prepare ("SELECT venue from base1 WHERE op = 'K O'Trevan'"); i know the issue is 'K O'Trevan' in that the 2nd ' completes i just don't know how to resolve it - other than changing 'K O'Trevan' to 'K O Trevan' Any ideas appreciated Steve On Wed, Oct 02, 2002 at 09:06:16AM +0100, Steven_Massey...

help with mySQL syntax
hello.  I have an sql command that pulls the ID number (which is an auto-incrimenting field) from the row a user selects to delete and binds it to a variable i can use.  I need to setup an SQL statement that then takes all the ID rows greater than that deleted row ID's number, and subtracts 1 from them, updating them with the new value. I think i may be on the right path, i'm not sure. if someone could help me with this as i've never really setup complex SQL statements before. 'gets the id value of the field being deleted from a dropdownlist selection Dim strC...

MySQL query syntax
What's the correct MySQL code for the following T-SQL query: select full_name =first_name+' '+last_name from contacts I run the above in MySQL and get an error back. Thanks.Please mark the post(s) that have helped you as "Answer" Select concat(first_name,'',last_name) as full_name from contacts...

VWD 2008 Express Error 42000 MySQL ODBC 5.1 Driver mysqld-5.0.22 error in SQL Syntax
This is driving me a bit mad now trying to get this fixed. Have downloaded and install the mysql connector 5.2.0, have managed to connect fine to the database using ODBC, Can connect ok to the database in Access 2008 and see data.In Visual Web Developer Express 2008 have managed to connect to the database ok, can see all the tables there. However when adding the SqlDataSource and configure, will connect ok, However in the configure the Select Statement it doesn't retreive any of the columns in the specify columns from a table or view. If I do a specify a customer SQL Statement ...

Web resources about - Converting SQL syntax to MySQL syntax - asp.net.mysql

PastBook’s Filepicker.io Integration Eases Process Of Converting Facebook Content To Books
PastBook , one of several companies that allow Facebook users to publish their content on the social network in actual books , announced the ...

Facebook No Longer Converting Groups Into Pages
Back when Facebook first launched Facebook Pages, many businesses and brands who had built up substantial audiences in their Facebook Groups ...

Zwartz Laminating-Converting B.V. on the App Store on iTunes
Get Zwartz Laminating-Converting B.V. on the App Store. See screenshots and ratings, and read customer reviews.


"Occupier" Thanked Former Soviet Citizen for "Converting" Him to Capitalism, Pro-Israel, Pro-USA - YouTube ...
May Day Demonstration on Union Square in New York City Zionism & Birth of Modern Israel in 1948: Former Soviet Citizen Pays Tribute to Ben-Gurion ...

Click go fears of converting print files
Is there a way to convert a print queue item to a .RTF or .PDF file? I like to save or email them. - The Sydney Morning Herald

Sudanese woman ordered to hang under sharia law for converting to Christianity gives birth
Khartoum, Sudan: A Christian Sudanese woman sentenced to hang for apostasy has given birth in jail, a Western diplomat said on Tuesday.

Imams warn against radicalism to Aboriginal inmates converting to Islam
The prison system has enlisted the help of ASIO to crack down on radicalisation behind bars amid revelations that Aboriginals are converting ...

Converting the world's companies one by one - The Science Show - ABC Radio National (Australian Broadcasting ...
Image: Trucks carrying logs make their way up a road in Jambi, Indonesia. A vast area of the Sumatran forest, and orangutan habitat, is being ...

Converting Churches Into Homes Is The Latest Hollywood Trend
You don't have to be a believer to be moved by the beauty of a church.

Resources last updated: 1/2/2016 9:26:44 AM