更好的存储过程,以从三个表中检索记录? [英] better stored procedure to retrieve records from three table?
问题描述
他在我的项目中使用了动态功能,因此我创建了sp以从三个表中获取值,但是在执行sp时却无法获取值
tblQuestionnaireSub列
qsID
qsTPRID
qsQuestion
qsAnswer
qsMinValue
qsMaxValue
tblQuestionnaire列
quesID
quesTPRID
quesDelStatus
tblDocumnetMasterMaster列
dmID
dmRefNo
dmSubject
来自tblDocumentMaster的dmID被添加到quesTPRID中,而quesTPRID是被添加到qsTPRID中的值
所以我想检索与该ID相关的值
这是我的sp
ALTER 过程 [dbo].[usp_GetQuestionnaireByID]
@ ID varchar ( 50 )
AS BEGIN SET 打开;
选择 * 来自(tblQuestionnaire 左 加入 tblQuestionnaireSub
tblQuestionnaire.quesID = tblQuestionnaireSub.qsTPRID)
左 加入 tblDocumentMaster 在上tblQuestionnaire.quesTPRID = tblDocumentMaster.dmID
其中 quesID = @ ID and tblQuestionnaire.quesDelStatus = ' 0'
结束
所以任何人都可以建议我此查询中的问题是什么吗?
谢谢..
没有提供足够的信息来回答这个问题.
因为您说的是没有返回值,所以我相信您表中的数据与查询中提供的子句匹配.但是如果没有表的详细信息,仅通过查看查询,没人能猜出原因.但是,下面加入
tblQuestionnaire.quesID = tblQuestionnaireSub.qsTPRID
考虑到您有tblQuestionnaire.quesTPRID列,左侧看起来令人怀疑.应该不应该
tblQuestionnaire.quesTPRID = tblQuestionnaireSub.qsTPRID
希望有帮助.
Milind
首先,您必须设置@ID的数据类型等于quesID的类型.
第二,您必须在方括号中写完整的select语句.
据我所知,我对您的sp进行了如下修改.
ALTER 过程 [dbo].[usp_GetQuestionnaireByID] @ ID int AS BEGIN SET 打开; 选择 * 来自(选择 tblQuestionnaireSub.qsTPRID ,tblQuestionnaireSub.quesDelStatus 来自 tblQuestionnaire 左 加入 tblQuestionnaireSub 上 tblQuestionnaire.quesID = tblQuestionnaireSub.qsTPRID)T 左 加入 tblDocumentMaster 在上T.quesTPRID = tblDocumentMaster.dmID 其中 quesID = @ ID and T.quesDelStatus = ' 0' 结束
检查表是否具有正确的值
hi am using dynamic in my project so i created sp to get values from three tables but am not able to get values while executing sp
tblQuestionnaireSub columns
qsID
qsTPRID
qsQuestion
qsAnswer
qsMinValue
qsMaxValue
tblQuestionnaire columns
quesID
quesTPRID
quesDelStatus
tblDocumnetMaster columns
dmID
dmRefNo
dmSubject
from tblDocumentMaster dmID is adding in quesTPRID and quesTPRID is value is adding in qsTPRID
so i want to retrieve values related to that id
here is my sp
ALTER PROCEDURE [dbo].[usp_GetQuestionnaireByID] @ID varchar(50) AS BEGIN SET NOCOUNT ON; SELECT * from (tblQuestionnaire left join tblQuestionnaireSub on tblQuestionnaire.quesID=tblQuestionnaireSub.qsTPRID) left join tblDocumentMaster on tblQuestionnaire.quesTPRID=tblDocumentMaster.dmID where quesID=@ID and tblQuestionnaire.quesDelStatus='0' end
so can any one suggest me what is the issue in this query??
thank you..解决方案There is not much information provided to answer the question to the point.
Since, you are saying, no values returned, I believe you have data in the tables which match the clauses supplied in the query. But without details of tables, just by looking at the query nobody can guess the cause. However, the below join
tblQuestionnaire.quesID=tblQuestionnaireSub.qsTPRID
left side looks doubtful considering you have tblQuestionnaire.quesTPRID column. Shouldnt it be
tblQuestionnaire.quesTPRID =tblQuestionnaireSub.qsTPRID
Hope that helps.
Milind
First you should have to set datatype of @ID is equal to type of quesID.
second you should have to write full select statement in brackets.
as per my knowledge i have modified your sp as follows.
ALTER PROCEDURE [dbo].[usp_GetQuestionnaireByID] @ID int AS BEGIN SET NOCOUNT ON; SELECT * from (select tblQuestionnaireSub.qsTPRID,tblQuestionnaireSub.quesDelStatus from tblQuestionnaire left join tblQuestionnaireSub on tblQuestionnaire.quesID=tblQuestionnaireSub.qsTPRID)T left join tblDocumentMaster on T.quesTPRID=tblDocumentMaster.dmID where quesID=@ID and T.quesDelStatus='0' end
Check the tables whether you are having correct values
这篇关于更好的存储过程,以从三个表中检索记录?的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!