ALTER PROCEDURE [dbo].[sp_GetPromise]
@From datetime = null,
@To datetime = null,
@CustID int = 0,
@agent NVARCHAR(50)=null,
@total int OUTPUT,
@totalAmt money OUTPUT
AS
BEGIN
DECLARE
@sql NVARCHAR(4000)=null,
@sqldate NVARCHAR(1000)=null,
@sqlAgent NVARCHAR(1000)=null,
@sqltotal NVARCHAR(1000)=null,
@sqltotalamt NVARCHAR(1000)=null
SET @sql = 'SELECT [ID]
,[CustomerID]
,[OrderID]
,[CName]
,[Name]
,[PDate]
,[AmountDue]
,[AmountPromised]
,[Method]
,[Ref]
,[Notes]
,[Agent]
,[datetime]
,[computer]
,[WritingAgent]
FROM [ype_new_old].[dbo].[Promise]
WHERE Del is null'
SET @sqltotal = N'SELECT
@total = COUNT(*) FROM [ype_new_old].[dbo].[Promise] WHERE Del is null'
SET @sqltotalamt = N'SELECT @totalamt = sum(AmountPromised) FROM [ype_new_old].[dbo].[Promise] WHERE Del is null'
IF (@CustID = 0)
BEGIN
if (@agent is not null)
SET @sqlAgent = ' AND [WritingAgent] = '+CHAR(39) + @agent+CHAR(39)
ELSE SET @sqlAgent = ' '
if (@From is not null)
SET @sqldate = ' AND PDate >= '+CHAR(39) + CAST(@From as VARCHAR(20)) +CHAR(39)
ELSE SET @sqldate = ' '
if (@To is not null)
SET @sqldate += ' AND PDate <= ' +CHAR(39)+ CAST(@To as VARCHAR(20))+CHAR(39)
SET @sql = @sql + @sqlAgent + @sqldate
SET @sqltotal = @sqltotal + @sqlAgent + @sqldate
SET @sqltotalamt = @sqltotalamt + @sqlAgent + @sqldate
END
ELSE
BEGIN
SET @sql = @sql + ' AND [CustomerID] = ' + CONVERT(varchar(9), @CustID)
SET @sqltotal = @sqltotal + ' AND [CustomerID] = ' + CONVERT(varchar(9), @CustID)
SET @sqltotalamt = @sqltotalamt + ' AND [CustomerID] = ' + CONVERT(varchar(9), @CustID)
END
EXEC sp_executesql @query0 = @sql
EXEC sp_executesql
@query = @sqltotal,
@params = N'
@total INT OUTPUT',
@total =
@total OUTPUT
EXEC sp_executesql
@query1 = @sqltotalamt,
@params1 = N'@totalamt money OUTPUT',
@totalamt = @totalamt OUTPUT
END