查询sql server 2005中的prepare语句 [英] query for prepare statement in sql server 2005

问题描述

我对sql server2005非常新。我会详细解释我的问题。我的数据库表包含被阻止用户和未阻止用户的列表。我想取所有未阻止的列表用户。我在存储过程中已经完成但有一个条件是，当用户点击UI中的page1时，只显示前10个成员，当他点击第2页时，应该出现下一个10。但是在写完存储过程后，它显示一些错误.....



消息4145，等级15，状态1，程序List_UnblockedConsumers，行23

表达式在预期条件的上下文中指定的非布尔类型，在'stmt'附近。

消息102，级别15，状态1，过程List_UnblockedConsumers，第25行

不正确'PREPARE'附近的语法。



我在下面复制我的代码。请给我必要的说明。

谢谢你的所有回复。 />

Hi,
Iam very new to sql server2005.I will explain my problem in detail.My database table contains a list of blocked users and unblocked users.I want to take the list of all unblocked users.That I have done in stored procedures but there is a condition that when the user clicks on page1 in the UI only the first 10 members should appear and when he clicks on page2 next 10 should appear.But after writing the stored procedure,it is showing some errors.....

Msg 4145, Level 15, State 1, Procedure List_UnblockedConsumers, Line 23
An expression of non-boolean type specified in a context where a condition is expected, near 'stmt'.
Msg 102, Level 15, State 1, Procedure List_UnblockedConsumers, Line 25
Incorrect syntax near 'PREPARE'.

Iam copying my code below.Please give me necessary instructions.
Thankyou for all the replies.

CREATE PROCEDURE List_UnblockedConsumers 
	-- Add the parameters for the stored procedure here
@_isAll binary="0",
@_pageNumber varchar="0",
@_numberOfItems varchar="0",
@Querystring varchar="0"
AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;

    -- Insert statements for procedure here
	SET @Querystring=(SELECT * FROM dbo.tbl_consumer WHERE BLOCKED=0)
    IF(_isAll=false) 
    SET @Querystring=(@Querystring + ' LIMIT ' + _pageNumber + ', ' + _numberOfItems)
	END IF
    PREPARE stmt FROM @Querystring;
	EXECUTE stmt;
	DEALLOCATE PREPARE stmt; 
END

推荐答案

<$ c $的对应物MS SQL上的c> PREPARE 将是 EXECUTE 或 spexecutesql 。请参阅以下两个样本。



在存储过程中构建动态SQL [ ^ ]

The counterpart of PREPARE on MS SQL would be EXECUTE or spexecutesql. Refer to the following for a sample of the two.

Building Dynamic SQL In a Stored Procedure[^]

这篇关于查询sql server 2005中的prepare语句的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！