DECLARE @LName --HTML encoded last name as input parameter from user

SELECT * FROM
      (SELECT LName
      FROM SomeView xtra
      WHERE  (( @LName <> '' AND dbo.EncodingFunction(dbo.DecodingFunction(xtra.LName))=@LName) OR @Lname=''))

[LNameComputedColumn] AS (dbo.EncodingFunction(dbo.DecodingFunction([LName])))

DECLARE @LName --HTML encoded last name as input parameter from user

SELECT * FROM
      (SELECT LNameComputedColumn
      FROM SomeView xtra
      WHERE  (( @LName <> '' AND xtra.LNameComputedColumn=@LName) OR @Lname='')

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

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO
  
CREATE FUNCTION dbo.fnHTMLDecode
(
  @String varchar(8000)
)
RETURNS varchar(8000)

BEGIN

DECLARE @HTMLDecodedString varchar(8000)
SELECT @HTMLDecodedString = ISNULL(@String, '')
DECLARE @ix int, @iy int, @pos int

SET @pos = 1 -- start at front of string

SET @ix = CHARINDEX('&#', SUBSTRING(@HTMLDecodedString, @pos, LEN(@HTMLDecodedString))) -- search for first occurance of encoding delimiter '&#'
WHILE @ix > 0 -- if one is found
BEGIN
  
  SET @iy = CHARINDEX(';', SUBSTRING(@HTMLDecodedString, @pos + @ix + 1, LEN(@HTMLDecodedString))) -- find the encoding terminator ';'
  
  IF @iy IN (2,3,4) -- iy is one larger than the number to be decoded
  BEGIN

    -- replace all occurrences of '&#<nnn>;' with the char equivalent
    SET @HTMLDecodedString = REPLACE(@HTMLDecodedString,  SUBSTRING(@HTMLDecodedString, @pos+@ix-1, @iy+2), CHAR(CAST(SUBSTRING(@HTMLDecodedString, @pos+@ix+1, @iy-1) as int))  )
    
    -- move position to one past first replacement
    SET @pos = @pos + @ix
  END
  ELSE
  BEGIN
    
    -- If the encoded number is too large, don't decode it; just leave it be and move position one past the front delimiter
    SET @pos = @pos + @ix + 1
  END
  
  -- search for next matching encoding delimiter
  set @ix = CHARINDEX('&#', SUBSTRING(@HTMLDecodedString, @pos, LEN(@HTMLDecodedString)))  
END

RETURN @HTMLDecodedString
END
  
GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO 
 

  

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO

CREATE FUNCTION [dbo].[fnHTMLEncode]
(
  @String varchar(8000)
)
RETURNS varchar(8000)

BEGIN

SET @String = ISNULL(@String, '')

DECLARE @HTMLEncodedString varchar(8000)
SELECT @HTMLEncodedString = ''

SELECT @HTMLEncodedString = @HTMLEncodedString +
  CASE 
    WHEN theChar LIKE '[A-Za-z0-9,._ ]' 
    THEN theChar
    ELSE '&#' + CAST(ASCII(theChar) AS varchar(3)) + ';'
  END
FROM
(
SELECT theChar = SUBSTRING(@string, lNumber, 1) 
FROM tblNumbers
WHERE lNumber <= LEN(@String) ) CharacterArray

-- Return the result of the function

RETURN @HTMLEncodedString
END
 
GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO