how to solve this error in stored procedure

Posted by Gow.Net under Sql Server on 9/13/2012 | Points: 10 | Views : 2124 | Status : [Member] | Replies : 3

Write New Post |

Search Forums | Answered

Resolved Posts |

Un Answered Posts |

Forums Home

when i execute this code

alter proc sp_pub
(
@param_category_name varchar(20)
)
as
begin

declare @c_id int
declare @p_id int

set @c_id=(select Cat_ID from BookCategory where Category=@param_category_name )
set @p_id=(select distinct(pub_ID) from NewBook where Cat_ID=@c_id)
select PublisherName from Publisher where pub_ID=@p_id


end

select distinct(pub_ID) from NewBook where Cat_ID=@c_id
output
1
2
3
4
5
i get following error
Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.

how to solve this error any one help me

gowthaman8870226416

Reply | Reply with Attachment

Alert Moderator

Responses

Posted by: Tanujad on: 9/13/2012 [Member] Starter | Points: 25

0	You cannot assign more than 1 value to an integer variable. DISTINCT returns all the unique ids in the table, which is bound to be more than 1. You can assign just a single value to a variable. Thanks Tanuja (If this content helped you, Please mark it as Answer) Gow.Net, if this helps please login to Mark As Answer. \| Alert Moderator

Posted by: Pandians on: 9/13/2012 [Member] [MVP] Silver | Points: 25

0	Check It Out! You can change the script alter proc sp_pub ( @param_category_name varchar(20) ) as begin Set Nocount On Select Distinct P.PublisherName from BookCategory BC join NewBook NB On (BC.Cat_ID = NB.Cat_ID) Join Publisher P On (P.pub_ID = NB.pub_ID) where BC.Category=@param_category_name end Cheers www.SQLServerbuddy.blogspot.com iLink Multitech Solutions Gow.Net, if this helps please login to Mark As Answer. \| Alert Moderator

Check It Out!

You can change the script

alter proc sp_pub

(

@param_category_name varchar(20)

)

as

begin

	Set Nocount On

	

	Select Distinct P.PublisherName from BookCategory BC 

	join NewBook NB On (BC.Cat_ID = NB.Cat_ID)

	Join Publisher P On (P.pub_ID = NB.pub_ID)

	where BC.Category=@param_category_name

end

Cheers
www.SQLServerbuddy.blogspot.com
iLink Multitech Solutions

Gow.Net, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Hariinakoti on: 9/14/2012 [Member] Starter | Points: 25

0	Hi Gow.net, u r not giving any allias names in ur query.First u give allias names then check it. Thanks & Regards Hari Gow.Net, if this helps please login to Mark As Answer. \| Alert Moderator

Latest Posts