How can I pass multiple values to single parameter in SQL Function?

Question

This is how variables are declared:

CREATE FUNCTION Fn_custom_roomie_emails
(@htenant       NUMERIC(18), 
 @dtmoveout     DATETIME, 
 @lesseeonly    VARCHAR(3)= 'NO', 
 @relationship  VARCHAR(100),
 @listseparator VARCHAR(10) = '; ', 
 @nameseperator VARCHAR(10) = ', ', 
 @andfunction   VARCHAR(3) = 'NO', 
 @tenantemail   VARCHAR(100) = '')

The function works fine when I execute in this way:

exec dbo.Fn_custom_roomie_emails(hmy, Getdate(), 'All', **'Roommate'**, ';', ' ', '', 'YES')

But now I want to pass multiple relationships as shown below:

exec dbo.Fn_custom_roomie_emails(hmy, Getdate(), 'All', **'Roommate', 'Guarantor'**, ';', ' ', '', 'YES')

So how can I do that?

Use a [Table-Valued Parameter](https://msdn.microsoft.com/en-us/library/bb510489.aspx) — Giorgos Betsos, Dec 23 '15 at 08:50
you can send values comma-seprated and then split it into function — Sundar Singh, Dec 23 '15 at 08:52
Giorgos: So you mean I should store this relationships in table and pass that table to function? — Omkar, Dec 23 '15 at 09:02
Have a look [here](http://stackoverflow.com/questions/33491873/passing-multiple-values-to-a-parameter-of-a-function-in-sql) — Giorgos Betsos, Dec 23 '15 at 09:05

score 0 · Answer 1 · answered Dec 23 '15 at 09:25

0

like Giorgos said Table-Valued Parameter.you can declare like this

    declare @relationship table (relationship varchar(100))
    INSERT INTO @relationship(relationship) values('Roommate');

answered Dec 23 '15 at 09:25

Raffaello.D.Huke

552
2
9

It's not that simple. What you've shown here is a table variable, which you can't pass as a parameter. You need to define a table type first, then you can declare it, insert data into it, and finally pass it as a parameter. – Ben Thul Dec 23 '15 at 17:44

How can I pass multiple values to single parameter in SQL Function?

1 Answers1