SQL Server Stored Procedure

The following stored procedure is giving error "The function has too many arguments".
Kindly help.
------------------------------

USE [drugs]
GO
/****** Object:  StoredProcedure [dbo].[FetchReport1Graph]    Script Date: 04/11/2012 20:11:48 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER procedure [dbo].[FetchReport1Graph]
(
   @Drug varchar(1000)
 )
as
 begin
   declare @strSelect varchar(1000)
      Begin Tran
      set @strSelect=  'select drugname, COUNT(patientId) as NumberDurg from AssessmentDrug where DrugName in(' + CAST(@Drug as varchar)+ ') group by DrugName'
       EXEC(@strSelect)
      Commit
 end
Sanjay GandhiFounder, KenhalAsked:
Who is Participating?

Improve company productivity with a Business Account.Sign Up

x
 
parthmalhanConnect With a Mentor Commented:
try Below:



ALTER procedure [dbo].[FetchReport1Graph]
(
   @Drug varchar(1000)
 )
as
 begin
   declare @strSelect varchar(1000)
      Begin Tran
 set @strSelect=  'select drugname, COUNT(patientId) as NumberDurg from AssessmentDrug where DrugName in(''' + CAST(@Drug as varchar)+ ''') group by DrugName'
ALTER procedure [dbo].[FetchReport1Graph]
(
   @Drug varchar(1000)
 )
as
 begin
   declare @strSelect varchar(1000)
      Begin Tran
0
 
sachinpatil10dConnect With a Mentor Commented:
Your StoreProcedure excepts only one parameter  @Drug varchar(1000)

so the storeprocedure call will be

exec [dbo].[FetchReport1Graph] '100'

Open in new window


may be you are using it like this

exec [dbo].[FetchReport1Graph] '100','100'

Open in new window


which gives following error

Msg 8144, Level 16, State 2, Procedure FetchReport1Graph, Line 0
Procedure or function FetchReport1Graph has too many arguments specified.

you can use it

exec [dbo].[FetchReport1Graph] '100,200'

Open in new window

0
 
Sanjay GandhiFounder, KenhalAuthor Commented:
Let me try and revert... getting slightly busy....will revert by evening...thanks.
0
 
Sanjay GandhiFounder, KenhalAuthor Commented:
Thanks, works fine.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.