ASP.NET - Sp

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

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 

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"

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 

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.