执行存储过程时,Sql Server中出现错误214 [英] Error Occur 214 in Sql Server when Execute the Store Procedure
问题描述
ALTER PROCEDURE [dbo]。[sp_Select_Party]
@QueryName as varchar(50),
@ac_code as varchar(10),
@ac_name as varchar(50),
@contact_person varchar(50)
AS
SET NOCOUNT ON;
BEGIN
开始尝试
DECLARE @SelectClause nvarchar (4000)
DECLARE @WhereClause nvarchar(2000)
if(@QueryName ='PartyList')
BEGIN
SET @SelectClause ='SELECT ac_code,ac_name,City,Contact_Person,Phone,Mobile,Email FROM tblPartyMaster'
SET @ WhereClause ='WHERE Isactive = 1'
SET @ SelectClause = @SelectClause + @ WhereClause
if(@ac_name<>'')
begin
SET @WhereClause = @WhereClause +'AND ac_name LIKE @ac_name +''%'''
end
if(@ac_code<>'')
开始
SET @WhereClause = @WhereClause +'AND ac_code LIKE @ac_code +''%'''
end
if(@contact_person<> '')
开始
SET @WhereClause = @WhereClause +'AND Contact_Person LIKE @contact_person +''%'''
end
结束
--if(@QueryName ='PartyList_Partial')
ELSE
BEGIN
SET @SelectClause ='SELECT distinct pm.ac_code,pm.ac_name,pm.City,pm.Contact_Person,pm.Phone,pm.Mobile,pm.Email来自tblPartyMaster PM,tblSmplColnParent SCP其中pm.ac_code = scp。 ac_code'
SET @ WhereClause ='AND pm.Isactive = True'
SET @SelectClause = @SelectClause + @ WhereClause
if(@ac_name<>'')
begin
SET @WhereClause = @WhereClause +'AND ac_name LIKE @ac_name +''%' ''
结束
if(@ac_code<>'')
begin
SET @WhereClause = @WhereClause +'AND ac_code LIKE @ac_code +''%'''
end
if(@contact_person<>'')
开始
SET @WhereClause = @WhereClause +'AND Contact_Person LIKE @contact_person +''%'''
end
结束
声明@Para nvarchar(4000)
设置@Para = N'@ QueryName varchar(50),@ ac_name varchar (50),@ ac_code varchar(10),@ contact_person varchar(50)'
SET @SelectClause = @SelectClause + @WhereClause
set @SelectClause = @SelectClause + 'ORDER BY ac_name'
print @SelectClause
EXEC sp_executesql @SelectClause,@ QueryName,@ ac_name,@ ac_code,@ contact_person
END TRY
BEGIN CATCH
SELECT
ERROR_NUMBER()AS ErrorNumber
,ERROR_MESSAGE()AS ErrorMessage
,ERROR_LINE()AS ERRORLINE;
END CATCH
END
exec sp_Select_Party
@ QueryName ='PartyList_Partial',
@ ac_name ='',
@ ac_code ='',
@contact_person =''
ALTER PROCEDURE [dbo].[sp_Select_Party]
@QueryName as varchar(50),
@ac_code as varchar (10),
@ac_name as varchar(50),
@contact_person varchar (50)
AS
SET NOCOUNT ON;
BEGIN
BEGIN TRY
DECLARE @SelectClause nvarchar(4000)
DECLARE @WhereClause nvarchar(2000)
if (@QueryName = 'PartyList')
BEGIN
SET @SelectClause='SELECT ac_code,ac_name,City,Contact_Person,Phone,Mobile,Email FROM tblPartyMaster '
SET @WhereClause= 'WHERE Isactive=1 '
SET @SelectClause=@SelectClause+@WhereClause
if(@ac_name <> '')
begin
SET @WhereClause = @WhereClause +' AND ac_name LIKE @ac_name + ''%'''
end
if(@ac_code <> '')
begin
SET @WhereClause = @WhereClause +' AND ac_code LIKE @ac_code + ''%'''
end
if(@contact_person <> '')
begin
SET @WhereClause = @WhereClause +' AND Contact_Person LIKE @contact_person + ''%'''
end
END
--if(@QueryName = 'PartyList_Partial')
ELSE
BEGIN
SET @SelectClause='SELECT distinct pm.ac_code, pm.ac_name, pm.City, pm.Contact_Person, pm.Phone, pm.Mobile, pm.Email From tblPartyMaster PM, tblSmplColnParent SCP where pm.ac_code=scp.ac_code '
SET @WhereClause= 'AND pm.Isactive=True '
SET @SelectClause=@SelectClause+@WhereClause
if(@ac_name <> '')
begin
SET @WhereClause = @WhereClause +' AND ac_name LIKE @ac_name + ''%'''
end
if(@ac_code <> '')
begin
SET @WhereClause = @WhereClause +' AND ac_code LIKE @ac_code + ''%'''
end
if(@contact_person <> '')
begin
SET @WhereClause = @WhereClause +' AND Contact_Person LIKE @contact_person + ''%'''
end
END
Declare @Para nvarchar(4000)
set @Para = N'@QueryName varchar(50),@ac_name varchar(50),@ac_code varchar(10),@contact_person varchar(50)'
SET @SelectClause = @SelectClause + @WhereClause
set @SelectClause = @SelectClause + ' ORDER BY ac_name'
print @SelectClause
EXEC sp_executesql @SelectClause,@QueryName,@ac_name,@ac_code,@contact_person
END TRY
BEGIN CATCH
SELECT
ERROR_NUMBER() AS ErrorNumber
,ERROR_MESSAGE() AS ErrorMessage
,ERROR_LINE() AS ERRORLINE;
END CATCH
END
exec sp_Select_Party
@QueryName='PartyList_Partial',
@ac_name='',
@ac_code='' ,
@contact_person=''
推荐答案
试试这个
Try this
SET @WhereClause= 'WHERE pm.Isactive=True '
问候,
Dinesh Kumar.V。
Regards,
Dinesh Kumar.V.
这篇关于执行存储过程时,Sql Server中出现错误214的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!