Click here to Skip to main content
15,867,686 members
Please Sign up or sign in to vote.
4.00/5 (1 vote)
SQL
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
Posted
Updated 27-Oct-12 8:39am
v2
Comments
Guruprasad.K.Basavaraju 8-Apr-14 15:45pm    
What exactly is the question here ?

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900