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