better stored procedure to retrieve records from three table?

Question

0.00/5 (No votes)

See more:

SQL-Server-2005

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

SQL

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..

Posted 29-Oct-12 20:59pm

ythisbug

Updated 29-Oct-12 21:22pm

v2

Add a Solution

Comments

Zoltán Zörgő 30-Oct-12 3:01am

First of all, please tell us, what is the problem you are experiencing? What do you want do you want exactly this sp to do?

ythisbug 30-Oct-12 3:12am

this sp will retrieve records from related to ID..while saving records in tblQuestionnaire adding tblDocumentMasters dmID and in QuestionnaireSub adding qsTPRID as tblQuestionnaires quesTPRID..so am not retrieving records while executing..

3 solutions

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

MT_ · Answer 1 · 2012-10-29T21:16:00

Solution 2

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

SQL

tblQuestionnaire.quesID=tblQuestionnaireSub.qsTPRID

left side looks doubtful considering you have tblQuestionnaire.quesTPRID column. Shouldnt it be

SQL

tblQuestionnaire.quesTPRID =tblQuestionnaireSub.qsTPRID

Hope that helps.
Milind

Posted 29-Oct-12 21:16pm

MT_

Updated 29-Oct-12 21:25pm

v2

Comments

ythisbug 30-Oct-12 3:23am

thanks for ur reply ..i modified question

MT_ 30-Oct-12 3:26am

seems you got wrong join it should be on quesTPRID !

ythisbug 30-Oct-12 5:00am

yes your rite thank you

MT_ 30-Oct-12 5:03am

Cool. So that solved ur problem? If yes, mark the answer as solution, upvote -Milind

ythisbug 30-Oct-12 5:05am

if i use like tat query while selecting rowcommand am gettin error there is no row at the position 0..

ythisbug 30-Oct-12 5:05am

and enumeration yielded no results...

BK 4 code · Answer 2 · 2012-10-29T21:58:00

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.

SQL

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

kankeyan · Answer 3 · 2012-10-29T21:14:00

Solution 1

Check the tables whether you are having correct values

Posted 29-Oct-12 21:14pm

kankeyan

Comments

ythisbug 30-Oct-12 3:23am

while executing am giving value as 66 tat id is related in all three tables..

MT_ 30-Oct-12 3:24am

This is at the best a comment and NOT solution.

better stored procedure to retrieve records from three table?

3 solutions

Solution 2

Solution 3

Solution 1

Add your solution here

Preview 0

Existing Members

...or Join us