ASP calls the MSSQL stored procedure with parameters and returns the temporary table recordset

A few days ago, when developing a system platform, the Arisisi Web Team (alixixi.com) needed to call several fields of multiple tables through a stored procedure, merge them into a temporary table, and sort them back to an ASP call.

Below, I share the experience:

1, stored procedure part:

The following are part of the stored procedure: CREATE PROCEDURE [dbo]. [Alixixi] @sNum int, @sOrder varchar (20) As SET Nocount ON CREATE TABLE #btable (ID int,channelid int,classid int,title varchar (), ImageUrl varchar (), htmlfiledate varchar ( ), Addtime datetime) Execute (' INSERT into #btable select top ' + @sNum + ' id,fid,cid,title,imageurl,myurl,writetime from ali_tb1 where Isbest=1 ' ) Execute (' INSERT into #btable select top ' + @sNum + ' id,fid,cid,topic,photourl,siteurl,addtime from ali_tb2 where Isbest=1 ' ) Execute (' SELECT * from #btable ORDER BY ' + @sOrder + ' desc ') Go

The function of this stored procedure is to establish a stored procedure named Alixixi, with two parameters snum and Sorder, which can be flexibly controlled to display the number of records returned and the sorting method.

In the MSSQL Query Analyzer, we can execute the stored procedure in this form to return two table each 5 records (altogether 10):

EXEC Alixixi 5, ' Addtime '

Below, we talk about the methods called in ASP (conn for database connection, please check your environment, the relevant database connection code is not listed here).

The following is a reference fragment: Dim cmd Set Rs =server.createobject ("ADODB.") Recordset ") Set CMD = Server.CreateObject ("Adodb.command") Set Cmd.activeconnection=conn cmd.commandtext= "Alixixi" Cmd.Parameters.append cmd.createparameter ("@sNum", 3,1,4,50) Cmd.Parameters.append cmd.createparameter ("@sOrder", 200,1,20, "Addtime") Cmd.commandtype=4 Set Rs=cmd.execute Set CMD = Nothing If not rs.eof Then Do as not rs.eof Response.Write Rs (0) & "<br>" Rs.movenext Loop Else Response.Write "No Record" End If Set rs=nothing

By using the above method, we can implement ASP to invoke the MSSQL stored procedure with parameter, and return the temporary table recordset.