March 4, 2013 at 6:17 am
Hi Friends,
DECLARE @JurisID int, @CodedID int, @SrcCodeValueRef varchar(100), @SrcCodeDescr varchar(100), @SrcCodeValue varchar(100), @DecNumber bigint
SET @DecNumber = @Number
declare @result varchar(50), @chars varchar(36), @IntFlag int
SET @chars = N'0123456789ABCDEFGHIJKLMNOPQRSTUVWXYZ'
DECLARE MySql CURSOR FOR SELECT * FROM #tempSrcCodeDB where SrcCodeValueRef is not null and SrcCodeValueRef not like '' and LEN(LTRIM(RTRIM(SrcCodeValue))) > @MaxLength
OPEN Mysql
FETCH NEXT FROM Mysql INTO @JurisID, @CodedID, @SrcCodeValueRef, @SrcCodeValue, @SrcCodeDescr
WHILE @@FETCH_STATUS = 0
BEGIN
IF ((LEN(@DecNumber)= @MaxLength ) and LEN(LTRIM(Rtrim(@SrcCodeValue))) > @MaxLength ) /* If Number is greater than the maxlength */
BEGIN
insert into SrcCodeMap (JurisID, CodeID, SrcCodeValueRef, SrcCodeValue, SrcCodeDescr, PnxCodeValue, SysCodeValue, VendorCode,CreatedDttm)
Values (@JurisID, @CodedID, @SrcCodeValueRef, @SrcCodeValue, @SrcCodeDescr,@DecNumber, @DecNumber,@VendorCode,GETDATE())
set @DecNumber = @DecNumber - 1
GOTO FETCHNEXT
END
ELSE
BEGIN
set @IntFlag = 0
WHILE (@IntFlag = 0) /* We used to generate random(alpha-numeric) values when we run out of Number */
BEGIN
IF @MaxLength = 1
BEGIN
SET @result = SUBSTRING(@chars, CAST((RAND() * LEN(@chars)) AS INT) + 1, 1)
END
IF @MaxLength = 2
BEGIN
SET @result = SUBSTRING(@chars, CAST((RAND() * LEN(@chars)) AS INT) + 1, 1) + SUBSTRING(@chars, CAST((RAND() * LEN(@chars)) AS INT) + 1, 1)
END
IF @MaxLength = 3
BEGIN
SET @result = SUBSTRING(@chars, CAST((RAND() * LEN(@chars)) AS INT) + 1, 1) + SUBSTRING(@chars, CAST((RAND() * LEN(@chars)) AS INT) + 1, 1)
+ SUBSTRING(@chars, CAST((RAND() * LEN(@chars)) AS INT) + 1, 1)
END
IF NOT EXISTS(select * from SrcCodeMap where CodeID = @CodeID and LTRIM(RTRIM(PnxCodeValue)) = @result )
--IF (select COUNT(distinct PnxCodeValue) from SrcCodeMap where CodeID = @CodeID and LTRIM(RTRIM(PnxCodeValue)) = @result ) = 0
BEGIN
insert into SrcCodeMap (JurisID, CodeID, SrcCodeValueRef, SrcCodeValue, SrcCodeDescr, PnxCodeValue, SysCodeValue, VendorCode,CreatedDttm)
Values (@JurisID, @CodedID, @SrcCodeValueRef, @SrcCodeValue, @SrcCodeDescr,@result, @result, @VendorCode,GETDATE())
GOTO FETCHNEXT
END
ELSE
continue
END
END
FETCHNEXT:
FETCH NEXT FROM Mysql INTO @JurisID, @CodedID, @SrcCodeValueRef, @SrcCodeValue, @SrcCodeDescr
END
print @@ROWCOUNT
CLOSE Mysql
DEALLOCATE Mysql
END
I have written this cursor(I showed you part of it) to get some distinct random values based on the length given by user (this cursor is called by a stored procedure). As you see, i declared a variable containing '0123456789ABCDEFGHIJKLMNOPQRSTUVWXYZ' . So the function will generate random values from here. My problem is, if i have to generate random values of one char length, i can generate upto 36 different values...If the input given by the customer, I mean the input is always going to be coming from the select statement like "select column1 from table. " So if the count of the data exceeds more than 36, this cursor can't generate any more values of length 1. So the cursor is keep on looping and it is uncommitted...So i am not able to move to the next input given by the end user. So gimme your suggestions to avoid if any of the input is keep on looping due to this kinda problem....i want to uncommit it and move to the next statement. Please gimme any kinda your suggestion if you really think there might be another way to handle his. Any suggestions would be really appreciated.
March 4, 2013 at 6:37 am
You've got no transactions in there, so every single statement is running in auto-commit, commit on completion. There's no 'uncommit' without explicit transactions.
What are you trying to do here?
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
March 4, 2013 at 6:47 am
GilaMonster (3/4/2013)
You've got no transactions in there, so every single statement is running in auto-commit, commit on completion. There's no 'uncommit' without explicit transactions.What are you trying to do here?
Yes, i did not use any transactions explicitly....even implicitly.....I am trying to generate random values based on the length and the data given by the end user. (it will be passed through parameters). For example if the end user pass the parameter with length 1, this cursor will generate random 36 values. if 2, then we know the probablity of generating unique random values for 36P2. so for example if the data (getting from select column from table) is having 40 rows to be inserted with a random values for each row, it can generate upto 36 rows of length 1. then it starts to keep on looping since it can not generate more than that.
So please give me your ideas to avoid this. I know i did not create the cursor properly and that why i came here to get some ideas.
March 4, 2013 at 7:06 am
Have you looked at alternative means of populating @result and @VendorCode in the following,
with a view to making the entire process set-based?
INSERT INTO SrcCodeMap (JurisID, CodeID, SrcCodeValueRef, SrcCodeValue, SrcCodeDescr,
PnxCodeValue,
SysCodeValue,
VendorCode,
CreatedDttm)
SELECT JurisID, CodedID, SrcCodeValueRef, SrcCodeValue, SrcCodeDescr,
PnxCodeValue = @result,
SysCodeValue = @result,
VendorCode = @VendorCode,
CreatedDttm = GETDATE()
FROM #tempSrcCodeDB
WHERE SrcCodeValueRef IS NOT NULL
AND SrcCodeValueRef not like ''
AND LEN(LTRIM(RTRIM(SrcCodeValue))) > @MaxLength
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
March 4, 2013 at 7:27 am
ChrisM@Work (3/4/2013)
Have you looked at alternative means of populating @result and @VendorCode in the following,with a view to making the entire process set-based?
INSERT INTO SrcCodeMap (JurisID, CodeID, SrcCodeValueRef, SrcCodeValue, SrcCodeDescr,
PnxCodeValue,
SysCodeValue,
VendorCode,
CreatedDttm)
SELECT JurisID, CodedID, SrcCodeValueRef, SrcCodeValue, SrcCodeDescr,
PnxCodeValue = @result,
SysCodeValue = @result,
VendorCode = @VendorCode,
CreatedDttm = GETDATE()
FROM #tempSrcCodeDB
WHERE SrcCodeValueRef IS NOT NULL
AND SrcCodeValueRef not like ''
AND LEN(LTRIM(RTRIM(SrcCodeValue))) > @MaxLength
Chris. I know what you mean by. the table i showed here is where we are getting the input rows. okay..let me explain..... you see this field in above, "PnxCodeValue " this is the filed where i am trying to update a unique random value.
for example, if this is the input row,
Select 100,0,'AttachDoc','Sample', 'Test'
union
Select 101,0,'AttachDoc','Sample1', 'Test1'
i will insert this row into the table like
100,0,'AttachDoc','Sample', 'Test', 'A'
101,0,'AttachDoc','Sample1', 'Test1', '9'
-- here you see, the last column is the random values...so it will generate random values, based on how many i insert.
If you still need the whole DDL and DML, let me know, i can give you , Chris. Sorry for the inconvience.
March 4, 2013 at 7:29 am
What ever the process, you can't generate unique random values more than allocated right, Chris ? I am just curious...
March 4, 2013 at 7:35 am
prakashr.r7 (3/4/2013)
What ever the process, you can't generate unique random values more than allocated right, Chris ? I am just curious...
They're not guaranteed to be unique! Run this
declare @result varchar(50), @chars varchar(36), @IntFlag int
SET @chars = N'0123456789ABCDEFGHIJKLMNOPQRSTUVWXYZ'
SET @result = SUBSTRING(@chars, CAST((RAND() * LEN(@chars)) AS INT) + 1, 1)
SELECT @result
go 36
- and you are very likely to see the same character twice. I've just run it - 'B' came up twice and 'X' three times.
Why should they be 'randomised'? If you took them off the stack one at a time, they could be unique.
Why do you want them to be unique (even when they clearly are not)?
Why restrict yourself to a single alphanumeric? (or two, or three?)
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
March 4, 2013 at 7:36 am
Sounds like an extremely odd requirement, so I doubt we're getting the whole picture. I'd suggest that you think about what Chris stated.
Anyway, I guess that something like this would do: -
DECLARE @Imput VARCHAR(8000) = '0123456789ABCDEFGHIJKLMNOPQRSTUVWXYZ', @length_required INT = 2;
WITH CTE AS (SELECT 1 N FROM (VALUES(1),(1),(1),(1),(1),(1),(1),(1),(1),(1))a(N)),
CTE2 AS (SELECT 1 N FROM CTE x CROSS JOIN CTE y),
CTE3 AS (SELECT 1 N FROM CTE2 x CROSS JOIN CTE2 y),
tally AS (SELECT TOP(DATALENGTH(@Imput))
ROW_NUMBER() OVER(ORDER BY (SELECT NULL)) N
FROM CTE3),
Subsets AS (SELECT CAST(SUBSTRING(@Imput, N, 1) AS VARCHAR(8000)) AS Token,
CAST('.'+CAST(N AS CHAR(5))+'.' AS VARCHAR(8000)) AS Permutation,
CAST(1 AS INT) AS Iteration
FROM tally
UNION ALL
SELECT CAST(Token+SUBSTRING(@Imput, N, 1) AS VARCHAR(8000)) AS Token,
CAST(Permutation+CAST(N AS CHAR(5))+'.' AS VARCHAR(8000)) AS
Permutation,
s.Iteration + 1 AS Iteration
FROM Subsets s
INNER JOIN tally n ON s.Permutation NOT LIKE '%.'+CAST(N AS CHAR(5))+'.%'
AND s.Iteration <= @length_required)
SELECT Token
FROM Subsets
WHERE Iteration = @length_required
ORDER BY Token;
So from your string you get: -
36 different combinations of 1 character strings,
1,260 different combinations of 2 character strings,
42,840 different combinations of 3 character strings
etc etc.
This is going to get exponentionally slower as you increase the length of the "@Imput" and "@length_required" due to the triangular join in the recursive part of the CTE.
March 4, 2013 at 7:37 am
You can do it using user defined function with a help from small view which makes NEWID() safe for use in UDF's:
CREATE VIEW dbo.UDFSafeNewIDBase AS SELECT NEWID() as SafeNewId
GO
-- will generate a random string up to 10 characters length:
CREATE FUNCTION dbo.fn_GetRandomString
(@N INT)
RETURNS VARCHAR(10)
AS
BEGIN
RETURN (SELECT (SELECT TOP(@N) SUBSTRING('0123456789ABCDEFGHIJKLMNOPQRSTUVWXYZ'
,ABS(CHECKSUM((SELECT SafeNewId FROM dbo.UDFSafeNewIDBase)))%36+1,1)
FROM (VALUES (1),(2),(3),(4),(5),(6),(7),(8),(9),(10)) q(n)
FOR XML PATH ('')));
END
GO
Now if you need to generate one or many random strings of required length you can use this:
-- get single random string value of 4 chars length
DECLARE @STR VARCHAR(10)
SET @STR = dbo.fn_GetRandomString(4)
SELECT @STR
-- get 100's of them of 2 chars in length
-- please note: they are random but not guaranteed to be unique!!!!
SELECT TOP 100 dbo.fn_GetRandomString(2)
FROM sys.objects
Actually, for some reason I think you really need not random but an unique codes...
Here the thread where I've shown how you can do this using n-base encoding logic:
http://www.sqlservercentral.com/Forums/Topic1267659-391-2.aspx
Oops, look like you do want random ones...
March 4, 2013 at 7:42 am
IF NOT EXISTS(select * from SrcCodeMap where CodeID = @CodeID and LTRIM(RTRIM(PnxCodeValue)) = @result )
--IF (select COUNT(distinct PnxCodeValue) from SrcCodeMap where CodeID = @CodeID and LTRIM(RTRIM(PnxCodeValue)) = @result ) = 0
BEGIN
insert into SrcCodeMap (JurisID, CodeID, SrcCodeValueRef, SrcCodeValue, SrcCodeDescr, PnxCodeValue, SysCodeValue, VendorCode,CreatedDttm)
Values (@JurisID, @CodedID, @SrcCodeValueRef, @SrcCodeValue, @SrcCodeDescr,@result, @result, @VendorCode,GETDATE())
GOTO FETCHNEXT
END
ELSE
continue
END
END
Yes, They are not guarnteed. That's why i created a If statement to check for the duplicates and if we have already inserted random value incoming, the "continue" will fetch the next random values...
Yes...i am restricted, Chris 🙁 . This is how my functionality works. So i have to stick with it. no other option. Have to generate unique random values. So if you see the above statement, when it is out of random values to be inserted, it is keep on looping up. I want to avoid that, Chris. Please help me.
March 4, 2013 at 7:49 am
Cadavre (3/4/2013)
Sounds like an extremely odd requirement, so I doubt we're getting the whole picture. I'd suggest that you think about what Chris stated.Anyway, I guess that something like this would do: -
DECLARE @Imput VARCHAR(8000) = '0123456789ABCDEFGHIJKLMNOPQRSTUVWXYZ', @length_required INT = 2;WITH CTE AS (SELECT 1 N FROM (VALUES(1),(1),(1),(1),(1),(1),(1),(1),(1),(1))a(N)),
CTE2 AS (SELECT 1 N FROM CTE x CROSS JOIN CTE y),
CTE3 AS (SELECT 1 N FROM CTE2 x CROSS JOIN CTE2 y),
tally AS (SELECT TOP(DATALENGTH(@Imput))
ROW_NUMBER() OVER(ORDER BY (SELECT NULL)) N
FROM CTE3),
Subsets AS (SELECT CAST(SUBSTRING(@Imput, N, 1) AS VARCHAR(8000)) AS Token,
CAST('.'+CAST(N AS CHAR(5))+'.' AS VARCHAR(8000)) AS Permutation,
CAST(1 AS INT) AS Iteration
FROM tally
UNION ALL
SELECT CAST(Token+SUBSTRING(@Imput, N, 1) AS VARCHAR(8000)) AS Token,
CAST(Permutation+CAST(N AS CHAR(5))+'.' AS VARCHAR(8000)) AS
Permutation,
s.Iteration + 1 AS Iteration
FROM Subsets s
INNER JOIN tally n ON s.Permutation NOT LIKE '%.'+CAST(N AS CHAR(5))+'.%'
AND s.Iteration <= @length_required)
SELECT Token
FROM Subsets
WHERE Iteration = @length_required
ORDER BY Token;
So from your string you get: -
36 different combinations of 1 character strings,
1,260 different combinations of 2 character strings,
42,840 different combinations of 3 character strings
etc etc.
This is going to get exponentionally slower as you increase the length of the "@Imput" and "@length_required" due to the triangular join in the recursive part of the CTE.
Generating Unique random values is done. But if it is out of that number, i have to stop doing the process and move on to next one.
I use this curosr via stored procedure...
So there will be an exec statement to pass parameters....and there are multiple execute statements are there, so if one get stuck, it can not move to the next one. 🙁
March 4, 2013 at 7:52 am
Eugene Elutin (3/4/2013)
You can do it using user defined function with a help from small view which makes NEWID() safe for use in UDF's:
CREATE VIEW dbo.UDFSafeNewIDBase AS SELECT NEWID() as SafeNewId
GO
-- will generate a random string up to 10 characters length:
CREATE FUNCTION dbo.fn_GetRandomString
(@N INT)
RETURNS VARCHAR(10)
AS
BEGIN
RETURN (SELECT (SELECT TOP(@N) SUBSTRING('0123456789ABCDEFGHIJKLMNOPQRSTUVWXYZ'
,ABS(CHECKSUM((SELECT SafeNewId FROM dbo.UDFSafeNewIDBase)))%36+1,1)
FROM (VALUES (1),(2),(3),(4),(5),(6),(7),(8),(9),(10)) q(n)
FOR XML PATH ('')));
END
GO
Now if you need to generate one or many random strings of required length you can use this:
-- get single random string value of 4 chars length
DECLARE @STR VARCHAR(10)
SET @STR = dbo.fn_GetRandomString(4)
SELECT @STR
-- get 100's of them of 2 chars in length
-- please note: they are random but not guaranteed to be unique!!!!
SELECT TOP 100 dbo.fn_GetRandomString(2)
FROM sys.objects
Actually, for some reason I think you really need not random but an unique codes...Here the thread where I've shown how you can do this using n-base encoding logic:
http://www.sqlservercentral.com/Forums/Topic1267659-391-2.aspx
Oops, look like you do want random ones...
you are correct, What ever it is , i just want unique values....but how can we predict that if we have more to insert than how much we can generate ? this is my concern....
March 4, 2013 at 8:02 am
prakashr.r7 (3/4/2013)
...Yes...i am restricted, Chris 🙁 . This is how my functionality works. So i have to stick with it. no other option. Have to generate unique random values.
Checking to see if all 36 charactes have been consumed in a single call to the sproc doesn't sound tricky. Changing it so that the loop (we'll get back to this) picks the characters sequentially off a stack until they've all been picked would be trivial, too. Cadavre and Eugene both show how to do this. But why do you have to live with this functionality which is crippling the actual, simple, process of inserting a set of data into one table from another - and increasing the amount and complexity of your code by a factor of 10? What makes it so critical that it overrides the business requirement?
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
March 4, 2013 at 8:15 am
ChrisM@Work (3/4/2013)
prakashr.r7 (3/4/2013)
...Yes...i am restricted, Chris 🙁 . This is how my functionality works. So i have to stick with it. no other option. Have to generate unique random values.
Checking to see if all 36 charactes have been consumed in a single call to the sproc doesn't sound tricky. Changing it so that the loop (we'll get back to this) picks the characters sequentially off a stack until they've all been picked would be trivial, too. Cadavre and Eugene both show how to do this. But why do you have to live with this functionality which is crippling the actual, simple, process of inserting a set of data into one table from another - and increasing the amount and complexity of your code by a factor of 10? What makes it so critical that it overrides the business requirement?
I agreee with you...and the function Eugene gave is really good. I can over ride minw with this , but he is also unsure of unique values. You may think i am mad (no surprise :-)). I have been ordered to generate only unique values. already explained to my high authorites. they are not listening to me. And we pass lot of exec statements to call the stored procedure which contains this cursor. So i am not able to commit if one get stuck. How can i do this in a better way and to move to the next statement?
🙂 Atleast what i can do is, i have to skip the one which get stuck. and that's why i came to you all SQL gurus.
March 4, 2013 at 8:19 am
if you want unique values then use 36-base encoding function from here:
http://www.sqlservercentral.com/Forums/Topic1267659-391-2.aspx
That one will generate unique values and it will "automatically" determine the size required based on how many numbers to be generated...
Viewing 15 posts - 1 through 15 (of 45 total)
You must be logged in to reply to this topic. Login to reply