Sql Server 2012 How to Get List on Function

Question

Function Details:

alter FUNCTION siralama_puan (@suggestion_id int)
RETURNS int
AS
Begin
     Declare @comment_count int,@like_count int,@favorite_count int,@date_point int,@suggestion_point int,@suggestion_date datetime,@fark int

     set @comment_count=(select [Suggestion].CommentCount from [Suggestion] where  [Suggestion].Id= @suggestion_id)
     set @like_count=(select [Suggestion].LikeCount from [Suggestion] where [Suggestion].Id=@suggestion_id)
     set @favorite_count=(select [Suggestion].FavoriteCount from [Suggestion] where [Suggestion].Id=@suggestion_id)
     set @suggestion_date=(select [Suggestion].Crtm from [Suggestion] where [Suggestion].Id=@suggestion_id)
     set @fark =(select DATEDIFF(day,@suggestion_date,GETDATE()))

     if @fark<6  
     set @date_point=30
    else if @fark<10 and @fark>=6
     set @date_point=20
    else
    set @date_point=10

     set @suggestion_point=(@comment_count*2)+(@like_count)+(@favorite_count*3)+@date_point
     RETURN @suggestion_point
End

Calling Function:

select dbo.siralama_puan  (122280,1) as puan order by puan desc

but it didn't work.Error:Procedure or function dbo.siralama_puan has too many arguments specified.Multiple arguments not working.

well, of course it doesn't work because your function receives only one argument. What's your question? — Lamak, Mar 15 '16 at 15:29
You need to alter the function parameter to take comma separated id's. And then convert the comma separated ids into temp table and loop through the temp table rows and r insert into a table variable and return the table. — ab0369, Mar 15 '16 at 15:36
@ashy, the comma separated list is just one - and at least for me not the best - approach... Better use `CREATE TYPE` or XML (see my answer). — Shnugo, Mar 15 '16 at 16:02
You could set all variables at once. There is no need for multiple `SET`. *`SELECT @comment_count = CommentCount, @like_count = LikeCount ... FROM [Suggestion] WHERE Id=@suggestion_id`*. Anyway passing multiple ids you probably has to change entire SP body to be set-based no one-row-based. — Lukasz Szozda, Mar 15 '16 at 16:48

score 0 · Answer 1 · edited May 23 '17 at 12:23

It is not possible to define one parameter of type INT and pass in a list. But there are several approaches:

TYPE

Create your own type with CREATE TYPE read here.... This allows you to pass in a list very similar to a (read only) table

XML

Let your parameter be of type XML and pass in something like

<root>
   <prm value="122280"/>
   <prm value="1"/>
</root>

It's quite easy to transform this XML within your function to a list with something like

SELECT Prm.value('@value','int') AS PrmValue
FROM @prm.nodes('/root/prm') AS One(Prm)

CSV

A parameter of type VARCHAR(MAX) and a comma separated list like "122280,1". Find a way to split this here: section "Dynamic IN-statement"

Sql Server 2012 How to Get List on Function

1 Answers1

TYPE

XML

CSV