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?

you can send values comma-seprated and then split it into function — Sundar Singh
– Sundar Singh, Commented Dec 23, 2015 at 8:52
Giorgos: So you mean I should store this relationships in table and pass that table to function? — Omkar
– Omkar, Commented Dec 23, 2015 at 9:02

Raffaello.D.Huke · Accepted Answer · 2015-12-23 09:25:45Z

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, 2015 at 9:25

Raffaello.D.Huke

5522 silver badges10 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Ben Thul Over a year ago

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.

Collectives™ on Stack Overflow

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

1 Answer 1

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related