EXECUTE sp_executesql userProvisining 'ADS',NULL,'001','2012-01-01','2012-01-30',NULL,NULL,NULL,NULL;


SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:		<Author,,Name>
-- Create date: <Create Date,,>
-- Description:	<Description,,>
-- =============================================
ALTER PROCEDURE userProvisining 
	-- Add the parameters for the stored procedure here
	 @TargetSystem nvarchar(35) = NULL,
	 @DepartmentCode nvarchar(35) = NULL,
	 @ProfitCenterCode nvarchar(35) =NULL ,
	 @sDate datetime = NULL,
	 @eDate datetime = NULL,
	 @personelNumber nvarchar(32) = NULL ,
	 @firstName nvarchar(32) = NULL,
	 @lastName nvarchar(32) = NULL,
	 @userName nvarchar(32) = NULL
AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	--SET NOCOUNT ON;

    -- Insert statements for procedure here
	SELECT p.Firstname,p.Lastname,p.PersonnelNumber,d.DepartmentName,pc.ShortName as ProfitCenter,u.ManageLevel, u.XProxyContext as SystemName,
		 OldValue = (case when dw.[OldValue] ='0' then 'Active' else 'Inactive' end),
		dwo.operationDate, OperationType = (case when dwo.OperationType ='U' then 'Updated' when dwo.OperationType ='D' then 'Deleted' else 'Inserted' end),AffectedColumn = case when dbc.caption is null then dbc.ColumnName else dbc.caption end 
	FROM DialogWatchProperty dw
		INNER JOIN DialogWatchOperation dwo ON dw.UID_DialogWatchOperation = dwo.UID_DialogWatchOperation
		INNER JOIN UNSAccountB u ON dwo.ObjectKey = u.xObjectKey
		INNER JOIN Person p ON u.uid_person = p.uid_person
		INNER JOIN Department d ON p.UID_Department = d.UID_Department
		INNER JOIN ProfitCenter pc ON p.UID_ProfitCenter = pc.UID_ProfitCenter
		---INNER JOIN ProfitCenter pc_ ON d.UID_ProfitCenter = pc_.UID_ProfitCenter
		INNER JOIN DialogColumn dbc on  dbc.UID_DialogColumn = dw.UID_DialogColumn
	where --OperationType = 'U'  
		(dwo.operationDate >= @sDate and dwo.operationDate <= @eDate)
		And u.XProxyContext = @TargetSystem 
		---And dbc.ColumnName in ('AccountDisabled')
		And dbc.ColumnName ='AccountDisabled'
		And p.FirstName = (case when @FirstName <>'' then @FirstName else p.FirstName end)
		And p.LastName = (case when @LastName <>'' then @LastName else p.LastName end)
		And p.CentralAccount = 
		(case when @userName <>'' then @userName else p.CentralAccount end)
		And PersonnelNumber = (case when @personelNumber <>'' then @personelNumber else PersonnelNumber end)
		And d.ObjectID = 
		(case when @DepartmentCode <>'' then @DepartmentCode else d.ObjectID end)
		And pc.AccountNumber = 
		(case when @ProfitCenterCode <>'' then @ProfitCenterCode else pc.AccountNumber end)
	order by p.firstname,p.lastname,AffectedColumn asc, dwo.operationDate desc	
	
	---return 0;		
END
GO

quote:

---

Originally posted by nigelrivett

try
EXECUTE userProvisining 'ADS',NULL,'001','2012-01-01','2012-01-30',NULL,NULL,NULL,NULL;


Probably should be userProvisioning but that's not important for running it.


==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy.

---