4 Replies - 701 Views - Last Post: 07 July 2016 - 12:39 PM

#1 ybadragon  Icon User is offline

  • Home Owner
  • member icon

Reputation: 571
  • View blog
  • Posts: 2,647
  • Joined: 11-May 12

Variable number of parameters

Posted 07 July 2016 - 07:50 AM

Is it possible to create a function that has a variable number of parameters similar to how the COALESCE function is made?

The coalesce function works by passing in a parameter, then another, then another and so on. As far as I know the limit to it is ~65,000 parameters. I would like to know if there is a way to give the same functionality to a function I create (Preferrably without having to manually write out every parameter in the function as a new one and defaulting it to NULL).

Is This A Good Question/Topic? 0
  • +

Replies To: Variable number of parameters

#2 modi123_1  Icon User is online

  • Suitor #2
  • member icon



Reputation: 13383
  • View blog
  • Posts: 53,410
  • Joined: 12-June 08

Re: Variable number of parameters

Posted 07 July 2016 - 08:00 AM

You could use a comma separated list... split it apart inside the procedure.

http://sqlperformanc...s/split-strings
http://sqlperformanc...rings-follow-up
Was This Post Helpful? 1
  • +
  • -

#3 ybadragon  Icon User is offline

  • Home Owner
  • member icon

Reputation: 571
  • View blog
  • Posts: 2,647
  • Joined: 11-May 12

Re: Variable number of parameters

Posted 07 July 2016 - 08:08 AM

I was looking into that, that does require me to split it on the receiving end though. But if that is the only way, then alas. I do question how COALESCE does it though.

EDIT - I suppose another option is using a Table Valued Parameter.

This post has been edited by ybadragon: 07 July 2016 - 08:10 AM

Was This Post Helpful? 0
  • +
  • -

#4 modi123_1  Icon User is online

  • Suitor #2
  • member icon



Reputation: 13383
  • View blog
  • Posts: 53,410
  • Joined: 12-June 08

Re: Variable number of parameters

Posted 07 July 2016 - 08:10 AM

By being a better, built in, function.
Was This Post Helpful? 0
  • +
  • -

#5 baavgai  Icon User is offline

  • Dreaming Coder
  • member icon


Reputation: 6966
  • View blog
  • Posts: 14,572
  • Joined: 16-October 07

Re: Variable number of parameters

Posted 07 July 2016 - 12:39 PM

If you're passing a ton of params, you could do something like this:
CREATE TABLE ParamSoup (
  ParamSoupId [uniqueidentifier] NOT NULL,
  AttrName [varchar](50) NOT NULL,
  AttrValue [varchar](500) NOT NULL
  CONSTRAINT PkParamSoup PRIMARY KEY (ParamSoupId)
)
GO

create procedure PsTest
  @ParamSoupId [uniqueidentifier]
as begin
  declare @table_name [varchar](500)
  declare @filter [varchar](500)
  select @table_name = min(case when a.AttrName='table_name' then a.AttrValue else null end),
      @filter = IsNull(min(case when a.AttrName='col_filter' then  a.AttrValue else null end), '%')
    from ParamSoup a
    where [email protected]
  select a.TABLE_NAME, a.COLUMN_NAME, a.ORDINAL_POSITION
    from INFORMATION_SCHEMA.COLUMNS a
    where a.TABLE_NAME=IsNull(@table_name,a.table_name)
      and a.COLUMN_NAME like @filter
    order by a.TABLE_NAME, a.ORDINAL_POSITION
end
go

-- test run
declare @qid [uniqueidentifier]
set @qid = NEWID()
-- insert into ParamSoup values(@qid, 'table_name', 'ParamSoup')

insert into ParamSoup values(@qid, 'col_filter', '%id%')
exec PsTest @qid

delete ParamSoup where [email protected]



This has the big advantage of your parameters surviving sessions. Of course, you may want a clean up, in which case you can timestamp it. Fishing the values out is rather primitive here; if you were serious then a number of helper procedures would be called for.
Was This Post Helpful? 1
  • +
  • -

Page 1 of 1