How to pass multivalued parameter in a Table-Valued Function?
-
Hi All!
I have written a Table-Valued Function say GetMonthName. This Functions has one parameter. When I pass a value in this function, its working fine but when I pass multivalued parameter then its showing an error message:
Procedure or function dbo.DCBL_GetMonthName has too many arguments specified.
How I can handle this problem?
Thanks in advance...
Hari
-
By only passing the correct number of arguments?
Not excatly sure what you are saying, but if you have a function declared like :
create function dbo.fcn_test @input int
then you can only call that with one variable. If this is not what you meant please give example of the create function statement and how you call it when you get the error.
-
PLEASE! If you want help, post the offending query that gave you the error!
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
OK guys!
Here I'll explain my problem with an example:
I created following function:
CREATE
Function [dbo].[GetSplitedValues]
(
@CommaSeparatedString
varchar(8000)
)
RETURNS
@Table Table (ID int)
Begin
Declare @TempStr varchar(8000)
Set @TempStr = @CommaSeparatedString + ','
While Len(@TempStr) > 0
Begin
Insert Into @Table Select SubString(@TempStr,1,CharIndex(',',@TempStr)-1)
Set @TempStr = Right(@TempStr,Len(@TempStr)-CharIndex(',',@TempStr))
End
Return
End
When I run this function in Query Analyzer, its working fine. But when I call this function in Reporting Services 2005 to create a DataSet, its raising an error.
Example: Select ID From dbo.GetSplitedValues(@State)
Here @State is Multi-Valued Parameter and it may have string like '1,2,3' or '1,4,6,9,10' on the basis of values selected in the parameter list. So when this parameter is passed into the function the values are treated as 1,2,3 instead of '1,2,3' or 1,4,6,9,10 instead of '1,4,6,9,10'. Thats why its raising an error. How can I solve this problem.......??????????
-
I don't think anybody could have guessed what you meant from your original post.
Anyway, the problem is not in SQL.
Your front end progrm should add the surrounding quotes.
-
Hari... are you calling the function using SQL that's embedded in the GUI code? If so, Koji is absolutely correct... the code must add the outer quotes as part of the code. Post the call if you'd like...
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
I am experiencing the same problem. When you mention "Front End Code" are you talking about the code created thru Report services. If so, where in the code do I need to add the quotes?
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply