Stored proc with update loop
-
I need some help with the proc.
This is what i am trying to do.
I am getting the domain name from the email. Then i am using that domian name to get all the users form the user table. If any users are returned i need to grab the userid from the users table and update the useroles table. Every userid has 3 rows in the useroles table. I need to update all the 3 rows.
This is how ia m getting the domain name form the email.
SELECT SUBSTRING(loc_bus_email,
NULLIF(CHARINDEX('@', loc_bus_email), 0) + 1,
LEN(loc_bus_email) - CHARINDEX('@', loc_bus_email) + 1)
AS usersemail
FROM LOCATION INNER JOIN PARTNER ON LOCATION.part_id = PARTNER.part_id where PARTNER.part_id =@ID
I need to pass usersemail to get all the users which is not working
SELECT * FROM users where USERNAME LIKE usersemail
If any users are returned i need to grab the userid from the users table and update the useroles table. Every userid has 3 rows in the useroles table. I need to update all the 3 rows.
Any 1 can help
-
It is not clear what you are trying to achieve.
You will need to post:
1. the DDL (CREATE TABLE statements) for the LOCATION, USERS a USERROLES tables.
2. some sample data for each table.
3. what you want the sample data in USERROLES to look like after the update.
-
This is the SQl i am geting the domian from an email address. This sql is only used to get the domain from an email address
Declare @usersemail varchar(50)
SELECT @usersemail=(SUBSTRING(loc_bus_email,NULLIF(CHARINDEX('@', loc_bus_email), 0) + 1,LEN(loc_bus_email) - CHARINDEX('@', loc_bus_email) + 1))
FROM LOCATION INNER JOIN PARTNER ON LOCATION.part_id = '5' where PARTNER.part_id ='5'
select @usersemail
This is the USERS Table from where i am going to get the USERID using this SQL . If there are any rows then it should do the update.
SELECT * FROM users where USERNAME like ' + @usersemail + '
CREATE TABLE [Users] (
[UserID] [int] IDENTITY (1, 1) NOT NULL ,
[Username] [nvarchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[FirstName] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[LastName] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[IsSuperUser] [bit] NOT NULL CONSTRAINT [DF_Users_IsSuperUser] DEFAULT (0),
[AffiliateId] [int] NULL ,
[Email] [nvarchar] (256) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[DisplayName] [nvarchar] (128) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL CONSTRAINT [DF_Users_DisplayName] DEFAULT (''),
[UpdatePassword] [bit] NOT NULL CONSTRAINT [DF_Users_UpdatePassword] DEFAULT (0),
CONSTRAINT [PK_Users] PRIMARY KEY CLUSTERED
(
[UserID]
 
ON [PRIMARY] ,CONSTRAINT [IX_Users] UNIQUE NONCLUSTERED
(
[Username]
 
ON [PRIMARY]) ON [PRIMARY]
GO
This is the USERROLES table which i am gonna update the rows by geting the USERID from the USERS Table. I am just updating the RoleId in this table for a user. Role id is gonna be hardcoded in the update statement. If they are silver then the roldid is 7 if gold then its 9. Every USERID has 3 rows with differnet roleid.
CREATE TABLE [UserRoles] (
[UserRoleID] [int] IDENTITY (1, 1) NOT NULL ,
[UserID] [int] NOT NULL ,
[RoleID] [int] NOT NULL ,
[ExpiryDate] [datetime] NULL ,
[IsTrialUsed] [bit] NULL ,
[EffectiveDate] [datetime] NULL ,
CONSTRAINT [PK_UserRoles] PRIMARY KEY CLUSTERED
(
[UserRoleID]
 
ON [PRIMARY] ,CONSTRAINT [FK_UserRoles_Roles] FOREIGN KEY
(
[RoleID]
 
REFERENCES [Roles] ([RoleID]
 
ON DELETE CASCADE NOT FOR REPLICATION ,CONSTRAINT [FK_UserRoles_Users] FOREIGN KEY
(
[UserID]
 
REFERENCES [Users] ([UserID]
 
ON DELETE CASCADE NOT FOR REPLICATION) ON [PRIMARY]
GO
-
Still do not really have enough information, but something like this should get you started:
UPDATE R
-- This will set all the users RoleID to 7
SET RoleID = 7
-- If there is a condition to control what the RoleID should be set to then use a CASE. eg
-- SET RoleID = CASE <SomeCond> WHEN 'Silver' THEN 7 ELSE 9 END
FROM Users U
JOIN Location L ON L.part_is = @ID
AND L.UserName LIKE '%' + SUBSTRING(L.loc_bus_email, CHARINDEX('@', L.loc_bus_email) + 1, 8000) +'%'
-- Maybe this should be EMail not UserName
-- JOIN Location L ON L.EMail like '%' + SUBSTRING(L.loc_bus_email, CHARINDEX('@', L.loc_bus_email) + 1, 4000) +'%'
JOIN UserRoles R ON U.UserID = R.UserID
-
To simply answer your question, you do not have any wildcard character in your Like pattern. You probably just need to make your query:
SELECT * FROM users where USERNAME LIKE '%' + usersemail
Hope this helps.
Mark -
Thanks i got till here. When i run select @userid, @username in my stored proc i get 3 records example
userd=1 username - king@yahoo.com
userid=2 username -= king2@yahoo.com
Userid=3 username = kang4@yahoo.com
Next i need to go into the UserRole table and update the ROLEID with the userid that i got from the users table. There are 3 rows for every userid in the USERole table. I need to update all the 3 rows with the roleid. RoleId 1 and 2 will remain the same but the thrid one will chnage according to the status.
I need to know how in my stored proc i should do this?
example for userid =1 in the userroles table
userroleid=1
Userid=1
RoleID=1
expirydate = null
idtrialused= null
effective date = null
userroleid=2
Userid=1
RoleID=2
expirydate = null
idtrialused= null
effective date = null
userroleid=3
Userid=1
RoleID=3
expirydate = null
idtrialused= null
effective date = null
It is same for the other usersid 2 and 3
MY STORED PROC
Declare @usersemail varchar(50), @userid varchar(50), @username varchar(50)
SELECT @usersemail='%'+ (SUBSTRING(loc_bus_email,NULLIF(CHARINDEX('@', loc_bus_email), 0) + 1,LEN(loc_bus_email) - CHARINDEX('@', loc_bus_email) + 1))
FROM LOCATION INNER JOIN PARTNER ON LOCATION.part_id = '5' where PARTNER.part_id ='5'
select @usersemail
DECLARE contact_cursor CURSOR FOR
SELECT userid, username FROM users where USERNAME like @usersemail
OPEN contact_cursor
FETCH NEXT FROM contact_cursor
INTO @userid, @username
WHILE @@FETCH_STATUS = 0
BEGIN
select @userid, @username
FETCH NEXT FROM contact_cursor
INTO @userid, @username
END
CLOSE contact_cursor
DEALLOCATE contact_cursor
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply