how to solve this error in stored procedure

Posted by Gow.Net under Sql Server on 9/13/2012 | Points: 10 | Views : 1169 | Status : [Member] | Replies : 3
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


Responses

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

Up
0
Down
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

Up
0
Down
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

Up
0
Down
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

Login to post response