我写了个存储过程，不知道为什么会用到exec sp_prepexecrpc这个

下面这是我在SQL Server Profiler中检测到的
declare @p1 int
set @p1=NULL
declare @p3 int
set @p3=NULL
exec sp_prepexecrpc @p1 output,N'get_gq_search',@p3 output,15,3,'U.vip_class desc,U.last_update_time desc
','
','
','
','螺纹钢
','                                                                                                    ','
','                                                  ','                                                  ',0
select @p1, @p3
存储过程如下：
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go-- =============================================
-- Author: King
-- Create date: 2009-05-04
-- Description: 根据查询条件获取新闻
-- =============================================
ALTER PROCEDURE [dbo].[get_gq_search]
-- Add the parameters for the stored procedure here
@rowscount int =0 output,
@PageSize int = 10, --每页记录数
@PageIndex int = 1, --当前页码
@OrderField varchar(5000) = NULL, --排序字段(必须!支持多字段)
@City nvarchar(100) = NULL, --交货地
@cd nvarchar(100)=NULL,----产地
@blockname nvarchar(50)=NULL,
@keyword varchar(5000)=NULL,
@Material nvarchar(50) = NULL,
@Length nvarchar(50)=NULL,
@Width nvarchar(50)=NULL,
@Height nvarchar(50)=NULL,
@blockid int =0
--WITH ENCRYPTION
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;

    -- Insert statements for procedure here

if ( @PageSize <=0 )
set @PageSize = 1
if ( @PageIndex <=0 )
set @PageIndex = 1
    --生成SQL语句
    DECLARE @SQL varchar(8000)
    DECLARE @SQL_1 varchar(8000)
    DECLARE @WhereSQL varchar(8000)
DECLARE @strblockname varchar(8000)
    DECLARE @Num int
SET @strblockname=''
SET @WhereSQL = ''
    SET @Num = @PageSize * (@PageIndex - 1)     if (@City<>'' and @City IS NOT NULL)----交货地
     set @WhereSQL = @WhereSQL + ' and jhd like ''%'+ltrim(rtrim(@City))+'%'''
    if (@cd<>'' and @cd is not null)----产地
set @WhereSQL = @WhereSQL + ' and cd like ''%'+ltrim(rtrim(@cd))+'%'''
if (@Material<>'' and @Material is not null)----材质
set @WhereSQL = @WhereSQL + ' and cz like ''%'+ltrim(rtrim(@Material))+'%'''
if (@Length<>'' and @Length is not null)----长度
set @WhereSQL = @WhereSQL + ' and length = '''+ltrim(rtrim(@Length))+''''
if (@Width<>'' and @Width is not null)----宽度
set @WhereSQL = @WhereSQL + ' and width = '''+ltrim(rtrim(@Width))+''''
if (@Height<>'' and @Height is not null)----厚度
set @WhereSQL = @WhereSQL + ' and height = '''+ltrim(rtrim(@Height))+''''
    if (@blockname <> '' and (@blockname is not null))----搜索关键字
     set @strblockname = ' and title like ''%'+ltrim(rtrim(@blockname))+'%'''
if (@keyword <> '' and (@keyword is not null))----搜索关键字
     set @strblockname = ' and title like ''%'+ltrim(rtrim(@keyword))+'%'''
if ( @blockid <> 0 )
set @WhereSQL = @WhereSQL + ' and blockid = '+ convert(varchar(50),@blockid)
--获取记录总数
DECLARE @SQLcount nvarchar(4000)
--set @SQL ='select top '+cast(@PageSize as varchar(80))+' News.id,News.userid,News.title,U.vip_class,U.last_update_time,News.blockid,News.count,B_s.spec_ico,News.cd,News.ck,News.cz,News.jhd,News.length,News.width,News.height,News.price,News.simage,News.bimage,News.createdate,U.company_name,U.userinfo,U.operation,U.web_url,U.logo,P.product_list from dv_user U join info_list News on U.userid=News.userid join (select distinct(dbo.f_union(i.userid)) as product_list,i.userid from info_list i where info_type=1 and supply_type=0 '+ @strblockname +' group by userid) P on P.userid=U.userid join block_spec B_s on B_s.id=News.blockid where News.id in(select max(id) id from info_list where info_type=1 and supply_type=0 and userid<>0 '+ @strblockname +' group by userid) '
set @SQL ='select top '+cast(@PageSize as varchar(80))+' News.id,News.userid,News.title,U.vip_class,U.last_update_time,u.block_info_text as product_list,News.blockid,News.count,B_s.spec_ico,News.cd,News.ck,News.cz,News.jhd,News.length,News.width,News.height,News.price,News.simage,News.bimage,News.createdate,U.company_name,U.userinfo,U.operation,U.web_url,U.logo from dv_user U join info_list News on U.userid=News.userid join block_spec B_s on B_s.id=News.blockid where News.id in (select max(id) id from info_list where info_type=1 and supply_type=0 and userid<>0 '+ @strblockname +' group by userid) '

    set @SQL_1 = 'select TOP '+cast(@Num as varchar(80))+' News.id from dv_user U join info_list News on U.userid=News.userid join block_spec B_s on B_s.id=News.blockid where News.id in (select max(id) id from info_list where info_type=1 and supply_type=0 and userid<>0 '+ @strblockname +' group by userid) '

set @SQLcount = N'select @rowscount=count(News.id) from dv_user U join info_list News on U.userid=News.userid join block_spec B_s on B_s.id=News.blockid where News.id in (select max(id) id from info_list where info_type=1 and supply_type=0 and userid<>0 '+ @strblockname +' group by userid) '
set @SQLcount = @SQLcount + @WhereSQL
--print @SQLcount
exec sp_executesql @SQLcount,N'@rowscount int OUTPUT',@rowscount OUTPUT
--根据页码和每页记录数合成sql语句 set @SQL = @SQL + @WhereSQL
if 1 < @PageIndex
BEGIN
set @SQL = @SQL + ' and News.id NOT IN (' + @SQL_1 + @WhereSQL + ' order by ' + @OrderField +')'
END
set @SQL = @SQL + ' order by ' + @OrderField
    exec (@SQL)     --return (0)
END

解决方案 »

免费领取超大流量手机卡，每月29元包185G流量+100分钟通话, 中国电信官方发货

http://blog.csdn.net/fredrickhu/archive/2009/09/21/4574840.aspx
看看动态SQL基本语法就知道了
重新生成執行plan，應該是第一次運行的時候出現的吧
--条件均可以改写成类似于：
if (isnull(@cd,'')<>'')----产地
it calls sp_cursorprepexec to prepare the stored procedure.http://technet.microsoft.com/zh-cn/library/ms130779.aspx
http://social.msdn.microsoft.com/forums/en-US/sqldataaccess/thread/05c6b8a1-d20e-4641-8642-0980dcf65612/
我只要把Prepared设置为false就不会有问题了，应该是预编译的问题！但我其它的的存储过程Prepared设置为true也不会出现这样的问题，不知道是什么原因了。