sql server passing string for in parameters

Question

i have a scalar valued function like this

CREATE FUNCTION getTotalWorkingSeconds(@machineList varchar(255))
RETURNS int
AS
BEGIN
  declare @res int
  Select @res =  Sum(DURATION) From PROCESSDATA where MACHINEID in (@machineList)
  return @res
END

i tried to use it like these

SELECT dbo.getTotalWorkingSeconds('3,2')

result; Conversion failed when converting the varchar value '3,2' to data type int.

--

SELECT dbo.getTotalWorkingSeconds(''''+'3,2'+'''')

result; Conversion failed when converting the varchar value ''3,2'' to data type int.

how i can pass id list to this function?

Edit: i can alter the function.

variables are interpreted as single monolithic values. To get your 3,2 string to be treated as two comma-separated integers, you'd have to build/execute the query statement dynamically. — Marc B
– Marc B, Commented Apr 13, 2014 at 16:23
You will need to use Split Function inside your function to use these comma separated values. — M.Ali
– M.Ali, Commented Apr 13, 2014 at 16:52

Reza · Accepted Answer · 2014-04-13 16:57:25Z

3

One solution is using xml parameter, so

CREATE FUNCTION Split
(
  @delimited nvarchar(max),
  @delimiter nvarchar(100)
) RETURNS @t TABLE
(
-- Id column can be commented out, not required for sql splitting string
  id int identity(1,1), -- I use this column for numbering splitted parts
  val nvarchar(max)
)
AS
BEGIN
  declare @xml xml
  set @xml = N'<root><r>' + replace(@delimited,@delimiter,'</r><r>') + '</r></root>'

  insert into @t(val)
  select
    r.value('.','varchar(max)') as item
  from @xml.nodes('//root/r') as records(r)

  RETURN
END
GO

CREATE FUNCTION getTotalWorkingSeconds(@machineList varchar(255))
RETURNS int
AS
BEGIN
  declare @t table (val nvarchar(100))
  insert into @t select * from dbo.split(@machineList,',')

  declare @res int
  Select @res =  Sum(DURATION) From PROCESSDATA where MACHINEID in (select val from @t)
  return @res
END

edited Apr 13, 2014 at 16:57

answered Apr 13, 2014 at 16:39

Reza

20.1k18 gold badges102 silver badges177 bronze badges

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

3 Comments

M.Ali Over a year ago

You cannot use EXECUTE inside a function. Also the way you are using OUTPUT param in your dynamic sql it would not.

M.Ali Over a year ago

this is a proper solution which will work for this problem :) + 1, Also have a look at this answer to see how you can use OUTPUT params with dynamic sql OUTPUT Parameters With Dynamic sql

sddk Over a year ago

i have adited line "insert into @t select * from dbo.split(@machineList,',')" like this "insert into @t select val from dbo.split(@machineList,',')" and it worked, thanks.

Collectives™ on Stack Overflow

sql server passing string for in parameters

1 Answer 1

3 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related