Stored proc update - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Stored proc update

Author

Topic

soorma
Yak Posting Veteran

52 Posts

Posted - 2006-10-13 : 16:01:05

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 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

snSQL
Master Smack Fu Yak Hacker

1837 Posts

Posted - 2006-10-13 : 16:31:30

I think this is what you want, you didn't say how you will decide on the RoleId, so I just put in one of the values you gave. This will query all UserRoles rows that match to a Users row which matches the filetr and then update the UserRoles that match.

UPDATE [UserRoles]
SET RoleId = 5
FROM
[UserRoles] 
INNER JOIN users ON users.UserID = [UserRoles].UserID
WHERE USERNAME like '%' + @usersemail

soorma
Yak Posting Veteran

52 Posts

Posted - 2006-10-13 : 18:37:14

The RoleID will depend upon gold , silver or platinum. And in the userorles table there are 3 rows for everyuserid. I need to update all the rows. Actually one row only. ROLEID 1 and 2 will always remain the same. I have to chnage the 3 row with role id. I don't know how can i update all the 3 rows or the 3 rd row with the roleid.
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

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2006-10-14 : 00:49:24

What is Gold, Silver or Platinum? Which column contains these values?

UPDATE [UserRoles]
SET RoleId = Case
                when SomeCol = 'Silver' then 7
                when SomeCol = 'Gold' then 9
             end
FROM
[UserRoles] 
INNER JOIN users ON users.UserID = [UserRoles].UserID
WHERE USERNAME like '%' + @usersemail

Harsh Athalye
India.
"Nothing is Impossible"

soorma
Yak Posting Veteran

52 Posts

Posted - 2006-10-16 : 13:45:53

This is updatiing all the rows(roleid) to gold silver or platinum values in the userroles table. There are 3 rows for every userid with the roleid of 1 and 2 and the third row is the gold, platinim or silver. The roleid 1 and 2 always remain the same. I need to update only the 3 roleid

Like this

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

Declare @usersemail varchar(50)
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

UPDATE [UserRoles]
SET RoleId = Case
when @Status = 'Silver' then 8
when @Status = 'Gold' then 4
when @Status = 'Platinum' then 3
when @Status = 'Stagnant' then 9
end
FROM
[UserRoles]
INNER JOIN users ON users.UserID = [UserRoles].UserID
WHERE USERNAME like '%' + @usersemail

snSQL
Master Smack Fu Yak Hacker

1837 Posts

Posted - 2006-10-16 : 14:09:34

Just add the RoleId filter to the WHERE clause, like this

UPDATE [UserRoles]
SET RoleId = Case
when @Status = 'Silver' then 8
when @Status = 'Gold' then 4
when @Status = 'Platinum' then 3
when @Status = 'Stagnant' then 9
end
FROM
[UserRoles] 
INNER JOIN users ON users.UserID = [UserRoles].UserID
WHERE USERNAME like '%' + @usersemail AND RoleId = 3

soorma
Yak Posting Veteran

52 Posts

Posted - 2006-10-16 : 15:58:55

Thanks it worked but there is one more thing if no rows are returned with is sql it should not do an update.

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

Here is the stored proc

Declare @usersemail varchar(50)
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

UPDATE UserRoles
SET RoleId = Case
when @Status = 'Silver' then 8
when @Status = 'Gold' then 4
when @Status = 'Platinum' then 3
when @Status = 'Stagnant' then 9
end
FROM
UserRoles As A
INNER JOIN users as B ON B.UserID = A.UserID
WHERE USERNAME like '%' + @usersemail and (RoleId = 8 or roleid=3 or roleid=4 or roleid=9)

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2006-10-16 : 16:00:29

Check the value of @@ROWCOUNT before you do the UPDATE.

YourCode
...

IF @@ROWCOUNT <> 0
Do Update

Tara Kizer

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources