CREATE PROCEDURE [dbo].[PUB_CORP_SEARCH]
@oi_return INT OUTPUT , -- 操作返回值
@ov_message NVARCHAR(1000) OUTPUT , -- 操作返回信息
@iv_user VARCHAR(30) , -- 操作用户
@iv_bankcode VARCHAR(30) , -- 银行代码
@iv_orgcode VARCHAR(20) , -- 机构代码
@id_workdate DATETIME , -- 操作日期
@iv_corptype VARCHAR(30) , -- 获取帐号
@iv_corpcode VARCHAR(30), -- 获取帐户类型
@iv_corpcname VARCHAR(30), -- 获取帐户拥有者中文名
@iv_corpidno VARCHAR(30), -- 获取帐户拥有者身份号
@iv_userregfrom VARCHAR(30),
@iv_userregto VARCHAR(30)
WITH EXECUTE AS OWNER
AS
BEGIN
DECLARE @v_moduleid VARCHAR(50)
DECLARE @v_expression NVARCHAR(4000)
SET @v_moduleid = 'PUB_CORP_SEARCH'
SET @oi_return = 0
SET @ov_message = ''
BEGIN TRY
SET @v_expression = 'SELECT BANK_CODE,
ORG_CODE,
CORP_CODE,
NAME,
NAME_EN,
CASE CORP_TYPE
WHEN ''001'' THEN ''全民所有制''
WHEN ''002'' THEN ''集体''
WHEN ''003'' THEN ''个体''
WHEN ''004'' THEN ''三资''
WHEN ''005'' THEN ''股份制''
WHEN ''006'' THEN ''有限责任''
WHEN ''007'' THEN ''个人独资''
WHEN ''008'' THEN ''合伙企业''
WHEN ''009'' THEN ''其他''
END ''CORP_TYPE'',
LICENSE_TYPE,
LICENSE_NO,
ADDRESS,
POSTCODE,
PHONE1,
PHONE2,
MANAGER,
[GROUP],
LOAN_CERTIFICATE,
CORP_ID,
CORP_REG_CODE,
CORP_REG_CAPITAL,
CORP_REG_DATE,
CORP_REG_ADDRESS,
CORP_REG_YEAR,
CORP_PERSON_NAME,
CORP_PERSON_ID,
CORP_PERSON_CODE,
MEMO,
FLG_ACTIVE,
REG_ORGCODE,
REG_USER,
REG_DATE,
LASTUPT_ORGCODE,
LASTUPT_USER,
LASTUPT_DATE,
I1,
I2,
I3,
I4,
I5,
S1,
S2,
S3,
S4,
S5,
S6,
S7,
S8,
S9,
S10,
S11,
S12,
S13,
S14,
S15,
S16,
S17,
S18,
S19,
S20,
T1,
T2,
T3,
T4,
T5,
D1,
D2,
D3,
D4,
D5,
N1,
N2,
N3,
N4,
N5,
N6,
N7,
N8,
N9,
N10,
CREATE_USER,
CREATE_DATETIME,
UPDATE_USER,
UPDATE_DATETIME,
UPDATE_COUNT
FROM TB_CORP
WHERE '
-- 过滤条件为空表示匹配所有记录
IF @iv_corptype = ''
SET @iv_corptype = '%'
ELSE
SET @iv_corptype = '%'+@iv_corptype+'%'
IF @iv_corpcode = ''
SET @iv_corpcode = '%'
ELSE
SET @iv_corpcode = '%'+@iv_corpcode+'%'
IF @iv_corpcname = ''
SET @iv_corpcname = '%'
ELSE
SET @iv_corpcname = '%'+@iv_corpcname+'%'
IF @iv_corpidno = ''
SET @iv_corpidno = '%'
ELSE
SET @iv_corpidno = '%'+@iv_corpidno+'%'
IF @iv_userregfrom ='' AND @iv_userregto=''
SET @v_expression = @v_expression + 'CORP_TYPE LIKE '''+ @iv_corptype +''' AND CORP_CODE LIKE ''' + @iv_corpcode + ''' AND NAME LIKE '''+ @iv_corpcname +''' AND LICENSE_NO LIKE '''+ @iv_corpidno+ ''' AND ORG_CODE= '''+@iv_orgcode+ ''' AND BANK_CODE= '''+@iv_bankcode+''''
ELSE IF @iv_userregfrom ='' AND @iv_userregto!=''
SET @v_expression = @v_expression + 'CORP_TYPE LIKE '''+ @iv_corptype +''' AND CORP_CODE LIKE ''' + @iv_corpcode + ''' AND NAME LIKE '''+ @iv_corpcname +''' AND LICENSE_NO LIKE '''+ @iv_corpidno+ ''' AND REG_DATE <= '''+ @iv_userregto+ ''' AND ORG_CODE= '''+@iv_orgcode+ ''' AND BANK_CODE= '''+@iv_bankcode+''''
ELSE IF @iv_userregfrom !='' AND @iv_userregto=''
SET @v_expression = @v_expression + 'CORP_TYPE LIKE '''+ @iv_corptype +''' AND CORP_CODE LIKE ''' + @iv_corpcode + ''' AND NAME LIKE '''+ @iv_corpcname +''' AND LICENSE_NO LIKE '''+ @iv_corpidno+ ''' AND REG_DATE >= '''+ @iv_userregfrom+ ''' AND ORG_CODE= '''+@iv_orgcode+ ''' AND BANK_CODE= '''+@iv_bankcode+''''
ELSE
SET @v_expression = @v_expression + 'CORP_TYPE LIKE '''+ @iv_corptype +''' AND CORP_CODE LIKE ''' + @iv_corpcode + ''' AND NAME LIKE '''+ @iv_corpcname +''' AND LICENSE_NO LIKE '''+ @iv_corpidno+ ''' AND REG_DATE <= '''+ @iv_userregto+ ''' AND REG_DATE >= '''+ @iv_userregfrom +''' AND ORG_CODE= '''+@iv_orgcode+ ''' AND BANK_CODE=''' +@iv_bankcode+''''
EXEC sp_executesql @v_expression
END TRY
BEGIN CATCH
SET @oi_return = ABS(ERROR_NUMBER()) * (-1)
SELECT @ov_message = CONVERT(VARCHAR(20),@oi_return) + ' : ' + ERROR_MESSAGE()
EXEC [dbo].[SYS01_LOG_ERROR]
@iv_user ,
@v_moduleid ,
@ov_message
RETURN
END CATCH
END
在存储过程中连接SQL语句字符串
最新推荐文章于 2021-06-12 23:26:21 发布