How to use sub query in stored procedure?

Question

1.00/5 (1 vote)

See more:

, +

My sub query returns a single value and that value consists of multiple value which are separated by comma.
My sub query is like this:-

SQL

select ApplGLCd from ebreportstructureho
where grpname = 'Appendix'

and it's output is this:-0034,0035,0064,0026,0030,0031,0056

When I use the sub query with the Stored Procedure though it don't give any sort of error but it is supposed to return some data which unfortunately not happening.

What I have tried:

SQL

SELECT date,SUM(clcr-cldr) AS ClosingCr FROM ebgltransaction  
WHERE brncd='0002' AND glcd IN (select ApplGLCd from ebreportstructureho
where grpname = 'Appendix') AND date BETWEEN '20151001' AND '20151031'
GROUP BY date
ORDER BY date

Posted 7-Feb-16 20:16pm

Member 11579819

Updated 23-Feb-23 18:15pm

Jörgen Andersson

v2

Add a Solution

Comments

Tomas Takac 8-Feb-16 2:24am

I guess the data isn't there then.

Member 11579819 8-Feb-16 2:27am

Data is there. If I just run the sub query it returns the value and if I hard code the value returned by the sub query then my Stored Procedure works fine. But if I use Sub query within Stored procedure then it do not give any output.

aarif moh shaikh 8-Feb-16 2:35am

I think one of all conditions are returning null. please check it

Member 11579819 8-Feb-16 2:40am

I checked that with all the values... None is null among them.

Jörgen Andersson 8-Feb-16 3:13am

You're breaking the first Normal Form right there.
While Asifs solution fixes your immediate problem it's not a long term solution. Read up on normalization

2 solutions

Solution 2

There should have been some trailing or leading junk data or space.please make sure whether data is in accordance to the requirement

Posted 23-Feb-23 18:15pm

Member 15933443

Updated 23-Feb-23 18:17pm

v2

Comments

Richard Deeming 24-Feb-23 4:42am

If you'd bothered to read the accepted solution, which was posted seven years ago, you'd understand why your answer is wrong.

You can't pass a string containing multiple comma-separated values to an In query and expect it to match on part of the string; it will try to match the entire list of values, which won't work.

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)

_Asif_ · Accepted Answer · 2016-02-07T20:45:00

You need to break your code in two parts. First will fill a table variable by splitting returned result of a sub query and the second part will use this table variable for getting result. A pseudo code would be like this

SQL

DECLARE @TBL TABLE
( 
   GL_ID VARCHAR(100)
) 

INSERT INTO @TBL (GL_ID)
SELECT Item 
FROM   (select ApplGLCd from ebreportstructureho
where grpname = 'Appendix') T CROSS APPLY dbo.SplitStrings_Moden(T.ApplGLCd,',')

SELECT date,SUM(clcr-cldr) AS ClosingCr 
FROM ebgltransaction 
     INNER JOIN @TBL T ON ebgltransaction.glcd = T.GL_ID
WHERE brncd='0002' 
AND date BETWEEN '20151001' AND '20151031'
GROUP BY date
ORDER BY date

Please refer below for split functions
Split strings the right way - or the next best way[^]