Monthly Archives: February 2007

Generate random number for each row

Today I had to add a new date column to one of my table, and populate it with some random entries. I found a script from SQL Team Blog about generating random numbers. I modified it a little and used it to populate these dates based on another date I had in my table.

Here is modified code:

-- Create the variables for the random number generation
DECLARE @Upper int;
DECLARE @Lower int-- This will create a random number between 1 and 365
SET @Lower = 1 -- The lowest random number
SET @Upper = 365 -- The highest random number

-- we'll use this temp table to assign a random number to each ID value
CREATE TABLE #temp (ID int NOT NULL, RandNum float NULL)
INSERT #temp (ID) SELECT ID FROM FOO

-- now, assign a new random value to each key value in #temp
DECLARE @id int
DECLARE Randomizer CURSOR
FOR SELECT RandNum FROM #temp

OPEN Randomizer
FETCH NEXT FROM Randomizer INTO @id

WHILE @@Fetch_Status != -1
BEGIN
UPDATE #temp SET RandNum = rand()
WHERE CURRENT OF Randomizer

FETCH NEXT FROM Randomizer
END

CLOSE Randomizer
DEALLOCATE Randomizer

UPDATE F
SET F.NewDate = dateadd(dd, round((T.RandNum * 100), 0), F.AnyDate)
FROM
FOO F INNER JOIN #temp T ON F.ID = T.ID

DROP TABLE #temp

Sequential Numbering/Counting of Records

Now say your table does not have an identify column, you don’t want to use a temporary table or alter your existing table, but you still would like to have a record number associated with each record. In this case you could use a self join to return a record number for each row. Here is an example that calculates a RecNum column, and displays the LastName for each record in the Northwind.dbo.Employees table. This example uses count(*) to count the number of records that are greater than or equal LastName in this self join.

SELECT COUNT(*) RecNum,
       a.LastName
FROM   Northwind.dbo.Employees a
       JOIN Northwind.dbo.Employees b
            ON  a.LastName >= b.LastName
GROUP BY
       a.LastName
ORDER BY
       a.LastName

The results from this query looks like this:

RecNum LastName
1 Buchanan
2 Callahan
3 Davolio
4 Dodsworth
5 Fuller
6 King
7 Leverling
8 Peacock
9 Suyama

Continue reading

SCOPE_IDENTITY() and @@IDENTITY Demystified

I’ve run across several applications where the use of IDENTITY values has caused some confusion, specifically in how the @@IDENTITY function operates given external influences. We’ve all likely been faced with how to grab the newly generated IDENTITY value from a SQL Server database table. And there are a 2 popular techniques that accomplish this with SQL Server:

  1. the @@IDENTITY function
  2. the SCOPE_IDENTITY() function

Continue reading

Disabling ASPX Controls on client side

To enable or disable .Net controls on client side use following scripts.

//To Disable
document.getElementById(element_client_id).setAttribute('disabled','true');

//To Enable
document.getElementById(element_client_id).removeAttribute('disabled');

Nearly all html rendered control would adher to this coding guide, but aspx checkbox has a span element wrapped up on it. To disable checkbox properly add a extra line as below.

document.getElementById(element_client_id).parentElement.removeAttribute('disabled');

Converting string to datetime

Here how you can convert string date to a datetime value:

DECLARE @DateString char(14)
SET @DateString = '20060703074815'
SELECT CAST(SUBSTRING(@DateString, 1, 4) + '-' +
SUBSTRING(@DateString, 5, 2) + '-' +
SUBSTRING(@DateString, 7, 2) + 'T' +
SUBSTRING(@DateString, 9, 2) + ':' +
SUBSTRING(@DateString, 11, 2) + ':' +
SUBSTRING(@DateString, 13, 2) AS datetime)