Use IP address as validation in webservice with stored procedure

Hi,

I have a stored procedure where I ask for 2 input parameters for the query to execute. It is going to used in connection with a web service, and I would like to improve the security.
What I want:

When the web service is called I would like to request the 2 input parameters and also retrieve the ip address. The IP address should then match the column IP Address in my Supplier_table.

Maybe use : ConnectionProperty('client_net_address')

What do I need to change in my query to get this to work?


My tables:

Customer_table:
- CustomerNo varchar(50)
- ProductionDate datetime
- DiscontinuedDate datetime
- StopProductionDate datetime

tmtB_table:
Park_GSRN varchar(50)
SupplierID int
StartDate datetime
StopDate datetime

Supplier_table:
- ID int
- supplierGUID uniqueidentifier
- IP_Address varchar(50)


Here is my SP so far:

 

GO
/****** Object:  StoredProcedure [dbo].[CustomerWebServiceGetByDate6]    Script Date: 05/07/2011 22:09:22 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER procedure [dbo].[CustomerWebServiceGetByDate6]
	@InputGUID as uniqueidentifier,
	@InputDate as datetime = '1753-01-01 00:00:000'
as
                                            
if(@InputDate is null) set @InputDate = GETDATE()	--this will handle the if statement
set @InputDate = convert(datetime, floor(convert(float, @InputDate)))	-- and this will truncate the time portion of the datetime


declare @maxStop datetime
select @maxStop = MAX(StopDate) from tmtB WHERE StopDate <= @InputDate



CREATE TABLE #tmtBalance
(Park_GSRN varchar(50))


;with CTE 
as
(
	
	SELECT
	Park_GSRN,
	ProductionDate,
	DiscontinuedDate,
	StopProductionDate,
	StartDate,
	StopDate,
	SupplierID,
	ROW_NUMBER() over (partition by Park_GSRN order by StopDate desc) RN 
	FROM (Customer LEFT JOIN tmtB ON Customer.CustomerNo = tmtB.Park_GSRN) LEFT JOIN Vendor ON tmtB.SupplierID=supplier.id
	where ((ProductionDate <= @InputDate and ProductionDate IS NOT NULL)
						and 
                        (StopProductionDate IS NULL or StopProductionDate >= @InputDate)
                        and
                        (DiscontinuedDate >= @InputDate or DiscontinuedDate IS NULL)
                        and
	Startdate <= @InputDate and supplierGUID = @InputGUID
	and (StopDate >= @InputDate or StopDate is null or 
	@InputDate >= @maxStop --entire subquery replaced here
))
)
INSERT INTO #tmtBalance
select Park_GSRN from CTE A
where A.RN = 1
order by A.Park_GSRN

DROP TABLE #tmtBalance

Select allOpen in new window