当前位置: 代码迷 >> Sql Server >> EXECUTE sp_executesql 出错,该如何解决
  详细解决方案

EXECUTE sp_executesql 出错,该如何解决

热度:151   发布时间:2016-04-27 17:25:07.0
EXECUTE sp_executesql 出错
if   exists   (select   *   from   dbo.sysobjects   where   id   =   object_id(N '[dbo].[sp_DLookup] ')   and   OBJECTPROPERTY(id,   N 'IsProcedure ')   =   1)
drop   procedure   [dbo].[sp_DLookup]
GO

SET   ANSI_NULLS   ON
GO
SET   QUOTED_IDENTIFIER   ON
GO

CREATE   PROCEDURE   [dbo].[sp_DLookup]
@psField   varchar(100),
@psTable   varchar(100),
@psCriteria   varchar(100)
AS
declare   @strSql   varchar(1000)

BEGIN
        set   @strSql   =   N 'Select   top   1   '[email protected]+N '   as   Result   From   '[email protected]
        If   (@psCriteria   !=   ' ')  
              set   @strSql   =   @strSql   +   N '   Where   '[email protected]


EXECUTE   sp_executesql   @strSql,   N '@psField   varchar(100)   output ',@psField   output


END
GO

错误提示:Msg   214,   Level   16,   State   2,   Procedure   sp_executesql,   Line   1
Procedure   expects   parameter   '@statement '   of   type   'ntext/nchar/nvarchar '.


------解决方案--------------------
if exists (select * from dbo.sysobjects where id = object_id(N '[dbo].[sp_DLookup] ') and OBJECTPROPERTY(id, N 'IsProcedure ') = 1)
drop procedure [dbo].[sp_DLookup]
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE PROCEDURE [dbo].[sp_DLookup]
@psField varchar(100),
@psTable varchar(100),
@psCriteria varchar(100)
AS
declare @strSql nvarchar(1000) --数据类型要修改为unicode类型

BEGIN
set @strSql = N 'Select top 1 '[email protected]+N ' as Result From '[email protected]
If (@psCriteria != ' ')
set @strSql = @strSql + N ' Where '[email protected]


EXECUTE sp_executesql @strSql, N '@psField varchar(100) output ',@psField output


END
GO
  相关解决方案