Too many arguments specified!!

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

Development Tools

Reporting Services Development

Too many arguments specified!!

Author

Topic

chriskhan2000
Aged Yak Warrior

544 Posts

Posted - 2004-12-01 : 12:10:17

I have several stored procedures in my SQL server that I would like to run using one expressional condition. For example if they pick A, then run stored procedure 1, B then run stored procedure 2, etc. I'm only using one dataset for the condition. Some of my stored procedure have the same parameter names while others don't. When it runs, the one that doesn't have those parameters in those stored procedure, I get this error "too many arguments specified". I put the parameters name and value in the parameters tab in my dataset.

The question is can I put the parameter name and value in my conditional expression? Here's my code.

=iif(Parameters!Source.Value = "EMPLOYEES", "CW2003.dbo.SP_RPT_EMPLOYEE", iif(Parameters!Source.Value = "FIN", "FIN.dbo.SP_RPT_EMPLOYEEFIN", iif(Parameters!Source.Value = "ACCT", "ACCT.dbo.SP_RPT_EMPLOYEEACCT", iif(Parameters!Source.Value = "ENG", "HR.dbo.SP_RPT_EMPLOYEEHR", "ENG.dbo.SP_RPT_EMPLOYEEENG"))))

So some of the stored procedure would have @ID, @STARTDATE, and @ENDDATE

Others have @ID, @STARTDATE, @ENDDATE, @LEVEL, @LOCATION

In the dataset where the Parameter Tab is, I put @ID, @STARTDATE, @ENDDATE, @LEVEL, @LOCATION.

It works fine for those that have all the parameters, but those that don't have it, gives me that too many argument error.

Any help or suggestions? Thanks in advance.

tikus
Starting Member

31 Posts

Posted - 2004-12-01 : 15:32:29

You can create a main SP that calls all the sub SPs depending on user's selection. The main SP takes all the parameters. When you are in the main SP you decide which sub SP to call.

For example, MainSP @Pick, @ID, @STARTDATE, @ENDDATE, @LEVEL, @LOCATION

Inside the MainSP, you can have

IF @Pick = 'A'
EXEC SubSP1 @ID, @STARTDATE, @ENDDATE
IF @Pick = 'B'
EXEC SUbSP2 @ID, @STARTDATE, @ENDDATE, @Level
IF @Pick = 'C'
EXEC SubSP3 @ID, @STARTDATE, @ENDDATE, @Level, @Location

so on and so forth...

tikus
Starting Member

31 Posts

Posted - 2004-12-01 : 15:39:48

Also, in the MainSP, set the default values for thte uncommon parameters to NULL to avoid error.

chriskhan2000
Aged Yak Warrior

544 Posts

Posted - 2004-12-01 : 15:41:55

Thanks Tikus. I think that will work. But I don't know how this will work with my stored procedure since some of them resides in different databases. They all have same schema, fields, and tables, but different databases. If I create the main SP, where do I put this? Do I need to put this in all of my databases that contains the stored procedures that I want to call from?

In my conditional expression, I have CW2003, FIN, ACCT, ENG, and HR database. They are all the same schema, tables, and fields. Only thing is that each database belongs to each department. NOt sure how I will be able to make the main SP.

tikus
Starting Member

31 Posts

Posted - 2004-12-02 : 01:55:26

I have an idea, to make things simple, make all of your SPs with the same parameters. Even though you don't need the parameters, just create them to make your code works. Set the default value to null to prevent the SPs from breaking.

For example:

SP1 @ID, @STARTDATE, @ENDDATE
SP2 @ID, @STARTDATE, @ENDDATE, @Level
SP3 @ID, @STARTDATE, @ENDDATE, @Level, @Location

Now,

SP1 @ID, @STARTDATE, @ENDDATE, @Level = null, @Location = null
SP2 @ID, @STARTDATE, @ENDDATE, @Level, @Location = null
SP3 @ID, @STARTDATE, @ENDDATE, @Level, @Location

Hope this is helping.

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2004-12-02 : 09:37:39