Advertisement
Not a member of Pastebin yet?
Sign Up,
it unlocks many cool features!
- 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
- 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.
- 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
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement