March 14, 2016 at 10:21 am
Hello,
I have created the cursor below. The intent of my project is to automate the creation of user accounts.
What I am doing is creating the username, then want to check to see if it already exists. If it exists, I want to add a number to the end of the username. However, my code is limited because I do not know how to do a 2nd check. I am looking for help to understand how I can check the table a 2nd time to see if my new username exists.
Basically, I am looking for a way to check the table until I create a new unique username or a suggestion on how I can do this a better way.
Thank you in advance!
DECLARE
@UN_IDint,
@ValidateUserName varchar(20)
DECLARE db_cursor CURSOR FOR
SELECT @UN_ID, UserName FROM #CreateUser k INNER JOIN Users b ON k.UserName = .ValidateUserName
OPEN db_cursor
FETCH NEXT FROM db_cursor INTO @UN_ID, @ValidateUserName
WHILE @@FETCH_STATUS = 0
BEGIN
UPDATE #CreateKeyCode
SET keycode = @ValidateUserName + '1'
FROM #CreateUser
WHERE UN_ID = @UN_ID
FETCH NEXT FROM db_cursor INTO @UN_ID, @ValidateUserName
CLOSE db_cursor
DEALLOCATE db_cursor
END
March 14, 2016 at 10:28 am
rjjh78 (3/14/2016)
Hello,I have created the cursor below. The intent of my project is to automate the creation of user accounts.
What I am doing is creating the username, then want to check to see if it already exists. If it exists, I want to add a number to the end of the username. However, my code is limited because I do not know how to do a 2nd check. I am looking for help to understand how I can check the table a 2nd time to see if my new username exists.
Basically, I am looking for a way to check the table until I create a new unique username or a suggestion on how I can do this a better way.
Thank you in advance!
DECLARE
@UN_IDint,
@ValidateUserName varchar(20)
DECLARE db_cursor CURSOR FOR
SELECT @UN_ID, UserName FROM #CreateUser k INNER JOIN Users b ON k.UserName = .ValidateUserName
OPEN db_cursor
FETCH NEXT FROM db_cursor INTO @UN_ID, @ValidateUserName
WHILE @@FETCH_STATUS = 0
BEGIN
UPDATE #CreateKeyCode
SET keycode = @ValidateUserName + '1'
FROM #CreateUser
WHERE UN_ID = @UN_ID
FETCH NEXT FROM db_cursor INTO @UN_ID, @ValidateUserName
CLOSE db_cursor
DEALLOCATE db_cursor
END
You do not need a cursor or a loop at all for this type of thing. You just need to use a tally or numbers table with an EXISTS.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
March 14, 2016 at 10:38 am
You should read more about tally tables here. http://www.sqlservercentral.com/articles/T-SQL/62867/%5B/url%5D
I keep a view in my system with a tally table that looks like this.
create View [dbo].[cteTally] as
WITH
E1(N) AS (select 1 from (values (1),(1),(1),(1),(1),(1),(1),(1),(1),(1))dt(n)),
E2(N) AS (SELECT 1 FROM E1 a, E1 b), --10E+2 or 100 rows
E4(N) AS (SELECT 1 FROM E2 a, E2 b), --10E+4 or 10,000 rows max
cteTally(N) AS
(
SELECT ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) FROM E4
)
select N from cteTally
So now for your challenge we first need a table to work with so I created a temp table named #Users. Here is the table and data.
if OBJECT_ID('tempdb..#Users') is not null
drop table #Users
create table #Users
(
UserName varchar(20)
)
insert #Users
select 'Sean'
With the framework in place this becomes very simple. Something like this will get you the results you are looking for with no looping at all.
declare @ValidateUserName varchar(20) = 'Sean'
select top 1 NewUserName
from
(
select @ValidateUserName as NewUserName
union all
select @ValidateUserName + CONVERT(varchar(5), N)
from cteTally
) x
where not exists (select UserName from #Users u where u.UserName = x.NewUserName)
Feel free to add some extra data to test. Change your insert to have several more entries.
insert #Users
select 'Sean' union all
select 'Sean1' union all
select 'Sean2' union all
select 'Sean3' union all
select 'Sean4' union all
select 'Sean5'
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
Viewing 3 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy