This enforces that it is necessary to know one way of creating random strings in order to use the function. One needs to pass a parameter to the function with a random value.
AUTOMATIC PASSWORD GENERATOR ONLINE UPDATE
The idea behind the function is that if somebody requires a password reset I could do the following: UPDATE Users SET Password = dbo.GeneratePassword() WHERE UserID = can address the following concerns as expressed in a comment on this answer: Given the newly provided context for usage of this function from the Question update: The main disadvantage here is that a Scalar function is much more likely to have its return value cached than an iTVF. You can create a SQLCLR function that would have access to randomizing functionality. SELECT * FROM Please see Item # 2 below related to running this for multiple rows! SELECT NULL UNION ALL SELECT NULL UNION ALL SELECT NULLĪnd then execute it as follows: DECLARE INT = 10 SELECT NULL UNION ALL SELECT NULL UNION ALL SELECT NULL UNION ALL Putting all of this info into practice, we can do the following with regards to the original request: CREATE FUNCTION VARBINARY(30)) SELECT _ScalarUDF(CRYPT_GEN_RANDOM(10)) AS SELECT * FROM _MultistatementTVF(CRYPT_GEN_RANDOM(10)) RETURNS TABLE ( VARCHAR(100))Īnd then run the tests: SELECT * FROM _InlineTVF(CRYPT_GEN_RANDOM(10)) AND, if you can structure your algorithm to be a single SELECT such that it can be done in an Inline Table-Valued Function / iTVF (usually using CTEs), then you technically can use a non-deterministic function (at least effectively) since the function call will pass in the expression instead of the result of that expression, like it does with Multistatement TVFs and Scalar UDFs: USE ĬONVERT(VARCHAR(20), 2) AS You can always create your own algorithm and pass in the value of CRYPT_GEN_RANDOM for the random aspect. uses a "length" of 20 since every 2 bytes will become a character (when running this in a database with a default collation that does not end in _SC, which is most cases). uses a "length" of 10 since each hex byte becomes a character. Since a hex byte is 2 characters, you only need to generate a 5-byte value. Please note the "length" values passed into the CRYPT_GEN_RANDOM function. But you can also see that even with a single row, multiple calls will get different return values (just like NEWID() ). Returns: Hex HexStringWithout0x Translated-ASCII Translated-UCS2orUTF16Ġx4F7D9ABBC4 0ECF378A7A ¿"bü<ݱØï 튄ꆠ䤅㫘ᓟ멿ৢ폫씎䛯Īs you can see, you need to use the format option of 2 on the CONVERT function so that it doesn't give you characters that, while more diverse, might be harder to type in, and you don't get the leading 0x. If you are ok with just hex values (0 - 9 and A - F) you can just call CRYPT_GEN_RANDOM which was introduced in SQL Server 2008: SELECT CRYPT_GEN_RANDOM(5) AS ,ĬONVERT(VARCHAR(20), CRYPT_GEN_RANDOM(5), 2) AS ,ĬONVERT(VARCHAR(20), CRYPT_GEN_RANDOM(10)) AS ,ĬONVERT(NVARCHAR(20), CRYPT_GEN_RANDOM(20)) AS You have a few options (TL DR -> working function is towards the end of Option #2, BUT also see Update section after Option #3 !):
0 kommentar(er)
