Solved

SQL Server Stored Procedure

Posted on 2012-04-11
4
290 Views
Last Modified: 2012-04-12
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
0
Comment
Question by:sanjay-gandhi
  • 2
4 Comments
 
LVL 4

Accepted Solution

by:
parthmalhan earned 400 total points
ID: 37836211
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
 
LVL 9

Assisted Solution

by:sachinpatil10d
sachinpatil10d earned 100 total points
ID: 37836215
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
 

Author Comment

by:sanjay-gandhi
ID: 37836261
Let me try and revert... getting slightly busy....will revert by evening...thanks.
0
 

Author Closing Comment

by:sanjay-gandhi
ID: 37837734
Thanks, works fine.
0

Featured Post

Find Ransomware Secrets With All-Source Analysis

Ransomware has become a major concern for organizations; its prevalence has grown due to past successes achieved by threat actors. While each ransomware variant is different, we’ve seen some common tactics and trends used among the authors of the malware.

Join & Write a Comment

Suggested Solutions

I wrote this interesting script that really help me find jobs or procedures when working in a huge environment. I could I have written it as a Procedure but then I would have to have it on each machine or have a link to a server-related search that …
Introduction SQL Server Integration Services can read XML files, that’s known by every BI developer.  (If you didn’t, don’t worry, I’m aiming this article at newcomers as well.) But how far can you go?  When does the XML Source component become …
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function

744 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now