ALTER PROCEDURE [dbo].[ProductionData]
(@Year nVarChar(4),@Point nVarChar(100))
AS
SELECT Date,Point,
(CASE WHEN pH = NULL THEN 0 ELSE pH END)AS pH,
(CASE WHEN SS = NULL THEN 0 ELSE SS END)AS SS,
(CASE WHEN TDS = NULL THEN 0 ELSE TDS END)AS TDS,
(CASE WHEN COD = NULL THEN 0 ELSE COD END)AS COD,
(CASE WHEN BOD = NULL THEN 0 ELSE BOD END)AS BOD,
(CASE WHEN Flow = NULL THEN 0 ELSE Flow END)AS Flow
FROM EnvProductionData
WHERE YEAR(Date) = @Year AND Point in (@Point)
ORDER BY Date
Tag : .NET, Ms SQL Server 2008, Web (ASP.NET), VB.NET
CREATE VIEW [dbo].[VW_ProductionData]
AS
SELECT Date,Point,
(CASE WHEN pH = NULL THEN 0 ELSE pH END)AS pH,
(CASE WHEN SS = NULL THEN 0 ELSE SS END)AS SS,
(CASE WHEN TDS = NULL THEN 0 ELSE TDS END)AS TDS,
(CASE WHEN COD = NULL THEN 0 ELSE COD END)AS COD,
(CASE WHEN BOD = NULL THEN 0 ELSE BOD END)AS BOD,
(CASE WHEN Flow = NULL THEN 0 ELSE Flow END)AS Flow
FROM EnvProductionData
USE [JimThaiDB]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[ProductionData]
@Point AS NVARCHAR(max) = ''
AS
BEGIN
SET NOCOUNT ON;
DECLARE @strPrepare AS NVARCHAR(MAX) = '';
SET @strPrepare = 'SELECT * FROM EnvProductionData WHERE Point IN(' + @Point + ')';
EXEC sp_executesql @strPrepare;
END
Call StroreProcedure
Code
USE [JimThaiDB]
GO
DECLARE @return_value int
EXEC @return_value = [dbo].[ProductionData]
@point = N'1,2,3,4'
SELECT 'Return Value' = @return_value
GO
Load balance : Server 01
