Select queries in one single SP

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Select queries in one single SP

Author

Topic

cplusplus
Aged Yak Warrior

567 Posts

Posted - 2008-07-05 : 06:25:47

How to get the total sum / count of records from below union query:

and want to check if rows are greater than 1 or not

if the result number of records are more than one, then would like to say "multiple" otherwise want to get the PNID

**********************************************************************
SELECT ModuleRecordID AS PNID
FROM TAB_ccsNetModuleLinks
WHERE ModuleName = 'CN'
AND LinkModuleRecordID = @COID
AND LinkModuleName = 'CO'

UNION ALL

SELECT LinkModuleRecordID AS PNID
FROM TAB_ccsNetModuleLinks
WHERE LinkModuleName = 'CN'
AND ModuleRecordID = @COID
AND ModuleName = 'CO'

**********************************************************************

Now if i get just 1 PNID from above query result, then want to execute another select query to get sequenceno field info using that PNID.

******************
select SequenceNO as PNSequenceNo from tab_PNinfo where pnid = (above pnid from union query)
******************

Thank you very much for the help.

ayamas
Aged Yak Warrior

552 Posts

Posted - 2008-07-05 : 07:48:39

Select count(pnid),pnid from
(

SELECT ModuleRecordID AS PNID
FROM TAB_ccsNetModuleLinks
WHERE ModuleName = 'CN'
AND LinkModuleRecordID = @COID
AND LinkModuleName = 'CO'

UNION ALL

SELECT LinkModuleRecordID AS PNID
FROM TAB_ccsNetModuleLinks
WHERE LinkModuleName = 'CN'
AND ModuleRecordID = @COID
AND ModuleName = 'CO'

)t group by pnid

wil get you the count of the pnid & the pnid value.

cplusplus
Aged Yak Warrior

567 Posts

Posted - 2008-07-05 : 11:19:40

Thanks for the help, But When i executed the query, it is showing in the count field as 1 but i am getting two records in total.

In the count field it should show 2.

quote:
Originally posted by ayamas

Select count(pnid),pnid from
(

SELECT ModuleRecordID AS PNID
FROM TAB_ccsNetModuleLinks
WHERE ModuleName = 'CN'
AND LinkModuleRecordID = @COID
AND LinkModuleName = 'CO'

UNION ALL

SELECT LinkModuleRecordID AS PNID
FROM TAB_ccsNetModuleLinks
WHERE LinkModuleName = 'CN'
AND ModuleRecordID = @COID
AND ModuleName = 'CO'

)t group by pnid

wil get you the count of the pnid & the pnid value.

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2008-07-05 : 12:43:43

Hello,
maybe something like this, but I have no possibility to make a test now.

Select count(pnid)as count_pnid,pnid
into #ttt
from
(

SELECT ModuleRecordID AS PNID
FROM TAB_ccsNetModuleLinks
WHERE ModuleName = 'CN'
AND LinkModuleRecordID = @COID
AND LinkModuleName = 'CO'

UNION ALL

SELECT LinkModuleRecordID AS PNID
FROM TAB_ccsNetModuleLinks
WHERE LinkModuleName = 'CN'
AND ModuleRecordID = @COID
AND ModuleName = 'CO'

)t group by pnid

select
case @@ROWCOUNT
when 0 then 'no rows'
when 1 then select * from #ttt
else 'multiple'
end

Greetings
Webfred

There are 10 types of people in the world: Those who understand binary, and those who don't...

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources