February 12, 2008 at 11:40 am
Hello,
Seems like this should be pretty straight forward, but its not working. I'm trying to build a string with 7 characters from a random statement:
Declare @mn int
Declare @mx int
Declare @intCounter int
Declare @textNumber varchar(7)
Set @mn = 1
Set @mx = 9
While @intCounter <= 7
Begin
Set @textNumber = @textNumber + Cast(Round(@mn + (RAND() * (@mx-@mn)),0) as varchar(1)) --Generates a random number from 1 to 9
SET @intCounter = @intCounter + 1
End
SELECT @textNumber
Basically trying to build a string like 4567345 for example BUT @textNumber isn't returning anything. Can anyone see anything that doesn't look right?
Strick
February 12, 2008 at 11:51 am
Int counter is not set to anything. Set it = to 1 or 0
set @intcounter = 1
February 12, 2008 at 11:54 am
Additionally, you have initialize the text string
set @textNumber = ''
February 12, 2008 at 11:56 am
You also need to init the value of @textNumber
This will generate the number without a loop:
declare @textNumber varchar(7)
set@textNumber =
right(abs(checksum(newid())%9)+1,1)+
right(abs(checksum(newid())%9)+1,1)+
right(abs(checksum(newid())%9)+1,1)+
right(abs(checksum(newid())%9)+1,1)+
right(abs(checksum(newid())%9)+1,1)+
right(abs(checksum(newid())%9)+1,1)+
right(abs(checksum(newid())%9)+1,1)
select@textNumber
February 12, 2008 at 11:58 am
It also won't help if textnumber isn't initialized to ''.
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
February 12, 2008 at 12:05 pm
Sweet thanks guys,
Its always something small..lol
Strick
February 12, 2008 at 1:27 pm
I think I have a different take on this situation.
In looking at the initial code presentation, well frankly, why loop if you don't have to? And if we can make it simpler, let's do that too.
As to the solution presented by Michael, far to inflexible and long for my tastes.
So my quick (5 minutes) 2 cents is this:
DECLARE @Length INT
SET @Length = 6
DECLARE @RetVal VARCHAR(25)
SELECT TOP(@Length) @RetVal = COALESCE(@RetVal,'') + RIGHT(CHECKSUM(NEWID())%9+1,1)
FROM Master.sys.SysColumns t1
CROSS JOIN Master.sys.SysColumns t2
SELECT@RetVal
This code is easy to pop into a UDF or SP. Can return as large or small a string as needed by adjusting two variables. Very quick and, I think, easy to understand.
GL!
February 12, 2008 at 1:46 pm
John Beggs (2/12/2008)
...As to the solution presented by Michael, far to inflexible and long for my tastes...
Perhaps, but but my solution involves no table access, so it should perform better.
February 12, 2008 at 1:54 pm
I think you'll find that the difference between 1ms and 1ms is pretty negligible. Feel free to test for yourself.
What boundary would you set for flexible coding versus running faster? 10ms? 100ms? 1000ms?
Personally, for no measurable difference, as in this case, I'll go for flexible.
February 12, 2008 at 2:02 pm
John Beggs (2/12/2008)
I think you'll find that the difference between 1ms and 1ms is pretty negligible. Feel free to test for yourself.What boundary would you set for flexible coding versus running faster? 10ms? 100ms? 1000ms?
Personally, for no measurable difference, as in this case, I'll go for flexible.
When I put both solutions into a query window and asked it to showplan, your solution showed as 100% of the batch, so that would be a 100 to 1 or greater difference in performance.
Performance needs depends on the application needs. Is it being called 10 times per second? 100 times per second? 1000 times per second?
February 12, 2008 at 2:24 pm
Sorry, I should have been more clear, as both run in under 1ms.
In regards to the cost versus quantity of use argument that you have presented, I agree, both need to be factored in together. I ignored this in my first post simply because both run in under 1ms, making performance a moot point.
As for the query plan, unfortunately, the results are relative. Look at the actual costs and I think you'll find, as I did, that they are neglible. Like the argument noted above, the cost of the query plan can't fully be judged on it's face value alone.
Try running the two with SET STATISTICS ON and look at the detailed results. I think you'll find that they both run at under 1ms.
February 12, 2008 at 2:32 pm
Of course - why generate each digit separately, when you get get them all at once?
Should you need a few million (or in my example, 1 million) at a time:
select top(1000000)
left(
replace(
replace(
cast(
checksum(newid())as varchar(20))
+cast(checksum(newid()) as varchar(20)
)
,'-','')
,'0','')
,7)
from sys.all_columns sc1, sys.all_columns sc2
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
February 12, 2008 at 3:03 pm
After seeing Matt's, we can just forget I posted! 😀
Great solution.
February 12, 2008 at 3:26 pm
Hey - it's easy when you sit back and watch the fur fly - sometimes a new answer just pops into your head....
Of course - it's swatting a fly with an ICBM if the original poster only needs one.....:P
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
February 12, 2008 at 4:04 pm
Matt Miller (2/12/2008)
Of course - why generate each digit separately, when you get get them all at once?Should you need a few million (or in my example, 1 million) at a time:
select top(1000000)
left(
replace(
replace(
cast(
checksum(newid())as varchar(20))
+cast(checksum(newid()) as varchar(20)
)
,'-','')
,'0','')
,7)
from sys.all_columns sc1, sys.all_columns sc2
Of course, there is a small but real possibility that solution will return rows with less than 7 characters if by chance you get a lot of zeros. 😀
Viewing 15 posts - 1 through 15 (of 28 total)
You must be logged in to reply to this topic. Login to reply