create PROCEDURE searchuserdetail
@EndUser INT,
@AdminUser INT
AS
DECLARE @ErrorMessage AS VARCHAR(200)
BEGIN
BEGIN
SELECT A.[userid] ,
A.[username] ,
B.[userrole],
B.[BILLING_GRP_CD] AS BillingGroupCd
FROM [dbo].USERDETAIL AS A
INNER JOIN A.USERROLE AS B
ON [A].[USERID] = [B].[USERID]
WHERE [A].[ACTIVE] = 1
AND [B].[ROLEID] IN ( @Enduser ,
@Adminuser )
END
this sp gets the user information from the two tables userrole and userdetail .
Now in this SP temporary table is created and data from the XML is inserted into the table using
SP_XML_PREPAREDOCUMENT
Temporary table has three column that are fieldName ,fieldvalue and operator.Blank Data table is returned and that sql query is stored in variable in order to use this dynamically . No of record from the temptable is counted and stored in a variable . Using while loop combination of field, value and oprator is applied to the where condition in sql Query
Column name
userid userid maps to the tablemasterid
username username maps to the table tablemasterid table
Status it is the status of the user that maps to from tablemasterid
tablemasterid has following coloumn
userid username useraddress Status
1 abcd address1 1
2 ghuj address2 0