CURSOR Hangs! - 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

Transact-SQL (2000)

CURSOR Hangs!

Author

Topic

MercuryNewt
Starting Member

12 Posts

Posted - 2005-01-14 : 20:12:57

I'm looping through a cursor and calling a stored procedure, which when run by itself executes fine. As a matter of fact, I can't get the stored procedure to error, however, the cursor will continue to run but the stored procedure stops executing. Any idea? I've tried looking for different errors but neither the stored procedure nor the cursor return any.

My team and I have been working with this code for hours now and have been all over the place on the web looking for solutions to no evail.

Any help would be greatly appreciated!

ehorn
Master Smack Fu Yak Hacker

1632 Posts

Posted - 2005-01-14 : 20:35:47

Can you post the cursor and sp ?

MercuryNewt
Starting Member

12 Posts

Posted - 2005-01-14 : 20:50:45

Here's the sp:

REATE PROCEDURE spGenerateNetworkID
@nvcSSN NVARCHAR (9), -- This represents the person's Social Security Number
@nvcFirstName NVARCHAR (25), -- This represents the person's first name
@chrMiddleInitial CHAR (1), -- This represents the person's middle initial
@nvcLastName NVARCHAR (25), -- This represents the person's last name
@nvcNetworkIDType NVARCHAR (4), -- This represents the type of Network ID that will be generated, ie. AHSN for employee, PHYS for physician or VEND for vendor
@nvcOutboundSSN NVARCHAR (9) = '' OUTPUT, -- This represents the Social Security Number to be returned from the stored procedure
@nvcOutboundNetworkID NVARCHAR (8) = '' OUTPUT, -- This represents the Network ID to be returned from the stored procedure
@nvcStatus NVARCHAR (5) = '' OUTPUT, -- This represents an outbound status
@nvcStatusResponse NVARCHAR (125) = '' OUTPUT -- This represents an outbound status response
AS
-- Determine if the @nvcSSN is 9 digits in length
IF (LEN(@nvcSSN) <> 9)
BEGIN
-- If not raise an error and set the appropriate status response
SET @nvcStatus = 'Error'
SET @nvcStatusResponse = 'The @nvcSSN parameter must be a 9 digit NVARCHAR string'
RETURN 100
END
-- Declare a counter for looping over our @nvcSSN parameter
DECLARE @intSSNDigitCheckCounter INT
SET @intSSNDigitCheckCounter = 1
-- Loop over our @nvcSSN parameter
WHILE (@intSSNDigitCheckCounter <= LEN(@nvcSSN))
BEGIN
-- Determine if all the characters that make up our @nvcSSN parameter are numeric
IF (ASCII(SUBSTRING(@nvcSSN,@intSSNDigitCheckCounter,1)) NOT IN ('48','49','50','51','52','53','54','55','56','57'))
BEGIN
-- If not raise an error and set the appropriate status response
SET @nvcStatus = 'Error'
SET @nvcStatusResponse = 'The @nvcSSN parameter must be made up of 9 numeric digits'
RETURN 101
END

-- Increment the value of our @intSSNDigitCheckCounter variable
SET @intSSNDigitCheckCounter = (@intSSNDigitCheckCounter + 1)
END
-- Since our @nvcSSN parameter has proven to be valid, set it as the value of our @nvcOutboundSSN outbound variable
SET @nvcOutboundSSN = @nvcSSN
-- Declare a variable for capturing a Network ID if one already exists for the passed @nvcSSN parameter
DECLARE @nvcExistingNetworkID NVARCHAR (8)
SET @nvcExistingNetworkID = ''
-- Check to see if the value passed in via the @nvcSSN parameter already exists within our NetworkIDs table
SELECT @nvcExistingNetworkID = NetworkID FROM NetworkIDs WHERE SSN = @nvcSSN
-- Determine if our @nvcExistingNetworkID variable contains anything but an empty string
IF (LEN(@nvcExistingNetworkID) > 1)
BEGIN
-- If so pass the existing Network ID back out of the procedure
SET @nvcOutboundNetworkID = @nvcExistingNetworkID
-- Set the appropriate status response
SET @nvcStatus = 'OK'
SET @nvcStatusResponse = 'Previously generated Network ID found'
RETURN 0
END
-- Determine if our @nvcFirstName parameter was passed as something other than an empty string
IF(LEN(@nvcFirstName) < 1)
BEGIN

-- If not raise an error and set the appropriate status response
SET @nvcStatus = 'Error'
SET @nvcStatusResponse = 'The @nvcFirstName parameter is required'
RETURN 102
END
-- Determine if our @nvcLastName parameter was passed as something other than an empty string
IF(LEN(@nvcLastName) < 1)
BEGIN

-- If not raise an error and set the appropriate status response
SET @nvcStatus = 'Error'
SET @nvcStatusResponse = 'The @nvcLastName parameter is required'
RETURN 103
END
-- Determine if our @nvcNetworkIDType parameter was passed as something other than an empty string
IF(LEN(@nvcNetworkIDType) < 1)
BEGIN

-- If not raise an error and set the appropriate status response
SET @nvcStatus = 'Error'
SET @nvcStatusResponse = 'The @nvcNetworkIDType parameter is required'
RETURN 104
END
-- Determine if our @nvcNetworkIDType parameter was passed with a value of AHSN, PHYS or VEND
IF(@nvcNetworkIDType <> 'AHSN' AND @nvcNetworkIDType <> 'PHYS' AND @nvcNetworkIDType <> 'VEND')
BEGIN

-- If not raise an error and set the appropriate status response
SET @nvcStatus = 'Error'
SET @nvcStatusResponse = 'The @nvcNetworkIDType parameter can only contain the value AHSN, PHYS or VEND'
RETURN 105
END
-- Declare required variables
DECLARE @nvcOutboundRandomCharacterString NVARCHAR (25), -- This represents our random character string variable outbound from our call to the spGenerateRandomCharacterString stored procedure below
@nvcNetworkIDNumberSegment NVARCHAR (4), -- This represents the number segment of an AHSN, PHYS or VEND Network ID
@nvcEntireNetworkID NVARCHAR (8), -- This represents our entire Network ID variable
@bitUniqueNetworkIDCreated BIT -- This represents a boolean variable for determining whether our newly created Network ID is unique
-- Set the default values for our variables
SET @nvcOutboundRandomCharacterString = ''
SET @nvcNetworkIDNumberSegment = ''
SET @nvcEntireNetworkID = @nvcNetworkIDType -- Create the new network ID based upon the value of the @nvcNetworkIDType parameter
SET @bitUniqueNetworkIDCreated = 0
-- Loop until a unique Network ID has been created
WHILE (@bitUniqueNetworkIDCreated <> 1)
BEGIN
-- Execute the spGenerateRandomCharacterString stored procedure to return a 4 character random string for use as the number segment of a Network ID
EXEC spGenerateRandomCharacterString 4,
0,
@nvcOutboundRandomCharacterString = @nvcOutboundRandomCharacterString OUTPUT
-- Set our @nvcNetworkIDNumberSegment equal to the value of our @nvcOutboundRandomCharacterString returned from the stored procedure call above
SET @nvcNetworkIDNumberSegment = @nvcOutboundRandomCharacterString
-- Append our @nvcNetworkIDNumberSegment variable to our @nvcEntireNetworkID variable creating the complete Network ID
SET @nvcEntireNetworkID = @nvcEntireNetworkID + @nvcNetworkIDNumberSegment
-- Determine if our newly created Network ID already exists within the NetworkIDs table
IF (NOT EXISTS(SELECT NetworkID FROM NetworkIDs WHERE NetworkID = @nvcEntireNetworkID))
BEGIN
-- If our newly created Network ID was not found, set the value of our @bitUniqueNetworkIDCreated to 1 (true) indicating that a unique Network ID has in fact been created
SET @bitUniqueNetworkIDCreated = 1
END
-- If our newly created Network ID was not unique, reset our variable values
ELSE
BEGIN
SET @nvcNetworkIDNumberSegment = ''
SET @nvcEntireNetworkID = @nvcNetworkIDType
END
END

-- If a new unique Network ID has been created, let's now generate a unique password
IF (@bitUniqueNetworkIDCreated = 1)
BEGIN
-- Declare required variables for generating a unique Password
DECLARE @nvcPassword NVARCHAR (6), -- This represents our Password variable
@bitUniquePasswordCreated BIT -- This represents a boolean variable for determining whether our newly created Password is unique
-- Set the default value for our variables
SET @nvcPassword = ''
SET @bitUniquePasswordCreated = 0
-- Loop until a unique Password has been created
WHILE (@bitUniquePasswordCreated <> 1)
BEGIN
-- Execute the spGenerateRandomCharacterString stored procedure to return a 6 character random string for use as the auto-generated Password
EXEC spGenerateRandomCharacterString 6,
0,
@nvcOutboundRandomCharacterString = @nvcOutboundRandomCharacterString OUTPUT
-- Set our @nvcPassword equal to the value of our @nvcOutboundRandomCharacterString returned from the stored procedure call above
SET @nvcPassword = @nvcOutboundRandomCharacterString

-- Determine if our newly created Password already exists within the NetworkIDs table
IF (NOT EXISTS(SELECT AutoGeneratedPassword FROM NetworkIDs WHERE AutoGeneratedPassword = @nvcPassword))
BEGIN
-- If our newly created Password was not found, set the value of our @bitUniquePasswordCreated to 1 (true) indicating that a unique Password has in fact been created
SET @bitUniquePasswordCreated = 1
END
-- If our newly created Password was not unique, reset our variable value
ELSE
BEGIN
SET @nvcPassword = ''
END
END
END

-- If both a unique Network ID and unique Password have been created, finalize the procedure
IF (@bitUniqueNetworkIDCreated = 1 AND @bitUniquePasswordCreated = 1)
BEGIN
-- Insert the new Network ID, Password and other pertinent data into our NetworkIDs table
INSERT INTO NetworkIDs(NetworkID,SSN,AutoGeneratedPassword,FirstName,MiddleInitial,LastName)
VALUES (@nvcEntireNetworkID,@nvcSSN,@nvcPassword,@nvcFirstName,@chrMiddleInitial,@nvcLastName)
-- Pass the new unique Network ID back out of the procedure
SET @nvcOutboundNetworkID = @nvcEntireNetworkID
-- Set the appropriate status response
SET @nvcStatus = 'OK'
SET @nvcStatusResponse = 'New Network ID generated'
RETURN 0
END
ELSE
BEGIN
SET @nvcStatus = 'BAD'
SET @nvcStatusResponse = 'No New Network ID generated'
RETURN 1
END
GO

and here's the cursor code:

-- Create a cursor to hold the recordset
DECLARE TermedEmployees CURSOR
FOR
SELECT TermedID, SSN, FirstName, MiddleInitial, LastName
FROM TerminatedEmployees
WHERE Created = 0
FOR READ ONLY
-- Open the cursor and traverse
OPEN TermedEmployees

-- Create a variable to hold the inidvidual EmployeeID from a single row
DECLARE @TermedID INT, @SSN NVARCHAR(9), @FirstName NVARCHAR(255), @MiddleInitial NVARCHAR(255), @LastName NVARCHAR(255),
@Status NVARCHAR(5), @StatusResponse NVARCHAR(150)

-- Retrieve the row and set the variable from above
FETCH NEXT FROM TermedEmployees INTO @TermedID, @SSN, @FirstName, @MiddleInitial, @LastName

-- Ensure that the FETCH executed correctly (any @@FETCH_STATUS other than 0)
WHILE (@@FETCH_STATUS = 0)
BEGIN
exec spGenerateNetworkID @SSN, @FirstName, @MiddleInitial, @LastName, 'AHSN', @nvcStatus = @Status OUTPUT, @nvcStatusResponse = @StatusResponse OUTPUT

UPDATE TerminatedEmployees
SET Created = 1
WHERE TermedID = @TermedID

-- Continue looping through the cursor by FETCHing the next row
FETCH NEXT FROM TermedEmployees INTO @TermedID, @SSN, @FirstName, @MiddleInitial, @LastName

END

-- Close the cursor
CLOSE TermedEmployees

-- Delete the reserved memory space for the cursor
DEALLOCATE TermedEmployees

nr
SQLTeam MVY

12543 Posts

Posted - 2005-01-14 : 21:10:19

Odd that you give a return code from the SP but don't use it.
Does it execute the SP at all?
Put in some trace code to see where it gets to - maybe gets stuck tryig to generate unique items.

You can get rid of the numeric check loop
if patindex('%[^0-9]%',@nvcSSN) <> 0

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

MercuryNewt
Starting Member

12 Posts

Posted - 2005-01-14 : 21:17:14

It gets through the stored procedure and returns an appropriate status code when we request it. The cursor code doesn't error or return any different @@FETCH_STATUS other than zero. Also, the numeric check loop, that you refernce, is used to ensure that certain ASCII characters are not utilized in either the Network ID or Password that the stored procedure generates.

nr
SQLTeam MVY

12543 Posts

Posted - 2005-01-14 : 21:26:00

If the SP completes and the fetch status is 0 where does it stop?
Do you mean it keeps calling the SP with the same values?

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

MercuryNewt
Starting Member

12 Posts

Posted - 2005-01-14 : 21:27:48

The fetch stops or hangs for some reason that we can't seem to figure out. We can't locate any errors.

ehorn
Master Smack Fu Yak Hacker

1632 Posts

Posted - 2005-01-14 : 21:43:49

This all seems a bit over-engineered (and very procedural). It appears you are basically creating a new password for terminated employees - is that correct?

Would, could you consider replacing this code with 2 set-based statement (One to insert the networkids and one to update TerminatedEmployees ?

MercuryNewt
Starting Member

12 Posts

Posted - 2005-01-14 : 21:48:55

A new Network ID and Password to be exact, but this same stored procedure will be utilized going forward to create Network IDs and Password for newly hired employees as well. Why do you think it is over-engineered?

MercuryNewt
Starting Member

12 Posts

Posted - 2005-01-14 : 21:53:09

I don't completely understand what you mean. Are you suggesting that I replace the cursor with 2 set-based statements? How exactly would that accomplish my needs? I need to execute the stored procedure for every row within my TerminatedEmployees tabel and insert those records into my NetworkIDs table. I'm only updating the TerminatedEmployees table to ensure that I don't reselect the same records when I execute the cursor a second time, since it continues to hang.

ehorn
Master Smack Fu Yak Hacker

1632 Posts

Posted - 2005-01-14 : 22:18:14

I am suggesting you consider replacing both the cursor and stored procedure with a few set-based statements ( and maybe a few udf's)

I believe it will better modularize your code, provide better debugging, allow for inline calls (udf for password and networkid creation) and dramitically improve the performance.

MercuryNewt
Starting Member

12 Posts

Posted - 2005-01-14 : 23:14:47

We tried this approach, however we utilize the RAND function in the subordinate stored procedure spGenerateRandomCharacterString. When trying to utilize the RAND function in a UDF, we kept getting T-SQL errors stating that it was not allowed. If you think this can be done in a different manner, could you please give some example code here?

I've posted the code from the subordinate procedure below:

CREATE PROCEDURE spGenerateNetworkID
@nvcSSN NVARCHAR (9), -- This represents the person's Social Security Number
@nvcFirstName NVARCHAR (25), -- This represents the person's first name
@chrMiddleInitial CHAR (1), -- This represents the person's middle initial
@nvcLastName NVARCHAR (25), -- This represents the person's last name
@nvcNetworkIDType NVARCHAR (4), -- This represents the type of Network ID that will be generated, ie. AHSN for employee, PHYS for physician or VEND for vendor
@nvcOutboundSSN NVARCHAR (9) = '' OUTPUT, -- This represents the Social Security Number to be returned from the stored procedure
@nvcOutboundNetworkID NVARCHAR (8) = '' OUTPUT, -- This represents the Network ID to be returned from the stored procedure
@nvcStatus NVARCHAR (5) = '' OUTPUT, -- This represents an outbound status
@nvcStatusResponse NVARCHAR (125) = '' OUTPUT -- This represents an outbound status response
AS
-- Determine if the @nvcSSN is 9 digits in length
IF (LEN(@nvcSSN) <> 9)
BEGIN
-- If not raise an error and set the appropriate status response
SET @nvcStatus = 'Error'
SET @nvcStatusResponse = 'The @nvcSSN parameter must be a 9 digit NVARCHAR string'
RETURN 100
END
-- Declare a counter for looping over our @nvcSSN parameter
DECLARE @intSSNDigitCheckCounter INT
SET @intSSNDigitCheckCounter = 1
-- Loop over our @nvcSSN parameter
WHILE (@intSSNDigitCheckCounter <= LEN(@nvcSSN))
BEGIN
-- Determine if all the characters that make up our @nvcSSN parameter are numeric
IF (ASCII(SUBSTRING(@nvcSSN,@intSSNDigitCheckCounter,1)) NOT IN ('48','49','50','51','52','53','54','55','56','57'))
BEGIN
-- If not raise an error and set the appropriate status response
SET @nvcStatus = 'Error'
SET @nvcStatusResponse = 'The @nvcSSN parameter must be made up of 9 numeric digits'
RETURN 101
END

-- Increment the value of our @intSSNDigitCheckCounter variable
SET @intSSNDigitCheckCounter = (@intSSNDigitCheckCounter + 1)
END
-- Since our @nvcSSN parameter has proven to be valid, set it as the value of our @nvcOutboundSSN outbound variable
SET @nvcOutboundSSN = @nvcSSN
-- Declare a variable for capturing a Network ID if one already exists for the passed @nvcSSN parameter
DECLARE @nvcExistingNetworkID NVARCHAR (8)
SET @nvcExistingNetworkID = ''
-- Check to see if the value passed in via the @nvcSSN parameter already exists within our NetworkIDs table
SELECT @nvcExistingNetworkID = NetworkID FROM NetworkIDs WHERE SSN = @nvcSSN
-- Determine if our @nvcExistingNetworkID variable contains anything but an empty string
IF (LEN(@nvcExistingNetworkID) > 1)
BEGIN
-- If so pass the existing Network ID back out of the procedure
SET @nvcOutboundNetworkID = @nvcExistingNetworkID
-- Set the appropriate status response
SET @nvcStatus = 'OK'
SET @nvcStatusResponse = 'Previously generated Network ID found'
RETURN 0
END
-- Determine if our @nvcFirstName parameter was passed as something other than an empty string
IF(LEN(@nvcFirstName) < 1)
BEGIN

-- If not raise an error and set the appropriate status response
SET @nvcStatus = 'Error'
SET @nvcStatusResponse = 'The @nvcFirstName parameter is required'
RETURN 102
END
-- Determine if our @nvcLastName parameter was passed as something other than an empty string
IF(LEN(@nvcLastName) < 1)
BEGIN

-- If not raise an error and set the appropriate status response
SET @nvcStatus = 'Error'
SET @nvcStatusResponse = 'The @nvcLastName parameter is required'
RETURN 103
END
-- Determine if our @nvcNetworkIDType parameter was passed as something other than an empty string
IF(LEN(@nvcNetworkIDType) < 1)
BEGIN

-- If not raise an error and set the appropriate status response
SET @nvcStatus = 'Error'
SET @nvcStatusResponse = 'The @nvcNetworkIDType parameter is required'
RETURN 104
END
-- Determine if our @nvcNetworkIDType parameter was passed with a value of AHSN, PHYS or VEND
IF(@nvcNetworkIDType <> 'AHSN' AND @nvcNetworkIDType <> 'PHYS' AND @nvcNetworkIDType <> 'VEND')
BEGIN

-- If not raise an error and set the appropriate status response
SET @nvcStatus = 'Error'
SET @nvcStatusResponse = 'The @nvcNetworkIDType parameter can only contain the value AHSN, PHYS or VEND'
RETURN 105
END
-- Declare required variables
DECLARE @nvcOutboundRandomCharacterString NVARCHAR (25), -- This represents our random character string variable outbound from our call to the spGenerateRandomCharacterString stored procedure below
@nvcNetworkIDNumberSegment NVARCHAR (4), -- This represents the number segment of an AHSN, PHYS or VEND Network ID
@nvcEntireNetworkID NVARCHAR (8), -- This represents our entire Network ID variable
@bitUniqueNetworkIDCreated BIT -- This represents a boolean variable for determining whether our newly created Network ID is unique
-- Set the default values for our variables
SET @nvcOutboundRandomCharacterString = ''
SET @nvcNetworkIDNumberSegment = ''
SET @nvcEntireNetworkID = @nvcNetworkIDType -- Create the new network ID based upon the value of the @nvcNetworkIDType parameter
SET @bitUniqueNetworkIDCreated = 0
-- Loop until a unique Network ID has been created
WHILE (@bitUniqueNetworkIDCreated <> 1)
BEGIN
-- Execute the spGenerateRandomCharacterString stored procedure to return a 4 character random string for use as the number segment of a Network ID
EXEC spGenerateRandomCharacterString 4,
0,
@nvcOutboundRandomCharacterString = @nvcOutboundRandomCharacterString OUTPUT
-- Set our @nvcNetworkIDNumberSegment equal to the value of our @nvcOutboundRandomCharacterString returned from the stored procedure call above
SET @nvcNetworkIDNumberSegment = @nvcOutboundRandomCharacterString
-- Append our @nvcNetworkIDNumberSegment variable to our @nvcEntireNetworkID variable creating the complete Network ID
SET @nvcEntireNetworkID = @nvcEntireNetworkID + @nvcNetworkIDNumberSegment
-- Determine if our newly created Network ID already exists within the NetworkIDs table
IF (NOT EXISTS(SELECT NetworkID FROM NetworkIDs WHERE NetworkID = @nvcEntireNetworkID))
BEGIN
-- If our newly created Network ID was not found, set the value of our @bitUniqueNetworkIDCreated to 1 (true) indicating that a unique Network ID has in fact been created
SET @bitUniqueNetworkIDCreated = 1
END
-- If our newly created Network ID was not unique, reset our variable values
ELSE
BEGIN
SET @nvcNetworkIDNumberSegment = ''
SET @nvcEntireNetworkID = @nvcNetworkIDType
END
END

-- If a new unique Network ID has been created, let's now generate a unique password
IF (@bitUniqueNetworkIDCreated = 1)
BEGIN
-- Declare required variables for generating a unique Password
DECLARE @nvcPassword NVARCHAR (6), -- This represents our Password variable
@bitUniquePasswordCreated BIT -- This represents a boolean variable for determining whether our newly created Password is unique
-- Set the default value for our variables
SET @nvcPassword = ''
SET @bitUniquePasswordCreated = 0
-- Loop until a unique Password has been created
WHILE (@bitUniquePasswordCreated <> 1)
BEGIN
-- Execute the spGenerateRandomCharacterString stored procedure to return a 6 character random string for use as the auto-generated Password
EXEC spGenerateRandomCharacterString 6,
0,
@nvcOutboundRandomCharacterString = @nvcOutboundRandomCharacterString OUTPUT
-- Set our @nvcPassword equal to the value of our @nvcOutboundRandomCharacterString returned from the stored procedure call above
SET @nvcPassword = @nvcOutboundRandomCharacterString

-- Determine if our newly created Password already exists within the NetworkIDs table
IF (NOT EXISTS(SELECT AutoGeneratedPassword FROM NetworkIDs WHERE AutoGeneratedPassword = @nvcPassword))
BEGIN
-- If our newly created Password was not found, set the value of our @bitUniquePasswordCreated to 1 (true) indicating that a unique Password has in fact been created
SET @bitUniquePasswordCreated = 1
END
-- If our newly created Password was not unique, reset our variable value
ELSE
BEGIN
SET @nvcPassword = ''
END
END
END

-- If both a unique Network ID and unique Password have been created, finalize the procedure
IF (@bitUniqueNetworkIDCreated = 1 AND @bitUniquePasswordCreated = 1)
BEGIN
-- Insert the new Network ID, Password and other pertinent data into our NetworkIDs table
INSERT INTO NetworkIDs(NetworkID,SSN,AutoGeneratedPassword,FirstName,MiddleInitial,LastName)
VALUES (@nvcEntireNetworkID,@nvcSSN,@nvcPassword,@nvcFirstName,@chrMiddleInitial,@nvcLastName)
-- Pass the new unique Network ID back out of the procedure
SET @nvcOutboundNetworkID = @nvcEntireNetworkID
-- Set the appropriate status response
SET @nvcStatus = 'OK'
SET @nvcStatusResponse = 'New Network ID generated'
RETURN 0
END
ELSE
BEGIN
SET @nvcStatus = 'BAD'
SET @nvcStatusResponse = 'No New Network ID generated'
RETURN 1
END
GO

Thanks...

ehorn
Master Smack Fu Yak Hacker

1632 Posts

Posted - 2005-01-14 : 23:45:07

RAND and/or NEWID are not allowed in a udf so you kinda have to trick it. Here I am using a tally table and a view for my udf to create a random character generator.

--create a tally table for use in the view
--only create this once and store as base table in schema
/*
create table dbo.numbers ( n int primary key)
declare @n int; set @n = 1
while @n <=1000
begin
	insert dbo.numbers
	select @n
	set @n = @n+1
end
*/

--create a view to expose NEWID() for randomness

create view dbo.vwRandomCharGenerator
as
	select top 1000 char(n) n
	from dbo.numbers
	where n between 65 and 90 or n between 97 and 122 --only A-Z or a-z characters
	order by newid()
go


--create password generator function which accepts length as input

create function dbo.udfPassWordGenerator (@length int)
returns varchar(1000) -- Cubic Centimeters.
AS
begin
declare @pwd  char(1000)
set @pwd = ''

Select @pwd = n + @pwd 
from 
(
	select n
	from dbo.vwRandomCharGenerator

) d
return left(@pwd,@length)
end
go


--call our password generator inline, passing in the desired length

select dbo.udfPassWordGenerator(10) pwd


--select with random ids generated

SELECT 
	dbo.udfPassWordGenerator(4) NetworkID, 
	TermedID, 
	SSN, 
	dbo.udfPassWordGenerator(6) AutoGeneratedPassword,
	FirstName, 
	MiddleInitial, 
	LastName
FROM 
	TerminatedEmployees
WHERE 
	Created = 0
	and (len(SSN) = 9 and isnumeric(SSN) = 1 )
	and (FirstName is not null or len(FirstName) > 0)
	and (LastName is not null or len(LastName) > 0)
--	and ( (netid is not null or len(netid) > 0) and netid in ('AHSN', 'PHYS', 'VEND') )

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources