ALTER PROCEDURE P_GetConsultationInfo
(
@Employee varchar(50),
@Title varchar(60),
@ConsultationID INT
)AS
begin
DECLARE @SQL VARCHAR(1000),@SQLWHERE VARCHAR(300)
SET @SQL = @SQL + 'SELECT T_ConsultationInfo.Department, T_Consultation.Name,'
SET @SQL = @SQL + 'T_Consultation.Code, T_ConsultationInfo.RiskMemo,'
SET @SQL = @SQL + 'T_ConsultationInfo.ConsultationMemo, T_ConsultationInfo.Imperative,'
SET @SQL = @SQL + 'T_ConsultationInfo.ConsultationFocus, T_ConsultationInfo.ConsultationName,'
SET @SQL = @SQL + 'T_ConsultationInfo.ConsultationNumber, T_ConsultationInfo.EditDate,'
SET @SQL = @SQL + 'T_ConsultationInfo.SubmitDate, T_ConsultationInfo.EndEditer,'
SET @SQL = @SQL + 'T_ConsultationInfo.Auditingflg, T_ConsultationInfo.DepartmentID,'
SET @SQL = @SQL + 'T_ConsultationInfo.Title, T_ConsultationInfo.RiskKeep,'
SET @SQL = @SQL + 'T_ConsultationInfo.Secrecy, T_ConsultationInfo.Employee,'
SET @SQL = @SQL + 'T_ConsultationInfo.EmployeeID, T_ConsultationInfo.ID AS ID,'
SET @SQL = @SQL + 'T_ConsultationInfo.FlowID, T_WorkFlow.FlowID AS WorkFlowID'
SET @SQL = @SQL + ' FROM T_ConsultationInfo INNER JOIN '
SET @SQL = @SQL + ' T_Consultation ON '
SET @SQL = @SQL + ' T_ConsultationInfo.ConsultationID = T_Consultation.ID INNER JOIN '
SET @SQL = @SQL + ' T_WorkFlow ON T_ConsultationInfo.FlowID = T_WorkFlow.ID'
IF (@Employee <> '')
SET @SQLWHERE = ' T_ConsultationInfo.Employee = ' + @Employee
IF (@ConsultationID <> '' AND @SQLWHERE <> '')
SET @SQLWHERE = @SQLWHERE + ' AND T_ConsultationInfo.ConsultationID ='+@ConsultationID
ELSE
SET @SQLWHERE = @SQLWHERE + ' T_ConsultationInfo.ConsultationID ='+@ConsultationID
IF (@Title <> '' AND @SQLWHERE <> '' )
SET @SQLWHERE = @SQLWHERE + ' AND dbo.T_ConsultationInfo.Title LIKE %'+@Title+'%'
ELSE
SET @SQLWHERE = @SQLWHERE + ' dbo.T_ConsultationInfo.Title LIKE %'+@Title+'%'
IF (@SQLWHERE <> '')
SET @SQL = @SQL + ' WHERE ' +@SQLWHERE
EXEC (@SQL)
end
(
@Employee varchar(50),
@Title varchar(60),
@ConsultationID INT
)AS
begin
DECLARE @SQL VARCHAR(1000),@SQLWHERE VARCHAR(300)
SET @SQL = @SQL + 'SELECT T_ConsultationInfo.Department, T_Consultation.Name,'
SET @SQL = @SQL + 'T_Consultation.Code, T_ConsultationInfo.RiskMemo,'
SET @SQL = @SQL + 'T_ConsultationInfo.ConsultationMemo, T_ConsultationInfo.Imperative,'
SET @SQL = @SQL + 'T_ConsultationInfo.ConsultationFocus, T_ConsultationInfo.ConsultationName,'
SET @SQL = @SQL + 'T_ConsultationInfo.ConsultationNumber, T_ConsultationInfo.EditDate,'
SET @SQL = @SQL + 'T_ConsultationInfo.SubmitDate, T_ConsultationInfo.EndEditer,'
SET @SQL = @SQL + 'T_ConsultationInfo.Auditingflg, T_ConsultationInfo.DepartmentID,'
SET @SQL = @SQL + 'T_ConsultationInfo.Title, T_ConsultationInfo.RiskKeep,'
SET @SQL = @SQL + 'T_ConsultationInfo.Secrecy, T_ConsultationInfo.Employee,'
SET @SQL = @SQL + 'T_ConsultationInfo.EmployeeID, T_ConsultationInfo.ID AS ID,'
SET @SQL = @SQL + 'T_ConsultationInfo.FlowID, T_WorkFlow.FlowID AS WorkFlowID'
SET @SQL = @SQL + ' FROM T_ConsultationInfo INNER JOIN '
SET @SQL = @SQL + ' T_Consultation ON '
SET @SQL = @SQL + ' T_ConsultationInfo.ConsultationID = T_Consultation.ID INNER JOIN '
SET @SQL = @SQL + ' T_WorkFlow ON T_ConsultationInfo.FlowID = T_WorkFlow.ID'
IF (@Employee <> '')
SET @SQLWHERE = ' T_ConsultationInfo.Employee = ' + @Employee
IF (@ConsultationID <> '' AND @SQLWHERE <> '')
SET @SQLWHERE = @SQLWHERE + ' AND T_ConsultationInfo.ConsultationID ='+@ConsultationID
ELSE
SET @SQLWHERE = @SQLWHERE + ' T_ConsultationInfo.ConsultationID ='+@ConsultationID
IF (@Title <> '' AND @SQLWHERE <> '' )
SET @SQLWHERE = @SQLWHERE + ' AND dbo.T_ConsultationInfo.Title LIKE %'+@Title+'%'
ELSE
SET @SQLWHERE = @SQLWHERE + ' dbo.T_ConsultationInfo.Title LIKE %'+@Title+'%'
IF (@SQLWHERE <> '')
SET @SQL = @SQL + ' WHERE ' +@SQLWHERE
EXEC (@SQL)
end
EXEC (@SQL)
之前加一行print @SQL
查看打印出来的SQL语句.
(
@Employee varchar(50),
@Title varchar(60),
@ConsultationID INT
)AS
DECLARE @SQL VARCHAR(1000),@SQLWHERE VARCHAR(300)
SET @SQL = ''
set @SQLWHERE = ''
SET @SQL = @SQL + 'SELECT T_ConsultationInfo.Department, T_Consultation.Name,'
SET @SQL = @SQL + 'T_Consultation.Code, T_ConsultationInfo.RiskMemo,'
SET @SQL = @SQL + 'T_ConsultationInfo.ConsultationMemo, T_ConsultationInfo.Imperative,'
SET @SQL = @SQL + 'T_ConsultationInfo.ConsultationFocus, T_ConsultationInfo.ConsultationName,'
SET @SQL = @SQL + 'T_ConsultationInfo.ConsultationNumber, T_ConsultationInfo.EditDate,'
SET @SQL = @SQL + 'T_ConsultationInfo.SubmitDate, T_ConsultationInfo.EndEditer,'
SET @SQL = @SQL + 'T_ConsultationInfo.Auditingflg, T_ConsultationInfo.DepartmentID,'
SET @SQL = @SQL + 'T_ConsultationInfo.Title, T_ConsultationInfo.RiskKeep,'
SET @SQL = @SQL + 'T_ConsultationInfo.Secrecy, T_ConsultationInfo.Employee,'
SET @SQL = @SQL + 'T_ConsultationInfo.EmployeeID, T_ConsultationInfo.ID AS ID,'
SET @SQL = @SQL + 'T_ConsultationInfo.FlowID, T_WorkFlow.FlowID AS WorkFlowID'
SET @SQL = @SQL + ' FROM T_ConsultationInfo INNER JOIN '
SET @SQL = @SQL + ' T_Consultation ON '
SET @SQL = @SQL + ' T_ConsultationInfo.ConsultationID = T_Consultation.ID INNER JOIN '
SET @SQL = @SQL + ' T_WorkFlow ON T_ConsultationInfo.FlowID = T_WorkFlow.ID'
IF (@Employee <> '')
SET @SQLWHERE = ' T_ConsultationInfo.Employee = ' + @Employee
IF (@ConsultationID <> '' AND @SQLWHERE <> '')
SET @SQLWHERE = @SQLWHERE + ' AND T_ConsultationInfo.ConsultationID ='+ CONVERT(VARCHAR(10), @ConsultationID)
ELSE
SET @SQLWHERE = @SQLWHERE + ' T_ConsultationInfo.ConsultationID ='+ CONVERT(VARCHAR(10), @ConsultationID)
IF (@Title <> '' AND @SQLWHERE <> '' )
SET @SQLWHERE = @SQLWHERE + ' AND dbo.T_ConsultationInfo.Title LIKE ''%'+@Title+'%'''
ELSE
SET @SQLWHERE = @SQLWHERE + ' dbo.T_ConsultationInfo.Title LIKE ''%'+@Title+'%'''
IF (@SQLWHERE <> '')
SET @SQL = @SQL + ' WHERE ' +@SQLWHERE
PRINT @SQLEXEC (@SQL)
GO
SET ANSI_NULLS ON
GO
/*-------------------------------------------------------------------------
对象名称:P_GetConsultationInfo
描述: 获取问题咨询的所有信息
参数:@Employee 问题咨询发起人
@Title 问题标题
@风险类别
历史:
类型 作者 开始日期 结束日期 状态 版本
--------------------------------------------------------------------------
创建 梁远华 完成 1.00
修改
-------------------------------------------------------------------------*/
ALTER PROCEDURE P_GetConsultationInfo
(
@Employee varchar(50),
@Title varchar(60),
@ConsultationID INT
)AS
DECLARE @SQL VARCHAR(1000),@SQLWHERE VARCHAR(300)
SET @SQL = 'SELECT T_ConsultationInfo.Department, T_Consultation.Name,'
--这里你不应该set @SQL = @SQL + 的,因为@SQL初始值为空 最终还是为空的
SET @SQL = @SQL + 'T_Consultation.Code, T_ConsultationInfo.RiskMemo,'
SET @SQL = @SQL + 'T_ConsultationInfo.ConsultationMemo, T_ConsultationInfo.Imperative,'
SET @SQL = @SQL + 'T_ConsultationInfo.ConsultationFocus, T_ConsultationInfo.ConsultationName,'
SET @SQL = @SQL + 'T_ConsultationInfo.ConsultationNumber, T_ConsultationInfo.EditDate,'
SET @SQL = @SQL + 'T_ConsultationInfo.SubmitDate, T_ConsultationInfo.EndEditer,'
SET @SQL = @SQL + 'T_ConsultationInfo.Auditingflg, T_ConsultationInfo.DepartmentID,'
SET @SQL = @SQL + 'T_ConsultationInfo.Title, T_ConsultationInfo.RiskKeep,'
SET @SQL = @SQL + 'T_ConsultationInfo.Secrecy, T_ConsultationInfo.Employee,'
SET @SQL = @SQL + 'T_ConsultationInfo.EmployeeID, T_ConsultationInfo.ID AS ID,'
SET @SQL = @SQL + 'T_ConsultationInfo.FlowID, T_WorkFlow.FlowID AS WorkFlowID'
SET @SQL = @SQL + ' FROM T_ConsultationInfo INNER JOIN '
SET @SQL = @SQL + ' T_Consultation ON '
SET @SQL = @SQL + ' T_ConsultationInfo.ConsultationID = T_Consultation.ID INNER JOIN '
SET @SQL = @SQL + ' T_WorkFlow ON T_ConsultationInfo.FlowID = T_WorkFlow.ID'
IF (@Employee <> '')
SET @SQLWHERE = ' T_ConsultationInfo.Employee = ' + @Employee
IF (@ConsultationID <> '' AND @SQLWHERE <> '')
SET @SQLWHERE = @SQLWHERE + ' AND T_ConsultationInfo.ConsultationID ='+@ConsultationID
ELSE
SET @SQLWHERE = @SQLWHERE + ' T_ConsultationInfo.ConsultationID ='+@ConsultationID
IF (@Title <> '' AND @SQLWHERE <> '' )
SET @SQLWHERE = @SQLWHERE + ' AND dbo.T_ConsultationInfo.Title LIKE %'+@Title+'%'
ELSE
SET @SQLWHERE = @SQLWHERE + ' dbo.T_ConsultationInfo.Title LIKE %'+@Title+'%'
IF (@SQLWHERE <> '')
SET @SQL = @SQL + ' WHERE ' +@SQLWHERE
EXEC (@SQL)