Click here to Skip to main content
Rate this: bad
good
Please Sign up or sign in to vote.
See more: SQL-server-2005
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 27-Oct-12 9:19am
lalitkr1.5K
Edited 27-Oct-12 9:39am
(no name)498.5K
v2
Comments
Guruprasad.kb at 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
0 Mathew Soji 309
1 BillWoodruff 300
2 Afzaal Ahmad Zeeshan 252
3 DamithSL 225
4 Maciej Los 215
0 OriginalGriff 6,249
1 Sergey Alexandrovich Kryukov 5,853
2 DamithSL 5,183
3 Manas Bhardwaj 4,673
4 Maciej Los 3,865


Advertise | Privacy | Mobile
Web03 | 2.8.1411019.1 | Last Updated 27 Oct 2012
Copyright © CodeProject, 1999-2014
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