SQL Code snippets from the "Troubleshooting the WCF-Custom adapter - Typed-Polling with dynamic SQL" blog post

Typed-Polling with dynamic SQL

USE [master]
GO
/****** Object:  Database [ShopDB]    Script Date: 13/03/2014 18:13:44 ******/
CREATE DATABASE [ShopDB]
 CONTAINMENT = NONE
 ON  PRIMARY 
( NAME = N'ShopDB', FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\DATA\ShopDB.mdf' , SIZE = 5120KB , MAXSIZE = UNLIMITED, FILEGROWTH = 1024KB )
 LOG ON 
( NAME = N'ShopDB_log', FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\DATA\ShopDB_log.ldf' , SIZE = 1024KB , MAXSIZE = 2048GB , FILEGROWTH = 10%)
GO
ALTER DATABASE [ShopDB] SET COMPATIBILITY_LEVEL = 110
GO
IF (1 = FULLTEXTSERVICEPROPERTY('IsFullTextInstalled'))
begin
EXEC [ShopDB].[dbo].[sp_fulltext_database] @action = 'enable'
end
GO
ALTER DATABASE [ShopDB] SET ANSI_NULL_DEFAULT OFF 
GO
ALTER DATABASE [ShopDB] SET ANSI_NULLS OFF 
GO
ALTER DATABASE [ShopDB] SET ANSI_PADDING OFF 
GO
ALTER DATABASE [ShopDB] SET ANSI_WARNINGS OFF 
GO
ALTER DATABASE [ShopDB] SET ARITHABORT OFF 
GO
ALTER DATABASE [ShopDB] SET AUTO_CLOSE OFF 
GO
ALTER DATABASE [ShopDB] SET AUTO_CREATE_STATISTICS ON 
GO
ALTER DATABASE [ShopDB] SET AUTO_SHRINK OFF 
GO
ALTER DATABASE [ShopDB] SET AUTO_UPDATE_STATISTICS ON 
GO
ALTER DATABASE [ShopDB] SET CURSOR_CLOSE_ON_COMMIT OFF 
GO
ALTER DATABASE [ShopDB] SET CURSOR_DEFAULT  GLOBAL 
GO
ALTER DATABASE [ShopDB] SET CONCAT_NULL_YIELDS_NULL OFF 
GO
ALTER DATABASE [ShopDB] SET NUMERIC_ROUNDABORT OFF 
GO
ALTER DATABASE [ShopDB] SET QUOTED_IDENTIFIER OFF 
GO
ALTER DATABASE [ShopDB] SET RECURSIVE_TRIGGERS OFF 
GO
ALTER DATABASE [ShopDB] SET  DISABLE_BROKER 
GO
ALTER DATABASE [ShopDB] SET AUTO_UPDATE_STATISTICS_ASYNC OFF 
GO
ALTER DATABASE [ShopDB] SET DATE_CORRELATION_OPTIMIZATION OFF 
GO
ALTER DATABASE [ShopDB] SET TRUSTWORTHY OFF 
GO
ALTER DATABASE [ShopDB] SET ALLOW_SNAPSHOT_ISOLATION OFF 
GO
ALTER DATABASE [ShopDB] SET PARAMETERIZATION SIMPLE 
GO
ALTER DATABASE [ShopDB] SET READ_COMMITTED_SNAPSHOT OFF 
GO
ALTER DATABASE [ShopDB] SET HONOR_BROKER_PRIORITY OFF 
GO
ALTER DATABASE [ShopDB] SET RECOVERY FULL 
GO
ALTER DATABASE [ShopDB] SET  MULTI_USER 
GO
ALTER DATABASE [ShopDB] SET PAGE_VERIFY CHECKSUM  
GO
ALTER DATABASE [ShopDB] SET DB_CHAINING OFF 
GO
ALTER DATABASE [ShopDB] SET FILESTREAM( NON_TRANSACTED_ACCESS = OFF ) 
GO
ALTER DATABASE [ShopDB] SET TARGET_RECOVERY_TIME = 0 SECONDS 
GO
USE [ShopDB]
GO
/****** Object:  Table [dbo].[tbl_Shipment_WarehouseA]    Script Date: 13/03/2014 18:13:44 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[tbl_Shipment_WarehouseA](
	[ID] [int] IDENTITY(1,1) NOT NULL,
	[ORDER_ID] [nvarchar](50) NOT NULL,
	[WAREHOUSE_FROM] [nvarchar](20) NOT NULL,
	[WAREHOUSE_TO] [nvarchar](20) NOT NULL,
	[QUANTITY] [int] NOT NULL,
	[STATUS_ID] [int] NULL,
 CONSTRAINT [PK_tbl_Shipment_WarehouseA] PRIMARY KEY CLUSTERED 
(
	[ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
 
GO
/****** Object:  Table [dbo].[tbl_Shipment_WarehouseB]    Script Date: 13/03/2014 18:13:44 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[tbl_Shipment_WarehouseB](
	[ID] [int] IDENTITY(1,1) NOT NULL,
	[ORDER_ID] [nvarchar](50) NOT NULL,
	[WAREHOUSE_FROM] [nvarchar](20) NOT NULL,
	[WAREHOUSE_TO] [nvarchar](20) NOT NULL,
	[QUANTITY] [int] NOT NULL,
	[STATUS_ID] [int] NOT NULL,
 CONSTRAINT [PK_tbl_Shipment_WarehouseB] PRIMARY KEY CLUSTERED 
(
	[ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
 
GO
SET IDENTITY_INSERT [dbo].[tbl_Shipment_WarehouseA] ON 
 
GO
INSERT [dbo].[tbl_Shipment_WarehouseA] ([ID], [ORDER_ID], [WAREHOUSE_FROM], [WAREHOUSE_TO], [QUANTITY], [STATUS_ID]) VALUES (1, N'ORDR-A-0001', N'WARE-1000', N'WARE-1111', 1337, 10)
GO
INSERT [dbo].[tbl_Shipment_WarehouseA] ([ID], [ORDER_ID], [WAREHOUSE_FROM], [WAREHOUSE_TO], [QUANTITY], [STATUS_ID]) VALUES (2, N'ORDR-A-0002', N'WARE-1000', N'WARE-1111', 5, 1)
GO
INSERT [dbo].[tbl_Shipment_WarehouseA] ([ID], [ORDER_ID], [WAREHOUSE_FROM], [WAREHOUSE_TO], [QUANTITY], [STATUS_ID]) VALUES (3, N'ORDR-A-0003', N'WARE-1000', N'WARE-2222', 5, 1)
GO
SET IDENTITY_INSERT [dbo].[tbl_Shipment_WarehouseA] OFF
GO
SET IDENTITY_INSERT [dbo].[tbl_Shipment_WarehouseB] ON 
 
GO
INSERT [dbo].[tbl_Shipment_WarehouseB] ([ID], [ORDER_ID], [WAREHOUSE_FROM], [WAREHOUSE_TO], [QUANTITY], [STATUS_ID]) VALUES (1, N'ORDR-B-0001', N'WARE-2000', N'WARE-1000', 100, 10)
GO
INSERT [dbo].[tbl_Shipment_WarehouseB] ([ID], [ORDER_ID], [WAREHOUSE_FROM], [WAREHOUSE_TO], [QUANTITY], [STATUS_ID]) VALUES (2, N'ORDR-B-0002', N'WARE-2000', N'WARE-5146', 456, 1)
GO
INSERT [dbo].[tbl_Shipment_WarehouseB] ([ID], [ORDER_ID], [WAREHOUSE_FROM], [WAREHOUSE_TO], [QUANTITY], [STATUS_ID]) VALUES (4, N'ORDR-B-0003', N'WARE-2000', N'WARE-2134', 350, 1)
GO
SET IDENTITY_INSERT [dbo].[tbl_Shipment_WarehouseB] OFF
GO
USE [master]
GO
ALTER DATABASE [ShopDB] SET  READ_WRITE 
GO

Typed-Polling with dynamic SQL - Create called SP with function

USE ShopDB
GO
 
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:		Tom Kerkhove
-- Create date: 02/06/2014
-- Description:	Export all shipments
-- =============================================
CREATE PROCEDURE ExportShipments
	@Warehouse nvarchar(10)
AS
BEGIN
	SET NOCOUNT ON;
 
	-- MARK DATA AS LOCKED
	DECLARE @lockData NVARCHAR(MAX);
	SET @lockData = N'UPDATE [ShopDB].[dbo].[tbl_Shipment_' + @Warehouse + '] SET [STATUS_ID] = 7 WHERE [STATUS_ID] = 1';
	EXEC(@lockData);
 
	-- EXTRACT DATA
	DECLARE @exportData NVARCHAR(MAX);
	SET @exportData = [ShopDB].[dbo].[ComposeExportShipmentSelect] (@Warehouse)
 
	-- CREATE TEMP TABLE
	SET FMTONLY OFF;
	CREATE TABLE #tempTable
		(
			  [ID] [INT] NOT NULL,
			  [ORDER_ID] [NVARCHAR](50) NOT NULL,
			  [WAREHOUSE_FROM] [NVARCHAR](20) NOT NULL,
			  [WAREHOUSE_TO] [NVARCHAR](18) NULL,
			  [QUANTITY] [INT] NOT NULL
		   )
	
	-- INSERT SELECT RESULTS INTO TEMP TABLE
	INSERT INTO #tempTable
		EXEC(@exportData);
 
	-- MARK DATA AS PROCESSED
	DECLARE @markProcessed NVARCHAR(MAX);
	SET @markProcessed = N'UPDATE [ShopDB].[dbo].[tbl_Shipment_' + @Warehouse + '] SET [STATUS_ID] = 10 WHERE [STATUS_ID] = 7';
	EXEC(@markProcessed);
 
	-- RETURN RESULT
	SELECT * FROM #tempTable;
END
GO

Typed-Polling with dynamic SQL - Create called SP with Function & FMTONLY

USE ShopDB
GO
 
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:		Tom Kerkhove
-- Create date: 02/06/2014
-- Description:	Export all shipments
-- =============================================
CREATE PROCEDURE ExportShipments
	@Warehouse nvarchar(10)
AS
BEGIN
	SET NOCOUNT ON;
 
	-- MARK DATA AS LOCKED
	DECLARE @lockData NVARCHAR(MAX);
	SET @lockData = N'UPDATE [ShopDB].[dbo].[tbl_Shipment_' + @Warehouse + '] SET [STATUS_ID] = 7 WHERE [STATUS_ID] = 1';
	EXEC(@lockData);
 
	-- EXTRACT DATA
	DECLARE @exportData NVARCHAR(MAX);
	SET @exportData = [ShopDB].[dbo].[ComposeExportShipmentSelect] (@Warehouse)
	EXEC(@exportData);
 
	-- MARK DATA AS PROCESSED
	DECLARE @markProcessed NVARCHAR(MAX);
	SET @markProcessed = N'UPDATE [ShopDB].[dbo].[tbl_Shipment_' + @Warehouse + '] SET [STATUS_ID] = 10 WHERE [STATUS_ID] = 7';
	EXEC(@markProcessed);
 
END
GO

Typed-Polling with dynamic SQL - Create called SP with Result Set SP

USE ShopDB
GO
 
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:		Tom Kerkhove
-- Create date: 02/06/2014
-- Description:	Export all shipments
-- =============================================
CREATE PROCEDURE ExportShipments
	@Warehouse nvarchar(10)
AS
BEGIN
	SET NOCOUNT ON;
 
	-- MARK DATA AS LOCKED
	DECLARE @lockData NVARCHAR(MAX);
	SET @lockData = N'UPDATE [ShopDB].[dbo].[tbl_Shipment_' + @Warehouse + '] SET [STATUS_ID] = 7 WHERE [STATUS_ID] = 1';
	EXEC(@lockData);
 
	-- EXTRACT DATA
	DECLARE @exportData NVARCHAR(MAX);
	EXEC [ShopDB].[dbo].[ExportShipment_AcquireResults] @Warehouse
 
	-- MARK DATA AS PROCESSED
	DECLARE @markProcessed NVARCHAR(MAX);
	SET @markProcessed = N'UPDATE [ShopDB].[dbo].[tbl_Shipment_' + @Warehouse + '] SET [STATUS_ID] = 10 WHERE [STATUS_ID] = 7';
	EXEC(@markProcessed);
 
END
GO

Typed-Polling with dynamic SQL - Create SELECT with a function

USE ShopDB
GO
 
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:		Tom Kerkhove
-- Create date: 02/06/2014
-- Description:	Compose SELECT-statement to export shipments
-- =============================================
CREATE FUNCTION ComposeExportShipmentSelect
(
	@Warehouse nvarchar(10)
)
RETURNS NVARCHAR(MAX)
AS
BEGIN
	-- DECLARE SELECT-STRING
	DECLARE @result NVARCHAR(MAX);
 
	-- COMPOSE SELECT STATEMENT
	SET @result = 'SELECT [ID], [ORDER_ID], [WAREHOUSE_FROM], [WAREHOUSE_TO], [QUANTITY] FROM [ShopDB].[dbo].[tbl_Shipment_' + @Warehouse + '] WHERE [STATUS_ID] = 7';
 
	-- RETURN SELECT STRING
	RETURN @result
 
END
GO

Typed-Polling with dynamic SQL - Export Result Set

USE ShopDB
GO
 
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:		Tom Kerkhove
-- Create date: 02/06/2014
-- Description:	Compose SELECT-statement to export shipments
-- =============================================
CREATE PROCEDURE ExportShipment_AcquireResults
	@Warehouse nvarchar(10)
AS DECLARE @result 
      TABLE(
			  [ID] [INT] NOT NULL,
			  [ORDER_ID] [NVARCHAR](50) NOT NULL,
			  [WAREHOUSE_FROM] [NVARCHAR](20) NOT NULL,
			  [WAREHOUSE_TO] [NVARCHAR](18) NULL,
			  [QUANTITY] [INT] NOT NULL
		   )
BEGIN
	-- DECLARE SELECT-STRING
	DECLARE @select NVARCHAR(MAX);
 
	-- COMPOSE SELECT STATEMENT
	SET @select = 'SELECT [ID], [ORDER_ID], [WAREHOUSE_FROM], [WAREHOUSE_TO], [QUANTITY] FROM [ShopDB].[dbo].[tbl_Shipment_' + @Warehouse + '] WHERE [STATUS_ID] = 7';
 
	-- EXECUTE SELECT AND INSERT INTO RESULT
	INSERT INTO @result
		(
			[ID],
			[ORDER_ID],
			[WAREHOUSE_FROM],
			[WAREHOUSE_TO],
			[QUANTITY]
		)
		EXEC(@select);
 
	-- RETURN RESULT SET
	SELECT * FROM @result;
END
GO