请教有关于动态SQL语句的问题！！！

flers 2006-10-18 04:38:56
SET QUOTED_IDENTIFIER OFF
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 = @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)

execute P_GetConsultationInfo '','',''
当我调用这个SP时，没有列出现，只有“命令已成功完成”提示，是否那里出错了，我查询不到任何数据，数据库中存在了相关的数据。
...全文
265 4 打赏 收藏 转发到动态 举报
写回复
用AI写文章
4 条回复
切换为时间正序
请发表友善的回复…
发表回复
xiaoku 2006-10-18
  • 打赏
  • 举报
 回复
我知道了...

SET QUOTED_IDENTIFIER OFF
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)
conichiwa 2006-10-18
  • 打赏
  • 举报
 回复
ALTER PROCEDURE P_GetConsultationInfo
(
@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 @SQL

EXEC (@SQL)
hellowork 2006-10-18
  • 打赏
  • 举报
 回复
在
EXEC (@SQL)
之前加一行print @SQL
查看打印出来的SQL语句.
allright_flash 2006-10-18
  • 打赏
  • 举报
 回复
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
一个查询的SQL语句请教,希望能够用一条SQL语句得到结果
一个查询的SQL语句请教,希望能够用一条SQL语句得到结果
一条sql语句完成MySQL去重留一
查了资料，请教了大佬之后得出了一个很便利的sql语句，这里分享下这段sql语句和思路。 需求分析 数据库中存在重复记录，删除保留其中一条（是否重复判断基准为多个字段） 解决方案 碰到这个需求的时候，心里大概是有...
oracle修改的sql语句,请教oracle高手 SQL语句该怎么修改，如何写。
当前位置:我的异常网» Oracle开发»请教oracle高手 SQL语句该怎么修改，如何写。请教oracle高手 SQL语句该怎么修改，如何写。www.myexceptions.net网友分享于：2013-02-04浏览：18次请问oracle高手 SQL语句该如何...
oracle sql长度限制吗_请教，ORACLE中SQL语句最大长度是多少
通常在procedure中sql拼接的时候会有这个问题，一62616964757a686964616fe78988e69d8331333335336536边解决方案是设置function对逻辑分别处理来实现减少sql长度的。VAR_DYNAMIC_SQL:='INSERTINTORP_TRANS_LOG_MINUTE...
写一条sql语句的过程
今天碰到了一个需求，要求写一份报表，但是难在了sql语句上面。本人sql水平，仅限于select * from 这种简单的sql语句，看到老长的sql语句头就疼。下面分享我写这条语句的过程。（当然少不了百度，和向公司的学长请教...
疑难问题

22,210

社区成员

121,726

社区内容

发帖
与我相关
我的任务
社区描述
MS-SQL Server 疑难问题
社区管理员
  • 疑难问题社区
  • 尘觉
加入社区
  • 近7日
  • 近30日
  • 至今

加载中

查看更多榜单
社区公告
暂无公告

试试用AI创作助手写篇文章吧

+ 用AI写文章