ASP.NET - Sp - Asked By Neethu on 14-Feb-12 12:18 AM

hi all,

i have 2 tables.

1.  bstint_testlog table

testcaseid  projectid  moduleid     actualresult        expectedresult            status
1.1.1   
16        27         vmnbvklvkhkj 
 bfvhdkvhshvjklfhb       Fail NULL
1.1.2 16        27         vbcvbvb  vbhvchnbn      Pass v01


2. bstint_reportissue table

issueid    testcaseid   severity    priority   empid      status     description
2          1.1.1       Low      High      22      New           rsgfdhgfhgfjnjgss



I have projectid and moduleid . By using that i have to get the testcaseid from table 1 and using that testcaseid i have to get the issueid and description in the 2nd table.


my stored procedure is

ALTER PROCEDURE [dbo].[bstint_issuedetls_bytestid]

@projectid int,
@moduleid int

AS
BEGIN

SELECT bstint_reportissue.issueid,
bstint_reportissue.description

FROM bstint_reportissue



WHERE  bstint_reportissue.testcaseid = (SELECT bstint_testlog.testcaseid
FROM bstint_testlog
WHERE  bstint_testlog.projectid = @projectid and bstint_testlog.moduleid = @moduleid)

END



But its not working. can anyone pls correct the SP
Web Star replied to Neethu on 14-Feb-12 12:24 AM
When you to change you condition , use IN instead of = because subquery return more than one value so that work with IN clause not with = sign

ALTER PROCEDURE [dbo].[bstint_issuedetls_bytestid]

@projectid int,
@moduleid int

AS
BEGIN

SELECT bstint_reportissue.issueid,
bstint_reportissue.description

FROM bstint_reportissue



WHERE  bstint_reportissue.testcaseid  IN  (SELECT bstint_testlog.testcaseid
FROM bstint_testlog
WHERE  bstint_testlog.projectid = @projectid and bstint_testlog.moduleid = @moduleid)

END 
Sandeep Mittal replied to Neethu on 14-Feb-12 12:25 AM
Instead of subquery use join.
Also you are using "equal to" with sub query this would give you the error if sub query would return more than 1 value. Use "equal to" operator only when you know that you would always get only 1 value from the sub query, otherwise use "IN"

ALTER PROCEDURE [dbo].[bstint_issuedetls_bytestid]
  @projectid int,
  @moduleid int
AS
BEGIN
 
  SELECT  bstint_reportissue.issueid,
      bstint_reportissue.description
  FROM    bstint_reportissue
  INNER JOIN bstint_testlog ON bstint_reportissue.testcaseid = bstint_testlog.testcaseid
  WHERE  bstint_testlog.projectid = @projectid and bstint_testlog.moduleid = @moduleid
 
END
Web Star replied to Neethu on 14-Feb-12 12:27 AM
One other good way you can join both table and get your desired result as follows

ALTER PROCEDURE [dbo].[bstint_issuedetls_bytestid]
(
@projectid int,
@moduleid int
)
AS
BEGIN
SELECT bstint_reportissue.issueid, bstint_reportissue.description
FROM bstint_reportissue Inner join bstint_reportissue ON bstint_reportissue.testcaseid = bstint_testlog.testcaseid 
WHERE  bstint_testlog.projectid = @projectid and bstint_testlog.moduleid = @moduleid)

END 
Sreekumar P replied to Neethu on 14-Feb-12 12:27 AM
Hi,

There will be 2 records (or multiple records) returned by the sub query.
So if u want to display all the testcaseid records use this

SELECT bstint_reportissue.issueid,
bstint_reportissue.description
FROM bstint_reportissue
WHERE  bstint_reportissue.testcaseid IN (SELECT bstint_testlog.testcaseid
FROM bstint_testlog
WHERE  bstint_testlog.projectid = @projectid and bstint_testlog.moduleid = @moduleid)


OR

if u want to display only one (that is TOP record) , use this SQL Query

SELECT bstint_reportissue.issueid,
bstint_reportissue.description
FROM bstint_reportissue
WHERE  bstint_reportissue.testcaseid IN (SELECT TOP 1 bstint_testlog.testcaseid
FROM bstint_testlog
WHERE  bstint_testlog.projectid = @projectid and bstint_testlog.moduleid = @moduleid)

Somesh Yadav replied to Neethu on 14-Feb-12 01:53 AM
Hi,

Try this,

ALTER PROCEDURE [dbo].[bstint_issuedetls_bytestid]
(
@projectid int,
@moduleid int
)
AS
BEGIN
SELECT bstint_reportissue.issueid, bstint_reportissue.description
FROM bstint_reportissue Inner join bstint_reportissue ON bstint_reportissue.testcaseid = bstint_testlog.testcaseid
WHERE  bstint_testlog.projectid = @projectid and bstint_testlog.moduleid = @moduleid)

END

Hope it helps you.