Want to return nothing if mulitiple results are fo

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)

Want to return nothing if mulitiple results are fo

Author

Topic

trackjunkie
Starting Member

31 Posts

Posted - 2009-09-30 : 16:24:28

I have the following stored proceedure to select iformation for a crystal report

ALTER Procedure [dbo].[Flex_RPT_CustNameFromItem] (
@item nvarchar(15)
)

Select
(select custaddr.name
from custaddr
join itemcust on custaddr.cust_num=itemcust.cust_num and itemcust.item=@item and custaddr.cust_seq = 0) As cust_name,
(select itemcust.cust_item
from itemcust
where itemcust.item = @item) as cust_item

The problem is there can be multiple returned rows for custaddr.name from the first select statement. If this is the case I want the whole proceedure to return nothing (sounds strange but makes sense in my application). If there is only one result I want it to work as it does above.

Can I wrap the whole thing in an if/else block to accomplish this? Something else?

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2009-09-30 : 17:16:27

I'm not sure I fully undertand, but you could add a GROUP BY and a HAVING clause or maybe use an IF statement or an EXISTS clasue.. depends what you want for output. Here is a quick example that might guide you:

	select 
		custaddr.name
	from 
		custaddr
	join 
		itemcust 
		on custaddr.cust_num=itemcust.cust_num 
		and itemcust.item=@item 
		and custaddr.cust_seq = 0
	GROUP BY
		custaddr.name
	HAVING COUNT(*) = 1

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-10-01 : 01:28:35

[code]ALTER Procedure [dbo].[Flex_RPT_CustNameFromItem] (
@item nvarchar(15)
)
Select *
from
(
Select custaddr.name As cust_name,
itemcust.cust_item as cust_item,
count(custaddr.name) over (partition by itemcust.cust_item) AS Occurance
from custaddr
join itemcust
on custaddr.cust_num=itemcust.cust_num
and itemcust.item=@item
and custaddr.cust_seq = 0
)t
WHERE Occurance=1
[/code]

trackjunkie
Starting Member

31 Posts

Posted - 2009-10-01 : 10:25:46

Thanks to last poster, that worked. Don't know what i'd to without you guys. Maybe someday i'll be good enough to actually contribute a solution....

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-10-01 : 14:40:36

welcome

you surely will do it one day provided you learn and understand the solutions given here

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources