Hi,
I have the following recordset
RsLatestCmm.Open "SELECT Top " & intHomepageCmm & " CID,CmmName,CmmStatus,date_created FROM Cmm_List where CmmStatus=1 ORDER BY date_created desc",conn,0,1

I have converted it into an sp like this
CREATE PROCEDURE [cmmSP_latestCmm] AS
SELECT Top @intHomepageCmm CID,CmmName,CmmStatus,date_created FROM Cmm_List where CmmStatus=1 ORDER BY date_created desc
GO

but I get error for intHomepageCmm
I have considered it as parameter
what should I do?
this intHomepageCmm gets the value from the database table

I think what your saying is that the value in your stored procedure, @intHomepageCmm, is based upon the value in another database table so what you need to do is this:

CREATE PROCEDURE cmmSP_latestCMM AS
DECLARE @intHomePageCmm as int
SET @intHomePageCmm = SELECT [value] From [table] Where [where clause]

SELECT TOP @intHomePageCmm CID, CmmName, CmmStatus, Date_created from Cmm_List where CmmStatus = 1 ORDER BY date_created desc

hth


"The one language all programmers understand is profanity."

Thanks a lot for your reply

I get error this error when creating it in ms sql
Incorrect syntax near the keyword 'SELECT'
this is my sp:
CREATE PROCEDURE [cmmSP_latestCmm] AS
DECLARE @intHomePageCmm as int
SET @intHomePageCmm = SELECT HomepageCmm From cmm_setting
SELECT Top @intHomePageCmm CID,CmmName,CmmStatus,date_created FROM Cmm_List where CmmStatus=1 ORDER BY date_created desc

GO

are you using MS SQL or MySQL?

"The one language all programmers understand is profanity."

ms sql

I probably should point this out:

In MS SQL 2000 the TOP keyword MUST be used as follows:
    SELECT TOP 1 * from [table]

In MS SQL 2005 the TOP keyword can be used as follows:
    SELECT TOP 1 * from [table]
    or
    Declare @nTop as int
    Set @nTop = 1
    SELECT Top (@nTop) * from [table]

In plain english, in SQL 2000 Top must be followed by a literal in 2005 it can be followed by a literal or a variable and, as such, my above code will only work on SQL 2005.


"The one language all programmers understand is profanity."