GENERATE RANDOM STRING IN SQL SERVER 2005

set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go
ALTER PROCEDURE [dbo].[aesp_GenerateRandomString] (
@useNumbers bit,
@useLowerCase bit,
@useUpperCase bit,
@charactersToUse as varchar(100),
@passwordLength as smallint,
@password varchar(30) OUTPUT
)
AS

BEGIN

DECLARE @characters varchar(100)
DECLARE @count int

IF @passwordLength <= 0
RaisError(‘Cannot generate a random string of zero length.’,16,1)

SET @characters = ”

IF @useNumbers = 1
BEGIN
— load up numbers 0 – 9
SET @count = 48
WHILE @count <=57
BEGIN
SET @characters = @characters + Cast(CHAR(@count) as char(1))
SET @count = @count + 1
END
END

IF @useLowerCase = 1
BEGIN
— load up uppercase letters A – Z
SET @count = 65
WHILE @count <=90
BEGIN
SET @characters = @characters + Cast(CHAR(@count) as char(1))
SET @count = @count + 1
END
END

IF @useUpperCase = 1
BEGIN
— load up lowercase letters a – z
SET @count = 97
WHILE @count <=122
BEGIN
SET @characters = @characters + Cast(CHAR(@count) as char(1))
SET @count = @count + 1
END
END

SET @count = 0
SET @password = ”

— IF you specIFy a character SET to use, the bit flags get ignored.
IF LEN(@charactersToUse) > 0
BEGIN
WHILE charindex(@charactersToUse,’ ‘) > 0
BEGIN
SET @charactersToUse = replace(@charactersToUse,’ ‘,”)
END

IF LEN(@charactersToUse) = 0
RaisError(‘Cannot use an empty character SET.’,16,1)

WHILE @count <= @passwordLength
BEGIN
SET @password = @password +
SUBSTRING(@charactersToUse,CAST(ABS(CHECKSUM(NEWID()))*RAND(@count) as
int)%LEN(@charactersToUse)+1,1)
SET @count = @count + 1
END
END
ELSE
BEGIN
WHILE @count <= @passwordLength
BEGIN
SET @password = @password + SUBSTRING(@characters,CAST(ABS(CHECKSUM(NEWID()))*RAND(@count) as int)%LEN(@characters)+1,1)
SET @count = @count + 1
END
END

SELECT @password

END
_________________________________________________
DECLARE @OutString VARCHAR(20)
exec dbo.aesp_GenerateRandomString 1,1,1,null,8,@OutString OUT
PRINT ‘Hi- ‘ + @OutString

______________________________________

The above stored procedure will give you a complex random sting every time.

Happy programming.Keep learning.

Pawan Kumar

Pawankkmr@hotmail.com

CHECK WHETHER ALL CHARACTERS IN THE INPUT STRING ARE OF UPPER CASE OR NOT.
IF YES THEN RETURN 1
ELSE
RETURN -1

ALTER PROC CHECKSTRINGFORCAPTIALLETTERS
(
@str Varchar(250)
)
AS
BEGIN
DECLARE @return AS INT , @position AS INT , @MyValue INT
SET @position = 1
SET @return = -1
SET @MyValue = 0
WHILE @position <= DATALENGTH(@str)
BEGIN
IF ASCII(SUBSTRING(@str, @position, 1)) BETWEEN 65 AND 90
SET @return = 0
ELSE
SET @return = 1

SET @position = @position + 1
PRINT @return

IF @return = 1
BEGIN
SET @MyValue = -1
GOTO Himanshu
END
ELSE
BEGIN
SET @MyValue = 1
END
END
Himanshu: SELECT @MyValue
END

EXEC CHECKSTRINGFORCAPTIALLETTERS ‘HIMsNSHU’

Pawan Kumar
Pawankkmr@hotmail.com

In my last article on delete duplicate rows i have used primary key to remove duplicate rows from the datatable

DELETE DUPLICATE ROWS FROM A TABLE IN SQL SERVER 2005 — Last article on duplicate rows

One can use the below query if the primary key is not there in the table.

SET ROWCOUNT 1

SELECT * FROM EMPLOYEE
WHILE @@ROWCOUNT > 0
DELETE FROM EMPLOYEE WHERE EMPNAME IN (SELECT DISTINCT EMPNAME FROM EMPLOYEE GROUP BY EMPNAME HAVING COUNT(*) > 1)

SET ROWCOUNT 0

Pawan Kumar

Pawankkmr@hotmail.com

WAITFOR FUNCTION IN SQL SERVER 2005

It blocks the execution of a batch, stored procedure, or transaction until a specified time or time interval is reached,
or a specified statement modifies or returns at least one row.

Example of WAITFOR

WAITFOR ’00:00:10′
SELECT * FROM EMPLOYEE

GO

Notes:

1.WAITFOR will not work with Cursors and Views.

2.The actual time delay may vary from the time specified in
time_to_pass, time_to_execute, or timeout and depends on the activity
level of the server.

3.The time counter starts when the thread associated with the
WAITFOR statement is scheduled. If the server is busy, the thread may
not be immediately scheduled; therefore, the time delay may be longer than the specified time.

4.When the query exceeds the querywait option, the WAITFOR statement argument can complete without running.
You can check all active and waiting processes from sp_who.

5.Each WAITFOR statement creates a thread.

If many WAITFOR statements are specified on the same server, many threads can be tied up waiting for these statements to run.
SQL Server monitors the number of threads associated with WAITFOR
statements, and randomly selects some of these threads to exit if the
server starts to experience thread starvation.

Pawan Kumar
Pawankkmr@hotmail.com

COALESCE STATEMENT TO CONCATENATE ALL THE ROWS OF A PARTICULAR COLUMN IN SQL SERVER 2005

–Create a table

CREATE TABLE EMPLOYEE
(
id INT IDENTITY(1,1) PRIMARY KEY NOT NULL,
EMPNAME VARCHAR(100),
SALARY DECIMAL
)

–Insert some data in the above table

SELECT * FROM EMPLOYEE

OUTPUT
__________________

1 Pawan 180000
2 Gaurav 80000
3 Saurabh 80000
4 Pawan 830000
5 Neelam 200000

–Try out the below Query

DECLARE @EMPNAME VARCHAR(1024)
SELECT @EMPNAME = COALESCE(@EMPNAME + ‘ , ‘, ”) + LTRIM(RTRIM(EMPNAME)) FROM EMPLOYEE
PRINT @EMPNAME

OUTPUT
_____________________

Pawan , Gaurav , Saurabh , Pawan , Neelam.

ONE MORE ALTERNATIVE IS THERE

DECLARE @EMPNAME VARCHAR(1024)
SELECT @EMPNAME = ISNULL(@EMPNAME + ‘ , ‘, ”) + LTRIM(RTRIM(EMPNAME)) FROM EMPLOYEE
PRINT @EMPNAME

OUTPUT
_____________________

Pawan , Gaurav , Saurabh , Pawan , Neelam.

NOTE : COALESCE will return the thing in varchar2 that means is the string is too large then it will not work.

Pawan Kumar
Pawankkmr@hotmail.com