passing dataset to function - 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 2005 Forums

Transact-SQL (2005)

passing dataset to function

Author

Topic

mike123
Master Smack Fu Yak Hacker

1462 Posts

Posted - 2009-09-03 : 01:11:02

Hi,

I have the following function that I am using, its working perfectly.

I want to use this function to insert a value into a column, but I want to make sure it doesnt exist in the column. Is there anyway I can pass it a SELECT statement containing that column, so we can do a WHERE NOT IN (SELECT field FROM tableName)

perhaps this is totally the wrong way to do it, but hopefully someone understands where I am trying to go with this ! :)

any help very appreciated! :)
thanks again
Mike123

SELECT @autoGeneratedPassword = dbo.fnCreatePassword(3,0,3,0)

CREATE FUNCTION [dbo].[fnCreatePassword]
(
@UpperCaseItems SMALLINT,
@LowerCaseItems SMALLINT,
@NumberItems SMALLINT,
@SpecialItems SMALLINT
)
RETURNS VARCHAR(80)
AS
BEGIN
-- Initialize some variables
DECLARE @UpperCase VARCHAR(26),
@LowerCase VARCHAR(26),
@Numbers VARCHAR(10),
@Special VARCHAR(13),
@Temp VARCHAR(8000),
@Password VARCHAR(8000),
@i SMALLINT,
@c VARCHAR(1),
@v TINYINT

-- Set the default items in each group of characters
SELECT @UpperCase = 'ABCDEFGHIJKLMNOPQRSTUVWXYZ',
@LowerCase = 'abcdefghijklmnopqrstuvwxyz',
@Numbers = '0123456789',
@Special = '!@#$%&*()_+-=',
@Temp = '',
@Password = ''

-- Enforce some limits on the length of the password
IF @UpperCaseItems > 20
SET @UpperCaseItems = 20

IF @UpperCaseItems < -20
SET @UpperCaseItems = -20

IF @LowerCaseItems > 20
SET @LowerCaseItems = 20

IF @LowerCaseItems < -20
SET @LowerCaseItems = -20

IF @NumberItems > 20
SET @NumberItems = 20

IF @NumberItems < -20
SET @NumberItems = -20

IF @SpecialItems > 20
SET @SpecialItems = 20

IF @SpecialItems < -20
SET @SpecialItems = -20

-- Get the Upper Case Items
SET @i = ABS(@UpperCaseItems)

WHILE @i > 0 AND LEN(@UpperCase) > 0
SELECT @v = nwd % LEN(@UpperCase) + 1,
@c = SUBSTRING(@UpperCase, @v, 1),
@UpperCase = CASE
WHEN @UpperCaseItems < 0 THEN STUFF(@UpperCase, @v, 1, '')
ELSE @UpperCase
END,
@Temp = @Temp + @c,
@i = @i - 1
FROM dbo.vwNEWID

-- Get the Lower Case Items
SET @i = ABS(@LowerCaseItems)

WHILE @i > 0 AND LEN(@LowerCase) > 0
SELECT @v = nwd % LEN(@LowerCase) + 1,
@c = SUBSTRING(@LowerCase, @v, 1),
@LowerCase = CASE
WHEN @LowerCaseItems < 0 THEN STUFF(@LowerCase, @v, 1, '')
ELSE @LowerCase
END,
@Temp = @Temp + @c,
@i = @i - 1
FROM dbo.vwNEWID

-- Get the Number Items
SET @i = ABS(@NumberItems)

WHILE @i > 0 AND LEN(@Numbers) > 0
SELECT @v = nwd % LEN(@Numbers) + 1,
@c = SUBSTRING(@Numbers, @v, 1),
@Numbers = CASE
WHEN @NumberItems < 0 THEN STUFF(@Numbers, @v, 1, '')
ELSE @Numbers
END,
@Temp = @Temp + @c,
@i = @i - 1
FROM dbo.vwNEWID

-- Get the Special Items
SET @i = ABS(@SpecialItems)

WHILE @i > 0 AND LEN(@Special) > 0
SELECT @v = nwd % LEN(@Special) + 1,
@c = SUBSTRING(@Special, @v, 1),
@Special = CASE
WHEN @SpecialItems < 0 THEN STUFF(@Special, @v, 1, '')
ELSE @Special
END,
@Temp = @Temp + @c,
@i = @i - 1
FROM dbo.vwNEWID

-- Scramble the order of the selected items
WHILE LEN(@Temp) > 0
SELECT @v = nwd % LEN(@Temp) + 1,
@Password = @Password + SUBSTRING(@Temp, @v, 1),
@Temp = STUFF(@Temp, @v, 1, '')
FROM dbo.vwNEWID

RETURN @Password
END

GO

mike123
Master Smack Fu Yak Hacker

1462 Posts

Posted - 2009-09-04 : 02:37:26

so basically what I want to do is

SELECT @autoGeneratedPassword = dbo.fnCreatePassword(3,0,3,0)

IF EXISTS (SELECT columnName FROM tableName WHERE columnName = autoGeneratedPassword )

if TRUE, then keep looping

SELECT @autoGeneratedPassword = dbo.fnCreatePassword(3,0,3,0)

until the IF EXISTS is FALSE

any help very much aprecciated !

thanks,
Mike123

mike123
Master Smack Fu Yak Hacker

1462 Posts

Posted - 2009-09-07 : 22:09:08

bump in case anyone can understand what Im trying to do and can help me out!

thanks again:)
mike123

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources