ProTech-Online.com

Microsoft SQL function code example to pull Syteline operation information.

USE [testsite_live_App]
GO
/****** Object:  UserDefinedFunction [MYSCHEMA].[_chad_Excel_Addin_Operations]    Script Date: 06/24/2013 23:43:00 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER FUNCTION [MYSCHEMA].[_chad_Excel_Addin_Operations] (
  @site nvarchar(20)
  ,@item nvarchar(30))
RETURNS nvarchar(MAX)
AS
BEGIN
      DECLARE @WCS AS nvarchar(MAX) = ''
      IF @site = 'testsite'
      BEGIN
            SET @WCS = STUFF((SELECT ',' + jbr.wc
                                    FROM testsite_Live_App.dbo.jobroute AS jbr
                                          INNER JOIN testsite_Live_App.dbo.job AS job
                                                ON job.job = jbr.job
                                                AND job.suffix = jbr.suffix
                                          INNER JOIN testsite_Live_App.dbo.item AS itm
                                                ON itm.job = job.job
                                          INNER JOIN testsite_Live_App.dbo.jrt_sch AS jsh
                                                ON jbr.job = jsh.job
                                                AND jbr.suffix = jsh.suffix
                                                AND jbr.oper_num = jsh.oper_num
                                    WHERE  job.suffix = '0' and job.type = 'S' AND itm.item IS NOT NULL
                                          AND itm.item = @item
                                    ORDER BY itm.item, jbr.oper_num
                                    FOR XML PATH('')), 1, 1, '')
      END
      ELSE IF @site = 'testsite2'
      BEGIN
            SET @WCS = STUFF((SELECT ',' + jbr.wc
                                    FROM testsite2_Live_App.dbo.jobroute AS jbr
                                          INNER JOIN testsite2_Live_App.dbo.job AS job
                                                ON job.job = jbr.job
                                                AND job.suffix = jbr.suffix
                                          INNER JOIN testsite2_Live_App.dbo.item AS itm
                                                ON itm.job = job.job
                                          INNER JOIN testsite2_Live_App.dbo.jrt_sch AS jsh
                                                ON jbr.job = jsh.job
                                                AND jbr.suffix = jsh.suffix
                                                AND jbr.oper_num = jsh.oper_num
                                    WHERE  job.suffix = '0' and job.type = 'S' AND itm.item IS NOT NULL
                                          AND itm.item = @item
                                    ORDER BY itm.item, jbr.oper_num
                                    FOR XML PATH('')), 1, 1, '')
      END
      ELSE IF @site = 'testsite3'
      BEGIN
            SET @WCS = STUFF((SELECT ',' + jbr.wc
                                    FROM testsite3_Live_App.dbo.jobroute AS jbr
                                          INNER JOIN testsite3_Live_App.dbo.job AS job
                                                ON job.job = jbr.job
                                                AND job.suffix = jbr.suffix
                                          INNER JOIN testsite3_Live_App.dbo.item AS itm
                                                ON itm.job = job.job
                                          INNER JOIN testsite3_Live_App.dbo.jrt_sch AS jsh
                                                ON jbr.job = jsh.job
                                                AND jbr.suffix = jsh.suffix
                                                AND jbr.oper_num = jsh.oper_num
                                    WHERE  job.suffix = '0' and job.type = 'S' AND itm.item IS NOT NULL
                                          AND itm.item = @item
                                    ORDER BY itm.item, jbr.oper_num
                                    FOR XML PATH('')), 1, 1, '')
      END
      RETURN @WCS

END