Hi

Did you think of partitioning your table into say 10 separate years?  That way you don't have to run the whole 10 year data?

Kind regards

Fred

Hi guys

Worked out a possible solution.  what do you think?  Any other, better ideas are welcome.

Kind regards

BEGIN TRANSACTION

INSERT INTO Clientdataset(Idnumber,FirstName,Surname,DateOfBirth,

FolderNumber,Gender)

VALUES('6906275293084', 'Robert', 'Sidney', '1968-09-11', 'RS01','Male')

DECLARE @IDNumber VARCHAR(13),

@Returned BIT;

SET @IDNumber = (SELECT...

Hi Scott

Thank you very much it's very helpful.  This is the however the actual problem.  I create a table and enter the following values:

CREATE TABLE Clientdataset (

Idnumber VARCHAR(13) DEFAULT NULL,

FirstName...

Hi Phil

The following proc only gets me the first two steps of the algorithm.  I'm stuck after that.

CREATE PROCEDURE [dbo].[ValidateSAIDNumber]

@IDNumber VARCHAR (13)

AS

BEGIN

DECLARE @a INT -- ~Holds Negative Index...

sorry the requirement was an attachment and I was ask to paste thr attachment info rather which I have now done.

thanks

Hi guys

I've actually just created a second table with the right column, movef the data over and used that table instead.

Thanks

Hi Grant

I have changed the Function as per your advice.  Do you perhaps have advice as per my original question posed please?

Kind regards

Thanks!  You're a star!

kind regards

CREATE PROCEDURE uspReturnEmployees

@departmentName VARCHAR = NULL

AS

BEGIN

SET NOCOUNT ON;

SELECT employeeNo

,lastName

,firstName

,gender

,IDNumber

,salaryLeveLID

,departmentID

FROM Employees E

INNER JOIN Department D

ON E.departmentID = D.departmentID

WHERE name = @departmentName

OR ISNULL(@departmentName, '') = ''

END

Yeah, I saw that, thanks.

Thanks for the advice. Really appreciate it.

Thanks!

Go figure - trying to learn something and you get lucky (with the results that is)

Yes, I think so. Checked the unicode check box in SSIS

This is some sample data and what it looks like in UltraEdit/Notebook++:

"1|EM|0||Ken|J|Sánchez||0||?<IndividualSurvey xmlns=""http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/IndividualSurvey""><TotalPurchaseYTD>0</TotalPurchaseYTD></IndividualSurvey>|92C4279F-1207-48A3-8448-4636514EB7E2|2009-01-07 00:00:00|"

"2|EM|0||Terri|Lee|Duffy||1||?<IndividualSurvey xmlns=""http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/IndividualSurvey""><TotalPurchaseYTD>0</TotalPurchaseYTD></IndividualSurvey>|D8763459-8AA8-47CC-AFF7-C9079AF79033|2008-01-24 00:00:00|"

"3|EM|0||Roberto||Tamburello||0||?<IndividualSurvey xmlns=""http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/IndividualSurvey""><TotalPurchaseYTD>0</TotalPurchaseYTD></IndividualSurvey>|E1A2555E-0828-434B-A33B-6F38136A37DE|2007-11-04 00:00:00|"

"4|EM|0||Rob||Walters||0||?<IndividualSurvey xmlns=""http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/IndividualSurvey""><TotalPurchaseYTD>0</TotalPurchaseYTD></IndividualSurvey>|F2D7CE06-38B3-4357-805B-F4B6B71C01FF|2007-11-28 00:00:00|"

"5|EM|0|Ms.|Gail|A|Erickson||0||?<IndividualSurvey xmlns=""http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/IndividualSurvey""><TotalPurchaseYTD>0</TotalPurchaseYTD></IndividualSurvey>|F3A3F6B4-AE3B-430C-A754-9F2231BA6FEF|2007-12-30 00:00:00|"

"6|EM|0|Mr.|Jossef|H|Goldberg||0||?<IndividualSurvey xmlns=""http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/IndividualSurvey""><TotalPurchaseYTD>0</TotalPurchaseYTD></IndividualSurvey>|0DEA28FD-EFFE-482A-AFD3-B7E8F199D56F|2013-12-16 00:00:00|"

"7|EM|0||Dylan|A|Miller||2||?<IndividualSurvey xmlns=""http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/IndividualSurvey""><TotalPurchaseYTD>0</TotalPurchaseYTD></IndividualSurvey>|C45E8AB8-01BE-4B76-B215-820C8368181A|2009-02-01 00:00:00|"

"8|EM|0||Diane|L|Margheim||0||?<IndividualSurvey xmlns=""http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/IndividualSurvey""><TotalPurchaseYTD>0</TotalPurchaseYTD></IndividualSurvey>|A948E590-4A56-45A9-BC9A-160A1CC9D990|2008-12-22 00:00:00|"

"9|EM|0||Gigi|N|Matthew||0||?<IndividualSurvey xmlns=""http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/IndividualSurvey""><TotalPurchaseYTD>0</TotalPurchaseYTD></IndividualSurvey>|5FC28C0E-6D36-4252-9846-05CAA0B1F6C5|2009-01-09 00:00:00|"

"10|EM|0||Michael||Raheem||2||?<IndividualSurvey...

Hi, thanks for replying.

LOL, Yes, I am re-reading a lot of Asterix books lately so...

Okay, I will open it up in notebook++ and see what happens.

Thanks.