quote:

---

Originally posted by Kristen

Madhi, you're taking things too seriously. It was a joke ...

---

quote:

---

These demopgraphics are not in a dataset

---

quote:

---

Originally posted by Billkamm

Here is a code listing of what I am trying to do with the UPDATE half of the problem. If you notice towards the bottom I created a check to update and add to the usage log. It seems silly that I would now have to repeat that large block of code for every demographic

http://www.PasteHere.com/?rbsmkl

In the near future I will need create a similiar stored procedure that will also allow me to INSERT fields that don't exist. This current example is all UPDATE.

---

-- Will update all the demographics fields in tblDemographics
CREATE PROCEDURE dbo.usp_UpdateDemographicsTable
(
	-- The user ID is required, so it can be added to the usage log
	@pkUserID			int,

	-- Use the application number or broker ID to lookup the demo ID
	@AppNumber			int,
	@BrokerID			int,
	
    -- Pass in all the demographics details
    @BrokerName         varchar(100),
    @BrokerDbaNames     varchar(100),
    @PrimaryContact     varchar(20),
    @PhoneNumber        varchar(25),
    @FaxNumber          varchar(25),
    @EmailAddress       varchar(255),
    @Address            varchar(250),
    @City               varchar(50),
    @fkStateId          int,
    @Zip                char(5),
    @fkTerritoryId      int,
    @fkAssignAcctExecID int,
    @Website            varchar(255),
    @TaxIdNumber        varchar(11)
)
AS

SET NOCOUNT ON

BEGIN TRANSACTION;

-- Used for parsing all the column names during the update
DECLARE @column_list AS varchar(250);
DECLARE @parsing_list AS varchar(250);
DECLARE @current_column AS varchar(25);
DECLARE @find_comma AS int;

-- Createa list of all the demographics fields
SELECT @column_list = "BrokerName, BrokerDbaNames, PrimaryContact, PhoneNumber, "
	 + "FaxNumber, EmailAddress, Address, City, fkStateId, Zip, fkTerritoryId, "
	 + "fkAssignAcctExecID, Website, TaxIdNumber";

-- Pull the demographics ID for this application or broker profile
DECLARE @demoID As int;

IF @AppNumber IS NOT NULL
BEGIN
	SELECT @demoID = fkDemoID FROM tblApplications WHERE pkApplicationNumber = @AppNumber;
END
ELSE IF @BrokerID IS NOT NULL
BEGIN
	SELECT @demoID = fkDemoID FROM tblBrokers WHERE pkBrokerID = @BrokerID;
END
ELSE
BEGIN	
	RAISERROR 50000 'No Application number or Broker Profile Number was specified';
	RETURN;
END

-- Loop through all the column names
SELECT @parsing_list = @column_list;

WHILE @parsing_list IS NOT NULL
BEGIN
	-- Retrieve the next column name
	SELECT @find_comma = PATINDEX('%,%', @parsing_list);
	IF @find_comma <> 0
	BEGIN
		-- Pull the column from the list
		SELECT @current_column = SUBSTRING(@parsing_list, 1,(@find_comma - 1));
		
		-- Remove the column from the list
		SELECT @parsing_list = dbo.Trim(SUBSTRING(@parsing_list, (@find_comma + 1), 250));
	END
	ELSE
	BEGIN
		SELECT @current_column = @parsing_list;
		SELECT @parsing_list = NULL;
	END

	-- If the demographics entry has changed update it in the table and add an entry to the usage log
	EXEC('
		IF @' + @current_column + ' <> (SELECT ' + @current_column + ' FROM tblDemographics WHERE pkDemoId = @demoID)
		BEGIN
			-- Add an entry to the usage log for either the application or broker ID
			INSERT INTO tblUsageLog
			(
				fkApplicationNumber,
				fkBrokerID,
				DateTimeOfAction,
				fkUserID,
				Description
			)
			Values
			(
				@AppNumber,
				@BrokerID,
				GetDate(),
				@pkUserID,
				''Changed ' + @current_column + ' from '' + (SELECT ' + @current_column + ' FROM tblDemographics WHERE pkDemoId = @demoID) + '' to '' + @' + @current_column + '
			);
			
			-- Update the values into the table
			UPDATE tblDemographics
			SET
				' + @current_column + ' = @' + @current_column + ',
			WHERE pkDemoId = @demoID;
		END
	')
END

-- Check for errors before committing the transaction
IF @@ERROR <> 0
BEGIN
	ROLLBACK TRANSACTION;
	RETURN -1;
END
ELSE
BEGIN
	COMMIT TRANSACTION
END

GO

-- Get current values
select
	@cur_BrokerName		= BrokerName,
	@cur_BrokerDbaNames	= BrokerDbaNames
	... and so on...
from
	tblDemographics
where
	pkDemoId = @demoID


-- update the row
update a
set
	BrokerName = 
		case
		when	@BrokerName is not null and
			@BrokerName <> a.BrokerName
		then 	@BrokerName
		else	a.BrokerName
		end,
	BrokerDbaNames = 
		case
		when	@BrokerDbaNames is not null and
			@BrokerDbaNames <> a.BrokerDbaNames
		then 	@BrokerDbaNames
		else	a.BrokerDbaNames
		end,
	... and so on...
from
	tblDemographics a
where
	a.pkDemoId = @demoID


-- Log changes
INSERT INTO tblUsageLog
	(
	fkApplicationNumber,
	fkBrokerID,
	DateTimeOfAction,
	fkUserID,
	Description
	)
select
	@AppNumber,
	@BrokerID,
	GetDate(),
	@pkUserID,
	a.Desc
from
	(
	select Desc =
		'Changed BrokerName from '+
		@cur_BrokerName+' to '+
		@BrokerName
	where
		@BrokerName is not null and
		@BrokerName <> @cur_BrokerName
	union all
	select Desc =
		'Changed BrokerDbaNames from '+
		@cur_BrokerDbaNames+' to '+
		@BrokerDbaNames
	where
		@BrokerDbaNames is not null and
		@BrokerDbaNames <> @cur_BrokerDbaNames
	union all
	... and so on...

	) a

IF @AppNumber IS NOT NULL
BEGIN
	SELECT @demoID = fkDemoID FROM tblApplications WHERE pkApplicationNumber = @AppNumber;
END
ELSE IF @BrokerID IS NOT NULL
BEGIN
	SELECT @demoID = fkDemoID FROM tblBrokers WHERE pkBrokerID = @BrokerID;
END
ELSE
BEGIN	
	RAISERROR 50000 'No Application number or Broker Profile Number was specified';
	RETURN;
END

IF @AppNumber IS NOT NULL
BEGIN
SELECT @demoID = fkDemoID
FROM	dbo.tblApplications
WHERE	    (@AppNumber IS NULL OR pkApplicationNumber = @AppNumber)
	AND (@BrokerID IS NULL OR pkBrokerID = @BrokerID)
IF @@ROWCOUNT = 0 -- Bit naughty, this is "No rows found" rather than "No parameters provided" !!
BEGIN	
	RAISERROR 50000 'No Application number or Broker Profile Number was specified';
	RETURN;
END

UPDATE D
SET
	[MyColumn1] = S.[MyColumn1],
	[MyStringColumn2] = S.[MyStringColumn2],
	...
FROM	dbo.MyDestinationTable AS D
	JOIN dbo.MySourceTable AS S
		 ON D.[MyPKColumn] = S.[MyPKColumn]

WHERE
	(
		   (D.[MyColumn1] <> S.[MyColumn1]
			OR (D.[MyColumn1] IS NULL AND S.[MyColumn1] IS NOT NULL)
			OR (D.[MyColumn1] IS NOT NULL AND S.[MyColumn1] IS NULL))
		OR (D.[MyStringColumn2] COLLATE Latin1_General_BIN <> S.[MyStringColumn2]
			OR (D.[MyStringColumn2] IS NULL AND S.[MyStringColumn2] IS NOT NULL)
			OR (D.[MyStringColumn2] IS NOT NULL AND S.[MyStringColumn2] IS NULL))
	)

INSERT INTO dbo.MyDestinationTable
(
	[MyPKColumn],
	[MyColumn1],
	[MyStringColumn2],
	...
)
SELECT	
	[MyPKColumn] = MyPKColumn,
	[MyColumn1] = MyColumn1,
	[MyStringColumn2] = MyStringColumn2,
	...
FROM	dbo.MySourceTable S
WHERE	NOT EXISTS
	(
	SELECT	*
	FROM	dbo.MyDestinationTable D
	WHERE
		    D.[MyPKColumn] = S.[MyPKColumn]
	)
ORDER BY S.MyPKColumn