KCPTFzN5

1USE [PantaRei]
2GO
3/****** Object:  StoredProcedure [dbo].[InsertDdrsData]    Script Date: 08/22/2012 14:29:28 ******/
4SET ANSI_NULLS ON
5GO
6SET QUOTED_IDENTIFIER ON
7GO
8
9-- =============================================
10-- Author:      <Author,,Name>
11-- Create date: <Create Date,,>
12-- Description: <Description,,>
13-- =============================================
14ALTER PROCEDURE [dbo].[InsertDdrsData] 
15@RECEIVABLESDATA RECEIVABLESTABLE READONLY,
16@DILUTIONSDATA DILUTIONSTABLE READONLY,
17@ACCOUNTABLESDATA ACCOUNTABLESTABLE READONLY,
18@OUTSTANDINGSDATA OUTSTANDINGSTABLE READONLY,
19@TABLE1 VARCHAR(MAX),
20@TABLE2 VARCHAR(MAX),
21@TABLE3 VARCHAR(MAX),
22@TABLE4 VARCHAR(MAX)
23AS
24BEGIN
25    -- SET NOCOUNT ON added to prevent extra result sets from
26    -- interfering with SELECT statements.
27    DECLARE @CMD nvarchar(345)
28
29    SET NOCOUNT ON
30
31    SET @TABLE1 = RTRIM(@TABLE1)
32    SET @TABLE2 = RTRIM(@TABLE2)
33    SET @TABLE3 = RTRIM(@TABLE3)
34
35    SELECT * INTO #t1 FROM @RECEIVABLESDATA
36
37    SET @CMD =
38
39        'INSERT INTO ' + QUOTENAME(@TABLE1) +
40        'SELECT * FROM #t1'
41
42    EXECUTE sp_executesql @CMD
43
44    SELECT * INTO #t2 FROM @DILUTIONSDATA
45
46    SET @CMD =
47
48        'INSERT INTO ' + QUOTENAME(@TABLE2) +
49        'SELECT * FROM #t2'
50
51    EXECUTE sp_executesql @CMD
52
53    SELECT * INTO #t3 FROM @ACCOUNTABLESDATA
54
55    SET @CMD =
56
57        'INSERT INTO ' + QUOTENAME(@TABLE3) +
58        'SELECT * FROM #t3'
59
60    EXECUTE sp_executesql @CMD
61
62    IF @OUTSTANDINGSDATA IS NOT NULL
63        BEGIN
64            SELECT * INTO #t4 FROM @OUTSTANDINGSDATA
65
66            SET @CMD =
67
68                'INSERT INTO ' + QUOTENAME(@TABLE4) +
69                'SELECT * FROM #t4'
70
71            EXECUTE sp_executesql @CMD
72
73        END 
74END
75	
76IF NOT EXISTS (SELECT * FROM @OUTSTANDINGSDATA)
77	
78CREATE TYPE LocationTableType AS TABLE 
79( LocationName VARCHAR(50)
80, CostRate INT );
81GO
82CREATE PROCEDURE dbo.usp_InsertProductionLocation
83    @TVP LocationTableType READONLY
84    AS 
85    SET NOCOUNT ON
86    SELECT *
87        FROM  @TVP;
88GO
89EXEC dbo.usp_InsertProductionLocation NULL
90	
91Msg 206, Level 16, State 2, Procedure usp_InsertProductionLocation, Line 0
92Operand type clash: NULL is incompatible with LocationTableType