正在运行过程，但没有存储过程 [英] Running procedure but without as stored procedure

问题描述

我在数据库中有以下存储过程，该存储过程用于进行gridview的分页.它将记录插入临时表中，设置行数并仅返回特定的行.

Hi,

I have following stored procedure in database which is used for paging purpose for a gridview. it inserts records in a temporary table, sets rowcount and just returns specific rows.

procedure [dbo].[GetResults]
@pIndex varchar(50),
@startRowIndex int,
@maximumRows int 
AS
DECLARE @TempItems TABLE
(
ID int IDENTITY,
GMKey int,
Details nChar(800)
)
DECLARE @maxRow int

SET CONCAT_NULL_YIELDS_NULL OFF
SET ANSI_WARNINGS OFF

SET @maxRow = (@startRowIndex + @maximumRows) - 1 
SET ROWCOUNT @maxRow

INSERT INTO @TempItems (GMKey,TABLENAME,Details)
Select PRIMARYINDEX as GMKey,  LTRIM(RTRIM(cast(Facility_Name as nChar(800)))) as Details 
from SEARCHINFO where Facility_Name LIKE @pIndex + ''%'' order by PRIMARYINDEX DESC

SET ROWCOUNT @maximumRows
SELECT GMKey, TABLENAME, Details FROM @TempItems WHERE ID >= @startRowIndex

RETURN 

我们的要求是不要在数据库中有存储过程，此功能必须通过asp.net代码实现.我不确定如何使用普通的命令对象来实现这一点.如您所注意到的，有一些语句，例如SET ROWCOUNT，创建临时表等.我可以使用存储过程的脚本并作为命令文本提供给命令对象并执行它吗?还是有其他更好的选择.

谢谢

Vijay

Our requirement is not to have stored procedure in the database, this functionality has to be implemented through asp.net code. I am not sure how to achieve this using a normal command object. As you can note there are statements as SET ROWCOUNT, create temporary table etc. Can I use the script of the stored procedure and give as commandtext to command object and execute it? or is there any other better option.

Thanks

Vijay

推荐答案

Stored Procedure只是预编译的SQL脚本，它存储为命名对象，并且可以通过名称调用.无论在存储过程中编写什么SQL，都可以作为SQL脚本执行(鉴于脚本中提供了参数值)

因此，您当然可以在应用程序中构建适当的SQL脚本(使用参数值)，并通过命令对象作为命令文本(而不作为Stored Procedure)执行.但是，您可能需要调整特定于Ado.net的应用程序代码，以便它可以从结果集中获取值，而不用读取输出参数(如果有的话).

顺便说一句，使用SCOPE_IDENTITY()是获取表的新插入ID的更好选择.您可以Google 它来学习如何使用它.

The Stored Procedure is nothing but a pre-complied SQL script which is stored as a named object and can be invoked by the name. Whatever SQL is written in the stored procedure, can be executed as an SQL srcipt (Given the fact that, parameter values are provided in the script)

So, you can of course build the appropirate SQL script in your application (With the parameter values) and execute as a command text (Not as a Stored Procedure) via the command object. But, you might need to adjust your Ado.net specific application codes so that, it can obtain value from the result set, instead of reading the output parameters if there is any.

By the way, use of SCOPE_IDENTITY() is a better option for getting the newly inserted ID of the table. You can Google it to learn how to use it.

我的基本要求是对表进行搜索，例如其中"facility_name"，例如< param/>％"，并且如果查询返回了100条记录，但我的gridview应该显示第二页，即应该从返回的100条记录的数据集中显示6-10.不回避上面的脚本，而是给出一个简单的sql查询，该查询仅返回6至10行(即仅5行).上面的脚本可以做到这一点.

My basic requirement is perform a search on a table e.g. "where facility_name like ''<param />%'' and if the query returns 100 records but my gridview is supposed to show second page i.e. it is supposed to show 6 - 10 from the returned dataset of 100 records. Can we not avoid the script above and instead just give a simple sql query which would return only 6 to 10 rows (i.e. only 5 rows). The script above does that.

这篇关于正在运行过程，但没有存储过程的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！