Click here to Skip to main content
13,252,362 members (60,090 online)
Rate this:
Please Sign up or sign in to vote.
create PROCEDURE searchuserdetail
      @EndUser INT,
      @AdminUser INT
      DECLARE @ErrorMessage AS VARCHAR(200)
                  SELECT    A.[userid]   ,
                            A.[username] ,
			    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 )				  

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 27-Oct-12 9:19am
Updated 27-Oct-12 9:39am
Guruprasad.kb 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)

  Print Answers RSS
Top Experts
Last 24hrsThis month

Advertise | Privacy |
Web04 | 2.8.171114.1 | Last Updated 27 Oct 2012
Copyright © CodeProject, 1999-2017
All Rights Reserved. Terms of Service
Layout: fixed | fluid

CodeProject, 503-250 Ferrand Drive Toronto Ontario, M3C 3G8 Canada +1 416-849-8900 x 100