EVCB_OCPP.Common/EVCB_OCPP.Domain/StoredProcedure/uspInsertMachineConnectionLog.sql

USE [OCPP_ConnectionLogDBContext]
GO

/****** Object:  StoredProcedure [dbo].[uspInsertMachineConnectionLog]    Script Date: 2019/07/25 下午 04:12:56 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

-- =============================================
-- Author:		<Author,,Name>
-- Create date: <Create Date,,>
-- Description:	<Description,,>
-- =============================================
CREATE PROCEDURE [dbo].[uspInsertMachineConnectionLog]
	-- Add the parameters for the stored procedure here
	@CreatedOn datetime,
	@ChargePointSerialNumber nvarchar(25) ,
	@MessageType nvarchar(50) ,
	@Data nvarchar(3600)  ,
	@Msg nvarchar(200) 

AS
BEGIN

        DECLARE @CurrentTable nvarchar(100); 
		SET @CurrentTable = 'MachineConnectionLog'+CONVERT(varchar(6),@CreatedOn,12);

	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;
	IF EXISTS (SELECT * FROM INFORMATION_SCHEMA.TABLES
           WHERE TABLE_NAME = @CurrentTable)
		BEGIN
			  PRINT 'Yes'
		END
	ELSE
		BEGIN
		DECLARE @DynamicSQL nvarchar(1000);
		
	
		SET @DynamicSQL=N'create table '+ @CurrentTable +' ('+'	[Id] [bigint] IDENTITY(1,1) NOT NULL,
	[ChargePointSerialNumber] [nvarchar](25) NULL,
	[MessageType] [nvarchar](50) NULL,
	[Data] [nvarchar](3600) NULL,
	[Msg] [nvarchar](200) NULL,
	[CreatedOn] [datetime] NOT NULL,
 CONSTRAINT [PK_dbo.'+ @CurrentTable +'] 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] TEXTIMAGE_ON [PRIMARY];';
		/*Adding braces is important as suggested by ypercube */
		exec (@DynamicSQL);
			PRINT 'NO'
		END
		 -- Insert statements for procedure here
		
		 DECLARE @sql nvarchar(4000)
		
		 SET @sql='INSERT INTO '+@CurrentTable+'
		 ([ChargePointSerialNumber]
           ,[MessageType]
           ,[Data]
           ,[Msg]
           ,[CreatedOn] )
     VALUES('''+@ChargePointSerialNumber+''','''+@MessageType+''','''+@Data+''','''+@Msg+''',N'+''''+ CONVERT(char(23), @CreatedOn, 121)+''');';
		  print(@sql);
		   exec (@sql);
		   
END

GO