February 25, 2009 at 4:19 am
Help please
For some reason the below code just does not seem to work and i cant see why!
DECLARE @consultantID INT,
@ApplicationNamenvarchar(256),
@UserNamenvarchar(256),
@passwordnvarchar(256),
@emailnvarchar(256),
@IsUserAnonymousBIT,
@datetimeDATETIME,
@ApplicationIDUNIQUEIDENTIFIER,
@newidUNIQUEIDENTIFIER,
@saltVARCHAR(32)
EXEC dbo.generatepassword_sp 15,@salt OUTPUT
SELECT @ApplicationName = NULL
SET @ApplicationName = 'Lawstaff'
SELECT @IsUserAnonymous = 0
SET @IsUserAnonymous = 0
SELECT @datetime = NULL
SET @datetime = GETDATE()
SET @newid = NEWID()
SELECT @ApplicationID = DBO.aspnet_Applications.ApplicationId FROM dbo.aspnet_Applications WHERE dbo.aspnet_Applications.ApplicationName = @ApplicationName
IF OBJECT_ID('tempdb..#consultantTemp') IS NOT NULL
BEGIN
drop table #consultantTemp
END
set rowcount 0
SELECTNUll consultantKey
,consultantID
,username
,password
INTO#consultantTemp
FROMdbo.consultants
WHEREofficeid IN (1,4,6) --AND username='Michelle'
set rowcount 1
UPDATE #consultantTemp set consultantKey = 1
while SELECT @@rowcount > 0
BEGIN
set rowcount 0
SELECT @consultantID=consultantID from #consultantTemp where consultantKey = 1
SELECT @UserName=UserName from #consultantTemp where consultantKey = 1
SELECT @password=password from #consultantTemp where consultantKey = 1
SELECT @email=Email from #consultantTemp where consultantKey = 1
EXEC dbo.aspnet_Users_CreateUser
@ApplicationID,
@UserName,
@IsUserAnonymous,
@datetime,
@newid
delete #consultantTemp where consultantKey = 1
set rowcount 1
update #consultantTemp set consultantKey = 1
end
set rowcount 0
Thanks for any help
February 25, 2009 at 4:51 am
You pull over the data to be processed into a temptb, so you'll no longer lock out the actual table.
- Why don't you use a regular cursor to loop your sproc execution ?
- why are you issuing 3 selects to fill up @variables, this can be performed in a single select and is not needed if you use the cursor.
The problem is
while select @@rowcount > 0
it should be
while @@rowcount > 0
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
February 25, 2009 at 6:05 am
It seems you want to loop through all records in #consultantTemp table. But I think it will not work this way. Either you have to use CURSOR for this or use below code...
CREATe TABLE #consultantTemp
(
consultantKey INT IDENTITY,
consultantID INT,
username VARCHAR(50), --Change datatype and length as per you table definition
password VARCHAR(50), --Change datatype and length as per you table definition
Email VARCHAR(50) --Change datatype and length as per you table definition
)
DECLARE @max-2 INT
DECLARE @CNT INT
INSERT INTO #consultantTemp(consultantID, username, password, Email)
SELECT consultantID, username, password, Email FROM dbo.consultants
WHERE officeid IN (1,4,6) --AND username='Michelle'
SET @max-2 = @@ROWCOUNT
SET @CNT = 1
WHILE @CNT < @max-2
BEGIN
SELECT @consultantID = consultantID,
@UserName=UserName,
@password=password,
@email=Email
FROM #consultantTemp WHERE consultantKey = @CNT
--ADD YOUOR CODE HERE
SET @CNT = @CNT + 1
END
Regards,
Nitin
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply