Here is the stored procedure that validates the student credentials. I will be posting the UDF's shortly, once I clean them up to look nice.

SET QUOTED_IDENTIFIER ON

GO

SET ANSI_NULLS ON

GO

CREATE PROC spa_SomeSproc

@ListID INT, --List ID where the student is located

@ItemID INT, --item that belongs to the list

@LName VARCHAR(255), --last name of student

@StudentID VARCHAR(75), --student id

@StudentID2 VARCHAR(75),

@SiteID VARCHAR(20)

AS

SET NOCOUNT ON

DECLARE @cnt INT, @zeroTrimExtraID INT, @zeroTrimPIN INT

create table #Students(

contactsExtraID INT,

FirstName VARCHAR(255),

LastName VARCHAR(255),

StudentBalance DECIMAL(19,4),

DateAdjusted datetime,

StudentID VARCHAR(75),

StudentID2 VARCHAR(75),

BuildingID VARCHAR(20),

Meal1Balance DECIMAL(19,2),

Meal2Balance DECIMAL(19,2),

StudentGrade VARCHAR(20),

PayStatus VARCHAR(20)

)

-------------------------------------------------------------------------------------------------

--if the studentid or the studentid2 needs trailing zero processing, do it

-------------------------------------------------------------------------------------------------

SELECT

@zeroTrimStudentID = b.zeroTrimStudentID,

@zeroTrimStudentID2 = b.zeroTrimStudentID2

FROM

tblInventory a,

tblExportSystem b

WHERE

a.ExportSystemID = b.id

AND a.itemid = @itemid

IF ISNUMERIC(@StudentID) = 0

SET @zeroTrimStudentID = 0

IF ISNUMERIC(@StudentID2) = 0

SET @zeroTrimStudentID2 = 0

IF @StudentID <> '' AND @zeroTrimStudentID=1

BEGIN

SET @StudentID = REPLACE(@StudentID, '0', ' ')

SET @StudentID = LTRIM(@StudentID)

SET @StudentID = REPLACE(@StudentID, ' ', '0')

END

IF @StudentID2 <> '' AND @zeroTrimStudentID2 = 1

BEGIN

SET @StudentID2 = REPLACE(@StudentID2, '0', ' ')

SET @StudentID2 = LTRIM(@StudentID2)

SET @StudentID2 = REPLACE(@StudentID2, ' ', '0')

END

-------------------------------------------------------------------------------------------------

-- This following SQL assumes that if the validations weren't entered (therefore blank) then they

-- weren't expected. Therefore, we need to do the checks with the validation bit values passed

-------------------------------------------------------------------------------------------------

INSERT INTO #Students

SELECT * FROM

(

SELECT

StudentInfoID,

FirstName,

sLNameEncoded,

StudentBalance,

DateAdjusted,

StudentID =

CASE WHEN @zeroTrimStudentID = 1 THEN

CASE WHEN ISNUMERIC(StudentID) = 1 THEN CAST(CONVERT(BIGINT, StudentID) AS VARCHAR)

ELSE StudentID

END

ELSE StudentID

END,

StudentID2 =

CASE WHEN @zeroTrimPIN=1 THEN

CASE WHEN isnumeric(StudentID2)=1 THEN CAST(convert(bigint, StudentID2) AS VARCHAR)

ELSE StudentID2

END

ELSE StudentID2

END,

BuildingID,

Meal1Balance,

Meal2Balance,

StudentGrade,

PayStatus

FROM

qStudentIDContacts xtra --this is the view which accesses the table that has the computed column

WHERE

xtra.ListID = @ListID

AND (( @LastName <> '' AND xtra.sLNameEncoded=@LastName) OR @LastName='')

AND (( @BuildingID <> '' AND xtra.BuildingID=@BuildingID) OR @BuildingID='')

) final

WHERE

(( @StudentID <> '' AND dbo.fnHTMLEncode(dbo.fnHTMLDecode(final.StudentID))=@StudentID) OR @StudentID='')

AND (( @StudentID2 <> '' AND dbo.fnHTMLEncode(dbo.fnHTMLDecode(final.StudentID2))=@StudentID2) OR @StudentID2='')

SET @cnt = @@rowcount

-------------------------------------------------------------------------------------------------

-- Check to see if the sample information was used if no rows were returned.

-------------------------------------------------------------------------------------------------

IF @cnt = 0

IF (

(@LastName='TestLastName' OR @LastName='')

AND (@StudentID='TestStudentID' OR @StudentID='TestStudentID+' OR @StudentID='TestStudentID-' OR @StudentID='' )

AND (@StudentID2='TestID2' OR @StudentID2='TestID2+' OR @StudentID2='TestID2-' OR @StudentID2='')

)

BEGIN

INSERT INTO #Students

SELECT

StudentInfoID = -1,

FirstName = 'Jimminy',

LastName = 'Cricket',

StudentBalance = ROUND(RAND() * 20000 / 100,2) *

CASE WHEN @StudentID = 'TestStudentID-' OR @StudentID2 = 'TestID2-'

THEN -1

ELSE 1

END, --generates random # between $0.00 and $20.00

DateAdjusted = GETDATE(),

StudentID = @StudentID,

StudentID2 = @StudentID2,

BuildingID = '',

Meal1Balance = ROUND(RAND() * 20000 / 100,0)*

CASE WHEN @StudentID = 'TestStudentID-' OR @StudentID2 = 'TestID2-'

THEN -1

ELSE 1

END, --generates random # between 0 and 200

Meal2Balance = ROUND(RAND() * 20000 / 100,0)*

CASE WHEN @StudentID = 'TestStudentID-' OR @StudentID2 = 'TestID2-'

THEN -1

ELSE 1

END, --generates random # between 0 and 200

StudentGrade = '03',

PayStatus = 'R'

SET @cnt = 1

END

SELECT * FROM #Students

-------------------------------------------------------------------------------------------------

-- Checks: If the validation bit is set and nothing was passed, this zeros so the sum will not

-- be 3. If the sum is 3 (everything was passed), the return the count of what was returned

-- above. The application logic will reject anything that != 1.

-------------------------------------------------------------------------------------------------

SELECT

CASE WHEN

(CASE WHEN LastNameValidation=1 AND @LastName = '' THEN 0

ELSE 1

END

+

CASE WHEN StudentIDValidation=1 AND @StudentID = '' THEN 0

ELSE 1

END

+

CASE WHEN StudentID2Validation=1 AND @StudentID2 = '' THEN 0

ELSE 1

END) = 3

THEN @cnt

ELSE 0

END validCount

FROM

tblInventory i

WHERE

itemid = @itemid

SET NOCOUNT OFF

GO

SET QUOTED_IDENTIFIER OFF

GO

SET ANSI_NULLS ON

GO