本人写了个Procedure
Create PROCEDURE [dbo].[sp_ConvertedAcctVerificaton]
-- =============================================
-- Author : David Dai
-- Create date : 06.06.2008
-- Description : Get the Missed Acct based on the PP
-- =============================================
-- Add the parameters for the stored procedure here
@pstrRepType VARCHAR(100) = 'IMPACS',
@pintReturn INT = 0 OUTPUT
AS
DECLARE @@strSQL VARCHAR(2000)
BEGIN
--check table name
SET NOCOUNT ON;
IF (@pstrRepType = '')
BEGIN
--NO report type FOUND AND RETURN NULL
SET @pintReturn = 2
RETURN
END
ELSE
BEGIN
--declare @sql nvarchar(4000)
SET @@strSQL =
CASE @pstrRepType
WHEN 'IMPACS' THEN
'Select ACC_NO,CTL_ACCT_NO,CUST_NO from tbl_int_relation AS A
where not EXISTS ( SELECT A.ACC_NO,A.CTL_ACCT_NO,A.CUST_NO from tbl_int_impacs AS B
where A.ACC_NO=B.ACC_NO AND A.CTL_ACCT_NO=B.CTL_ACCT_NO and A.CUST_NO=B.CUST_NO) AND APPL_CODE=''IM'' '
WHEN 'LOAN'
'Select ACC_NO,CTL_ACCT_NO,CUST_NO from tbl_int_relation AS A
where not EXISTS (SELECT A.ACC_NO,A.CTL_ACCT_NO,A.CUST_NO from tbl_int_impacs AS B
where A.ACC_NO=B.ACC_NO AND A.CTL_ACCT_NO=B.CTL_ACCT_NO and A.CUST_NO=B.CUST_NO) AND APPL_CODE=''AM'' '
WHEN 'RC+'
'Select ACC_NO,CTL_ACCT_NO,CUST_NO from tbl_int_relation AS A
where not EXISTS (SELECT A.ACC_NO,A.CTL_ACCT_NO,A.CUST_NO from tbl_int_impacs AS B
where A.ACC_NO=B.ACC_NO AND A.CTL_ACCT_NO=B.CTL_ACCT_NO and A.CUST_NO=B.CUST_NO) AND APPL_CODE=''MD'' '
WHEN 'INVEST'
'Select ACC_NO,CTL_ACCT_NO,CUST_NO from tbl_int_relation AS A
where not EXISTS (SELECT A.ACC_NO,A.CTL_ACCT_NO,A.CUST_NO from tbl_int_impacs AS B
where A.ACC_NO=B.ACC_NO AND A.CTL_ACCT_NO=B.CTL_ACCT_NO and A.CUST_NO=B.CUST_NO) AND (APPL_CODE=''MF'' OR APPL_CODE=''TR'')'
END
--execute sp_executesql @sql
END
IF (@@strSQL <> '' )
BEGIN
EXEC(@@strSQL )
SET @pintReturn = 0
RETURN
END
ELSE
BEGIN
-- NO SQL FORMED
SET @pintReturn = 1
RETURN
END
END
但是编译的时候总是报错,各位能帮我看看吗?
Create PROCEDURE [dbo].[sp_ConvertedAcctVerificaton]
-- =============================================
-- Author : David Dai
-- Create date : 06.06.2008
-- Description : Get the Missed Acct based on the PP
-- =============================================
-- Add the parameters for the stored procedure here
@pstrRepType VARCHAR(100) = 'IMPACS',
@pintReturn INT = 0 OUTPUT
AS
DECLARE @@strSQL VARCHAR(2000)
BEGIN
--check table name
SET NOCOUNT ON;
IF (@pstrRepType = '')
BEGIN
--NO report type FOUND AND RETURN NULL
SET @pintReturn = 2
RETURN
END
ELSE
BEGIN
--declare @sql nvarchar(4000)
SET @@strSQL =
CASE @pstrRepType
WHEN 'IMPACS' THEN
'Select ACC_NO,CTL_ACCT_NO,CUST_NO from tbl_int_relation AS A
where not EXISTS ( SELECT A.ACC_NO,A.CTL_ACCT_NO,A.CUST_NO from tbl_int_impacs AS B
where A.ACC_NO=B.ACC_NO AND A.CTL_ACCT_NO=B.CTL_ACCT_NO and A.CUST_NO=B.CUST_NO) AND APPL_CODE=''IM'' '
WHEN 'LOAN'
'Select ACC_NO,CTL_ACCT_NO,CUST_NO from tbl_int_relation AS A
where not EXISTS (SELECT A.ACC_NO,A.CTL_ACCT_NO,A.CUST_NO from tbl_int_impacs AS B
where A.ACC_NO=B.ACC_NO AND A.CTL_ACCT_NO=B.CTL_ACCT_NO and A.CUST_NO=B.CUST_NO) AND APPL_CODE=''AM'' '
WHEN 'RC+'
'Select ACC_NO,CTL_ACCT_NO,CUST_NO from tbl_int_relation AS A
where not EXISTS (SELECT A.ACC_NO,A.CTL_ACCT_NO,A.CUST_NO from tbl_int_impacs AS B
where A.ACC_NO=B.ACC_NO AND A.CTL_ACCT_NO=B.CTL_ACCT_NO and A.CUST_NO=B.CUST_NO) AND APPL_CODE=''MD'' '
WHEN 'INVEST'
'Select ACC_NO,CTL_ACCT_NO,CUST_NO from tbl_int_relation AS A
where not EXISTS (SELECT A.ACC_NO,A.CTL_ACCT_NO,A.CUST_NO from tbl_int_impacs AS B
where A.ACC_NO=B.ACC_NO AND A.CTL_ACCT_NO=B.CTL_ACCT_NO and A.CUST_NO=B.CUST_NO) AND (APPL_CODE=''MF'' OR APPL_CODE=''TR'')'
END
--execute sp_executesql @sql
END
IF (@@strSQL <> '' )
BEGIN
EXEC(@@strSQL )
SET @pintReturn = 0
RETURN
END
ELSE
BEGIN
-- NO SQL FORMED
SET @pintReturn = 1
RETURN
END
END
但是编译的时候总是报错,各位能帮我看看吗?
CASE @pstrRepType
WHEN 'IMPACS' THEN
'Select ACC_NO,CTL_ACCT_NO,CUST_NO from tbl_int_relation AS A
where not EXISTS ( SELECT A.ACC_NO,A.CTL_ACCT_NO,A.CUST_NO from tbl_int_impacs AS B
where A.ACC_NO=B.ACC_NO AND A.CTL_ACCT_NO=B.CTL_ACCT_NO and A.CUST_NO=B.CUST_NO) AND APPL_CODE=''IM'' '
WHEN 'LOAN' THEN
'Select ACC_NO,CTL_ACCT_NO,CUST_NO from tbl_int_relation AS A
where not EXISTS (SELECT A.ACC_NO,A.CTL_ACCT_NO,A.CUST_NO from tbl_int_impacs AS B
where A.ACC_NO=B.ACC_NO AND A.CTL_ACCT_NO=B.CTL_ACCT_NO and A.CUST_NO=B.CUST_NO) AND APPL_CODE=''AM'' '
WHEN 'RC+' THEN
'Select ACC_NO,CTL_ACCT_NO,CUST_NO from tbl_int_relation AS A
where not EXISTS (SELECT A.ACC_NO,A.CTL_ACCT_NO,A.CUST_NO from tbl_int_impacs AS B
where A.ACC_NO=B.ACC_NO AND A.CTL_ACCT_NO=B.CTL_ACCT_NO and A.CUST_NO=B.CUST_NO) AND APPL_CODE=''MD'' '
WHEN 'INVEST' THEN
'Select ACC_NO,CTL_ACCT_NO,CUST_NO from tbl_int_relation AS A
where not EXISTS (SELECT A.ACC_NO,A.CTL_ACCT_NO,A.CUST_NO from tbl_int_impacs AS B
where A.ACC_NO=B.ACC_NO AND A.CTL_ACCT_NO=B.CTL_ACCT_NO and A.CUST_NO=B.CUST_NO) AND (APPL_CODE=''MF'' OR APPL_CODE=''TR'')'
END
(1)要么没缩近,所以可能造成一些符号不成对,比如 begin end
(2)要么构建出的动态语句有问题。自己print出来看看。
-- =============================================
-- Author : David Dai
-- Create date : 06.06.2008
-- Description : Get the Missed Acct based on the PP
-- =============================================
-- Add the parameters for the stored procedure here
@pstrRepType VARCHAR(100) = 'IMPACS',
@pintReturn INT = 0 OUTPUT
AS
DECLARE @@strSQL VARCHAR(2000)
BEGIN
--check table name
SET NOCOUNT ON;
IF (@pstrRepType = '')
BEGIN
--NO report type FOUND AND RETURN NULL
SET @pintReturn = 2
RETURN
END
ELSE
BEGIN
--declare @sql nvarchar(4000)
SET @@strSQL =
CASE @pstrRepType
WHEN 'IMPACS' THEN
'Select ACC_NO,CTL_ACCT_NO,CUST_NO from tbl_int_relation AS A
where not EXISTS ( SELECT A.ACC_NO,A.CTL_ACCT_NO,A.CUST_NO from tbl_int_impacs AS B
where A.ACC_NO=B.ACC_NO AND A.CTL_ACCT_NO=B.CTL_ACCT_NO and A.CUST_NO=B.CUST_NO) AND APPL_CODE=''IM'' '
WHEN 'LOAN' THen
'Select ACC_NO,CTL_ACCT_NO,CUST_NO from tbl_int_relation AS A
where not EXISTS (SELECT A.ACC_NO,A.CTL_ACCT_NO,A.CUST_NO from tbl_int_impacs AS B
where A.ACC_NO=B.ACC_NO AND A.CTL_ACCT_NO=B.CTL_ACCT_NO and A.CUST_NO=B.CUST_NO) AND APPL_CODE=''AM'' '
WHEN 'RC+' THEN
'Select ACC_NO,CTL_ACCT_NO,CUST_NO from tbl_int_relation AS A
where not EXISTS (SELECT A.ACC_NO,A.CTL_ACCT_NO,A.CUST_NO from tbl_int_impacs AS B
where A.ACC_NO=B.ACC_NO AND A.CTL_ACCT_NO=B.CTL_ACCT_NO and A.CUST_NO=B.CUST_NO) AND APPL_CODE=''MD'' '
WHEN 'INVEST' THEN
'Select ACC_NO,CTL_ACCT_NO,CUST_NO from tbl_int_relation AS A
where not EXISTS (SELECT A.ACC_NO,A.CTL_ACCT_NO,A.CUST_NO from tbl_int_impacs AS B
where A.ACC_NO=B.ACC_NO AND A.CTL_ACCT_NO=B.CTL_ACCT_NO and A.CUST_NO=B.CUST_NO) AND (APPL_CODE=''MF'' OR APPL_CODE=''TR'')'
END
--execute sp_executesql @sql
END
IF (@@strSQL <> '' )
BEGIN
EXEC(@@strSQL )
SET @pintReturn = 0
RETURN
END
ELSE
BEGIN
-- NO SQL FORMED
SET @pintReturn = 1
RETURN
END
END