[T-SQL] Advanced Server Configuration

“How I check all the advanced configuration of the SQL Server?”

EXEC sp_configure 'Show Advanced Options', 1;
GO
RECONFIGURE;
GO
EXEC sp_configure;

[T-SQL] Identity Columns

Allowing inserts to identity columns:

If you are inserting data from some other source to a table with an identity column and you need to ensure you retain the indentity values, you can temporarily allow inserts to the indentity column. Without doing so explicitly you will receive an error if you attempt to insert a value into the indentity column. For example, if I have a table named MYTABLE and I want to allow inserts into it's identity column, I can execute the following:


set identity_insert mytable on

Once you execute the command you will be able to insert values into the table's identity column. This will stay in effect in until you turn it off by executing the following:


set identity_insert mytable off

Be aware that at any time, only a single table in a session can have the identity_insert set to on. If you attempt to enable this for a table and another table already has this enabled, you will receive an error and will not be able to do so until you first turn this off for the other table. Also, if the value used for the indentity is larger than the current identity value then the new value will be used for the identity seed for the column.



Reseeding the identity value:

You can reseed the indentity value, that is, to have the identity values reset or start at a new predefined value by using DBCC CHECKIDENT. For example, if I have a table named MYTABLE and I want to reseed the indentity column to 30 I would execute the following:


dbcc checkident (mytable, reseed, 30)

If you wanted to reseed the table to start with an identity of 1 with the next insert then you would reseed the table's identity to 0. The identity seed is what the value is currently at, meaning that the next value will increment the seed and use that. However, one thing to keep in mind is that if you set the identity seed below values that you currently have in the table, that you will violate the indentity column's uniqueness constraint as soon as the values start to overlap. The identity value will not just “skip” values that already exist in the table.

[T-SQL] Rebuild All Index with Fill Factor

DECLARE @TableName VARCHAR(255)
DECLARE @sql NVARCHAR(500)
DECLARE @fillfactor INT
SET @fillfactor = 80
DECLARE TableCursor CURSOR FOR
SELECT OBJECT_SCHEMA_NAME([object_id])+'.'+name AS TableName
FROM sys.tables
OPEN TableCursor
FETCH NEXT FROM TableCursor INTO @TableName
WHILE @@FETCH_STATUS = 0
BEGIN
SET @sql = 'ALTER INDEX ALL ON ' + @TableName + ' REBUILD WITH (FILLFACTOR = ' + CONVERT(VARCHAR(3),@fillfactor) + ')'
EXEC (@sql)
FETCH NEXT FROM TableCursor INTO @TableName
END
CLOSE TableCursor
DEALLOCATE TableCursor
GO

[Cognos] Certificate Questions

http://www.cognos-bi.info/certification.html

[T-Sql] Deleting Duplicate Records

Deleting Duplciate Records on any specific crieteria


DECLARE @DATE AS VARCHAR(19)
SET @DATE=(SELECT CONVERT(VARCHAR(19),ISNULL(MAX(TIMEMODIFIED),'01/01/2000'),121) FROM ACDW.DBO.QB_SALESREP)

INSERT INTO QB_SALESREP SELECT * FROM QB_SALESREP_TEMP WHERE TIMEMODIFIED>@DATE

DELETE ACDW.DBO.QB_SALESREP
FROM ACDW.DBO.QB_SALESREP TSR INNER JOIN
(
SELECT LISTID,MIN(TIMEMODIFIED) TIMEMODIFIED
FROM ACDW.DBO.QB_SALESREP
GROUP BY LISTID
HAVING COUNT(*)>1
) DD ON TSR.LISTID=DD.LISTID AND TSR.TIMEMODIFIED=DD.TIMEMODIFIED

What is Denali?

“What is Denali?”

Denali is code name of SQL Server 2011.

Here is the list of the code name of other versions of SQL Server.

In 1988, Microsoft released its first version of SQL Server. It was developed jointly by Microsoft and Sybase for the OS/2 platform.

1993 – SQL Server 4.21 for Windows NT
1995 – SQL Server 6.0, codenamed SQL95
1996 – SQL Server 6.5, codenamed Hydra
1999 – SQL Server 7.0, codenamed Sphinx
1999 – SQL Server 7.0 OLAP, codenamed Plato
2000 – SQL Server 2000 32-bit, codenamed Shiloh (version 8.0)
2003 – SQL Server 2000 64-bit, codenamed Liberty
2005 – SQL Server 2005, codenamed Yukon (version 9.0)
2008 – SQL Server 2008, codenamed Katmai (version 10.0)
2010 – SQL Server 2008 R2, Codenamed Kilimanjaro (aka KJ)
Next – SQL Server 2011, Codenamed Denali

MCTS : SQL SERVER 2005

Yesterday i cleared 70-431 which i was planning to do since 3 years back.
THANKS to ALMIGHTY ALLAH

SQL Azure and Normal DB Server

Good article related to it.

http://msdn.microsoft.com/en-us/library/ee336241.aspx

[T-SQL] New Features of SQL SERVER 2008

http://technet.microsoft.com/en-us/library/cc721270.aspx

[T-SQL] General Insert SP

set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
GO
ALTER Procedure [dbo].[generalInsert] (@SourceTableName varchar(100), @TargetTableName varchar(100) = '', @WhereClause varchar(8000) = '') with recompile as
Begin
-- Description : Generate Insert queries with data
-- Created By : Farhan Iqbal
-- Dated : 3th March, 2007
-- Example : Exec GeneralInsert 'ACCT_Orders', 'ACCT_Orders', 'Where Order_Id = 1'
Declare @Fields varchar(8000), @Data varchar(max), @SQL nvarchar(max)
set @Fields = ' '
set @Data = ''
if len(ltrim(rtrim(@TargetTableName))) = 0 set @TargetTableName = @SourceTableName
select @Fields = @Fields + ','+Column_Name
from INFORMATION_SCHEMA.columns
where Table_Name = @SourceTableName
order by Ordinal_Position
select @Data = @Data + ', '+ Case
when Data_Type = 'uniqueidentifier' then +'''''''+isNull(['+Column_Name+'], ''Null'')+'''''''
when Data_Type = 'varbinary' then +'''''''+isNull(['+Column_Name+'], ''Null'')+'''''''
when Data_Type = 'varchar' then +'''''''+isNull(replace(['+Column_Name+'], '''''''', ''''''''''''), '''')+'''''''
when Data_Type = 'timestamp' then +'''''''+isNull(['+Column_Name+'], ''Null'')+'''''''
when Data_Type = 'sysname' then +'''''''+isNull(['+Column_Name+'], ''Null'')+'''''''
when Data_Type = 'sql_variant' then +'''''''+isNull(['+Column_Name+'], ''Null'')+'''''''
when Data_Type = 'text' then +'''''''+isNull(replace(convert(varchar(max), ['+Column_Name+']), '''''''', ''''''''''''), '''')+'''''''
when Data_Type = 'ntext' then +'''''''+isNull(replace(convert(nvarchar(max), ['+Column_Name+']), '''''''', ''''''''''''), '''')+'''''''
when Data_Type = 'image' then +''''''''''
when Data_Type = 'xml' then +''''''''''
when Data_Type = 'nvarchar' then +'''''''+isNull(replace(['+Column_Name+'], '''''''', ''''''''''''), '''')+'''''''
when Data_Type = 'nchar' then +'''''''+isNull(replace(['+Column_Name+'], '''''''', ''''''''''''), '''')+'''''''
when Data_Type = 'char' then +'''''''+isNull(replace(['+Column_Name+'], '''''''', ''''''''''''), '''')+'''''''
when Data_Type = 'datetime' then +'''''''+isNull(Convert(Varchar, ['+Column_Name+']), ''Null'')+'''''''
when Data_Type = 'smalldatetime' then +'''''''+isNull(Convert(Varchar, ['+Column_Name+']), ''Null'')+'''''''
when Data_Type = 'bigint' then +'''+isNull(Convert(Varchar, ['+Column_Name+']), ''Null'')+'''
when Data_Type = 'binary' then +'''+isNull(Convert(Varchar, ['+Column_Name+']), ''Null'')+'''
when Data_Type = 'bit' then +'''+isNull(Convert(Varchar, ['+Column_Name+']), ''Null'')+'''
when Data_Type = 'decimal' then +'''+isNull(Convert(Varchar, ['+Column_Name+']), ''Null'')+'''
when Data_Type = 'float' then +'''+isNull(Convert(Varchar, ['+Column_Name+']), ''Null'')+'''
when Data_Type = 'int' then +'''+isNull(Convert(Varchar, ['+Column_Name+']), ''Null'')+'''
when Data_Type = 'money' then +'''+isNull(Convert(Varchar, ['+Column_Name+']), ''Null'')+'''
when Data_Type = 'numeric' then +'''+isNull(Convert(Varchar, ['+Column_Name+']), ''Null'')+'''
when Data_Type = 'real' then +'''+isNull(Convert(Varchar, ['+Column_Name+']), ''Null'')+'''
when Data_Type = 'smallint' then +'''+isNull(Convert(Varchar, ['+Column_Name+']), ''Null'')+'''
when Data_Type = 'tinyint' then +'''+isNull(Convert(Varchar, ['+Column_Name+']), ''Null'')+'''
when Data_Type = 'smallmoney' then +'''+isNull(Convert(Varchar, ['+Column_Name+']), ''Null'')+''' else '' end
from INFORMATION_SCHEMA.columns
where Table_Name = @SourceTableName
order by Ordinal_Position
select @SQL = 'Select ''Insert into '+@TargetTableName+' ('+ Substring (@Fields, 3, len(@Fields))+')
values ('+ substring(rtrim(ltrim(@Data)), 3, len(@Data))+ ')'' from '+@SourceTableName +' '+@WhereClause
execute sp_executesql @SQL
End

[T-SQL] Recursive CTE Contd...

Using recursive CTE normally if level exceeds 100 then it will give statement
"The statement terminated. The maximum recursion 100 has been exhausted before statement completion"

So change the below line


SELECT @PARENT_ID=Min(COALESCE(PARENTPRODUCTGROUP_ID,ID)) FROM RECURSIVECTE option (maxrecursion 32767) ;

[T-SQL] Recursive CTE

Function implemented with normal recursive logic but have limit for 32 levels

CREATE FUNCTION [dbo].[OLD_RETURN_ROOTID](@CHILD_ID INT)
RETURNS INT
AS
BEGIN
-- Declare the return variable here
DECLARE @PARENT_ID bigINT
SET @PARENT_ID = NULL
SET @PARENT_ID=
(
SELECT PARENTPRODUCTGROUP_ID FROM PRODUCTGROUP
WHERE ID=@CHILD_ID AND SYSTEM_ID=2
)
IF (ISNULL(@PARENT_ID,0)=0)
SET @PARENT_ID=@CHILD_ID
ELSE
SET @PARENT_ID=(SELECT DBO.RETURN_ROOTID(@PARENT_ID))
RETURN(@PARENT_ID)
END

Function implemented with recursive CTE logic - no limits


CREATE FUNCTION [dbo].[RETURN_ROOTID](@CHILD_ID INT)
RETURNS INT
AS
BEGIN
-- Declare the return variable here
DECLARE @PARENT_ID INT;
SET @PARENT_ID = NULL;
WITH RECURSIVECTE AS (
SELECT ID,PARENTPRODUCTGROUP_ID FROM PRODUCTGROUP WHERE ID=@CHILD_ID
UNION ALL
SELECT CO.ID,CO.PARENTPRODUCTGROUP_ID FROM PRODUCTGROUP AS CO
INNER JOIN RECURSIVECTE AS CTE
ON(CO.PARENTPRODUCTGROUP_ID =CTE.ID)
)
SELECT @PARENT_ID=Min(COALESCE(PARENTPRODUCTGROUP_ID,ID)) FROM RECURSIVECTE;
IF (@PARENT_ID=@CHILD_ID)
SET @PARENT_ID=@CHILD_ID
ELSE
SET @PARENT_ID=(SELECT DBO.RETURN_ROOTID(@PARENT_ID))
RETURN(@PARENT_ID)

[T-SQL] Find Statistics Update Date – Update Statistics

SELECT name AS index_name,
STATS_DATE(OBJECT_ID, index_id) AS StatsUpdated
FROM sys.indexes
WHERE OBJECT_ID = OBJECT_ID('LEARNER')

[T-SQL] Get Date of All Weekdays or Weekends of the Year

DECLARE @Year AS INT,
@FirstDateOfYear DATETIME,
@LastDateOfYear DATETIME
-- You can change @year to any year you desire
SELECT @year = 2010
SELECT @FirstDateOfYear = DATEADD(yyyy, @Year - 1900, 0)
SELECT @LastDateOfYear = DATEADD(yyyy, @Year - 1900 + 1, 0)
-- Creating Query to Prepare Year Data
;WITH cte AS (
SELECT 1 AS DayID,
@FirstDateOfYear AS FromDate,
DATENAME(dw, @FirstDateOfYear) AS Dayname
UNION ALL
SELECT cte.DayID + 1 AS DayID,
DATEADD(d, 1 ,cte.FromDate),
DATENAME(dw, DATEADD(d, 1 ,cte.FromDate)) AS Dayname
FROM cte
WHERE DATEADD(d,1,cte.FromDate) < @LastDateOfYear
)
SELECT FromDate AS Date, Dayname
FROM CTE
WHERE DayName IN ('Saturday','Sunday')
/*
WHERE DayName IN ('Saturday,Sunday') -- For Weekend
WHERE DayName NOT IN ('Saturday','Sunday') -- For Weekday
WHERE DayName LIKE 'Monday' -- For Monday
WHERE DayName LIKE 'Sunday' -- For Sunday
*/
OPTION (MaxRecursion 370)

[T-SQL] Find Location of Data File

SELECT SUBSTRING(physical_name, 1,CHARINDEX(N'master.mdf',LOWER(physical_name)) - 1) DataFileLocationFROM master.sys.master_filesWHERE database_id = 1 AND FILE_ID = 1

[T-SQL] Generate Data script of any specific table

http://vyaskn.tripod.com/code/generate_inserts_2005.txt

[T-SQL] Application Locks (or Mutexes) in SQL Server 2005

Application locks aren't a well known area of locking in SQL Server, but they can be very useful for special scenarios. They work in an analogous way to the lock() construct in .Net and are basicaly user defined mutexes in SQL Server.


An application lock is a bit different than other kinds of SQL Server locks though. While other locks lock schema or data, application locks lock a part of your code. There are 2 stored procedure that are used for this: sp_getapplock and sp_releaseapplock.

USE AdventureWorks;

BEGIN TRANSACTION
DECLARE @res INT
EXEC @res = sp_getapplock
-- unique name nvarchar(255), truncated to 255 if longer
@Resource = 'This a Lock ID',
-- Valid values: Shared, Update, IntentShared, IntentExclusive, Exclusive
@LockMode = 'Exclusive',
-- Scope of the lock: Transaction or Session
@LockOwner = 'Transaction',
-- Timeout in miliseconds
@LockTimeout = 60000,
-- db principal that has access permisions
@DbPrincipal = 'public'

-- we can see our lock in in the DMV with resource_type = 'APPLICATION'
SELECT resource_type, request_mode,
resource_description
FROM sys.dm_tran_locks

-- 0 and 1 are valid return values
IF @res NOT IN (0, 1)
BEGIN
RAISERROR ( 'Unable to acquire Lock', 16, 1 )
END
ELSE
BEGIN
SELECT * FROM Person.Address
EXEC @res = sp_releaseapplock
@Resource = 'This a Lock ID',
@DbPrincipal = 'public',
@LockOwner = 'Transaction'
END
COMMIT
If an application lock owner is a transaction, the lock gets automatically released when the transaction ends.

However to be able to call sp_getapplock a user calling the stored procedure must meet one of these conditions:

is dbo
is in the db_owner role
is the DB Principal ID (e.g. guest)
is in the DB Principal ID role (e.g. public)
Best understood with an example...
Note that application locks aren't taken on any data like standard locks. Let's illustrate with some code derived from above code. We need 3 batches:

Batch 1 Batch 2 Batch 3
BEGIN TRAN
EXEC @res = sp_getapplock ....
SELECT *
FROM Person.Address
BEGIN TRAN
EXEC @res = sp_getapplock ....
SELECT *
FROM Person.Address
SELECT *
FROM Person.Address

Run batch 1: Begin a transaction, get an application lock and select data from Person.Address, but don't release the lock nor end the transaction.

Run batch 2: Begin a transaction, which will try to get an application lock but it won't be able to since the application lock with the same name (@Resource) already exists. The batch will wait until the lock with the existing name (@Resource) is released or the transaction is ended which automatically releases the application lock.

Run batch 3: This will always run disregarding the application lock altogether since there are no real locks on data.

... and of course with an another example
A great example of application locks is a typical business logic problem of inserting data if it doesn't exist and update it if it does. I've written about this in this blog post. In it I looked at the locking being held and the post comments have great value. But however you try to make this work you'll always run into some concurrency issues. If you put the whole thing into a transaction then you’ll get into situations with violating PK constraints when inserting data. Another option is to use XLOCK and HOLDLOCK hints in a transaction but this can result in a deadlock which is even worse that the first situation. Application locks prove to be a very good solution to this:

USE tempdb
GO

IF OBJECT_ID('AppLockTest') IS NOT NULL
DROP TABLE AppLockTest
IF OBJECT_ID('spTestAppLocks') IS NOT NULL
DROP PROC spTestAppLocks
GO

CREATE TABLE AppLockTest( id INT, val VARCHAR(10))
INSERT INTO AppLockTest
SELECT 1, 'value 1' UNION ALL
SELECT 2, 'value 2'
GO

CREATE PROC spTestAppLocks
@id INT,
@val VARCHAR(10)
AS
BEGIN TRANSACTION
DECLARE @res INT
EXEC @res = sp_getapplock
@Resource = 'Upsert_app_lock',
@LockMode = 'Exclusive',
@LockOwner = 'Transaction',
@LockTimeout = 60000,
@DbPrincipal = 'public'
PRINT 'LOCK ACQUIRED: start our upsert'
-- 0 and 1 are valid return values
IF @res NOT IN (0, 1)
BEGIN
RAISERROR ( 'Unable to acquire Lock', 16, 1 )
END
ELSE
BEGIN
-- just to see that the stored procedure will wait
-- for the completion of the previous one.
WAITFOR DELAY '00:00:10'

IF EXISTS (SELECT * FROM AppLockTest WHERE id = @id)
BEGIN
UPDATE AppLockTest
SET val = @val
WHERE id = @id
END
ELSE
BEGIN
INSERT AppLockTest
SELECT @id, @val
END
EXEC @res = sp_releaseapplock
@Resource = 'Upsert_app_lock',
@DbPrincipal = 'public',
@LockOwner = 'Transaction'
PRINT 'LOCK RELEASED: end our upsert'
END
COMMIT
GO

-- Run in Query Window 1
EXEC spTestAppLocks 1, 'val 1a'

-- Run in Query Window 2
EXEC spTestAppLocks 1, 'val 1b'
If you run the stored procedure in Query Window 1 and after 5 second your run the stored procedure in Query Window 2 you'll see that the whole code between sp_getapplock and sp_releaseapplock won't execute until the stored procedure in Query Window 1 finishes.

If all of your update/insert logic follows uses this pattern then you'll never get concurrency issues. Of course this method doesn't apply to all environments, so if you can use it requires some testing and design considerations.

Conclusion
While not often used they can come in handy in complex business logic cases. You can see that an application lock simply locks the part of your T-SQL code and not actual data. So to achieve mutual exclusion (mutex), all access has to follow this same lock acquisition pattern using sp_getapplock and sp_releaseapplock. This is of course best achieved with stored procedures which is another plus in favor of them over ad-hoc (parameterized) queries.

[T-SQL] How To Get a List of Columns using the "sys.columns" View?

If you have an existing table, but you don't remember what are the columns defined in the table, you can use the "sys.columns" system view to get a list of all columns of all tables in the current database.
In order to a list of columns of a single table, you need to join sys.columns and sys.tables as shown in the tutorial example below: SELECT * FROM sys.columns c, sys.tables t
WHERE c.object_id = t.object_id
AND t.name = 'tip'
GO
object_id name column_id user_type_id max_length
2073058421 id 1 56 4
2073058421 subject 2 167 80
2073058421 description 3 167 256
2073058421 create_date 4 61 8
You can see the column names easily from the sys.columns view. But you can only see the column type IDs. This requires another join to get the column type names. You may try the "sp_columns" stored procedure to get a better list of columns shown in the next tutorial.

[T-SQL] How To Get a List of Columns using the "sys.columns" View?

If you have an existing table, but you don't remember what are the columns defined in the table, you can use the "sys.columns" system view to get a list of all columns of all tables in the current database.
In order to a list of columns of a single table, you need to join sys.columns and sys.tables as shown in the tutorial example below: SELECT * FROM sys.columns c, sys.tables t
WHERE c.object_id = t.object_id
AND t.name = 'tip'
GO
object_id name column_id user_type_id max_length
2073058421 id 1 56 4
2073058421 subject 2 167 80
2073058421 description 3 167 256
2073058421 create_date 4 61 8
You can see the column names easily from the sys.columns view. But you can only see the column type IDs. This requires another join to get the column type names. You may try the "sp_columns" stored procedure to get a better list of columns shown in the next tutorial.

Configure Users in Cognos 8.x

Step 1: Go to properties
Step 2: Go to Permissions Tab
Step 3: Click check box for Override the access permission
Step 4: If your desired group/user is not displayed in the list then you need to add that

into the list , for that i will explain steps afterwards.
Step 5: Select the desired user/group.
Step 6: Appropiate status will be dispayed in right window
Step 7: Update settings and then click apply.
Step 8: Click ok and verify

For adding user:
Step 1: Click add link
Step 2: Click default
Step 3: Click Users
Step 4: Click checkbox over top namely "Show users in the list"
Step 5: All users will be dispayed , click the checkbox for required users
Step 6: Click button => , so that selected user will be displayed on right hand side grid.
Step 7: Click Ok

For adding group
Step 1: Click add link
Step 2: Click Cognos
Step 3: Click checkbox over top namely "Show users in the list"
Step 4: All groups will be dispayed , click the checkbox for required groups
Step 5: Click button => , so that selected group will be displayed on right hand side grid.
Step 6: Click Ok

[Cognos] Script to hide date controls on combo selection

List = document.formWarpRequest._oLstChoicesparamShowAll;


List.options[0].selected = true;
List.onchange=function()
{

if (List.value=='N')
{
document.getElementById('dateEditBoxToDate').style.display="inline";
document.getElementById('dateEditBoxFromDate').style.display="inline";
document.getElementById('ad').style.display="inline";
document.getElementById('ad1').style.display="inline";
document.getElementById('ad2').style.display="inline";
document.getElementById('ad3').style.display="inline";
}

else
{
document.getElementById('dateEditBoxFromDate').style.display="none";
document.getElementById('dateEditBoxToDate').style.display="none";
document.getElementById('ad').style.display="none";
document.getElementById('ad1').style.display="none";
document.getElementById('ad2').style.display="none";
document.getElementById('ad3').style.display="none";
}

};

[Cognos] Script for updating caption of Finish Button

var pstr1=document.getElementsByTagName('Button');

for(var i=0;i {
if(pstr1[i]!=null && pstr1[i].name !=null)
{
if (pstr1[i].name!=null pstr1[i].name!='')
{
if(pstr1[i].name.substring(0,6)=='finish')
{
pstr1[i].innerHTML ="Run";
}


}
}
}

[T-SQL] Proper case function in SQL Server

CREATE FUNCTION PROPERCASE
(
--The string to be converted to proper case
@input varchar(8000)
)
--This function returns the proper case string of varchar type
RETURNS varchar(8000)
AS
BEGIN
IF @input IS NULL
BEGIN
--Just return NULL if input string is NULL
RETURN NULL
END
--Character variable declarations
DECLARE @output varchar(8000)
--Integer variable declarations
DECLARE @ctr int, @len int, @found_at int
--Constant declarations
DECLARE @LOWER_CASE_a int, @LOWER_CASE_z int, @Delimiter char(3), @UPPER_CASE_A int, @UPPER_CASE_Z int
--Variable/Constant initializations
SET @ctr = 1
SET @len = LEN(@input)
SET @output = ''
SET @LOWER_CASE_a = 97
SET @LOWER_CASE_z = 122
SET @Delimiter = ' '
SET @UPPER_CASE_A = 65
SET @UPPER_CASE_Z = 90
WHILE @ctr <= @len
BEGIN
--This loop will take care of reccuring white spaces
WHILE CHARINDEX(SUBSTRING(@input,@ctr,1), @Delimiter) > 0
BEGIN
SET @output = @output + SUBSTRING(@input,@ctr,1)
SET @ctr = @ctr + 1
END
IF ASCII(SUBSTRING(@input,@ctr,1)) BETWEEN @LOWER_CASE_a AND @LOWER_CASE_z
BEGIN
--Converting the first character to upper case
SET @output = @output + UPPER(SUBSTRING(@input,@ctr,1))
END
ELSE
BEGIN
SET @output = @output + SUBSTRING(@input,@ctr,1)
END
SET @ctr = @ctr + 1
WHILE CHARINDEX(SUBSTRING(@input,@ctr,1), @Delimiter) = 0 AND (@ctr <= @len)
BEGIN
IF ASCII(SUBSTRING(@input,@ctr,1)) BETWEEN @UPPER_CASE_A AND @UPPER_CASE_Z
BEGIN
SET @output = @output + LOWER(SUBSTRING(@input,@ctr,1))
END
ELSE
BEGIN
SET @output = @output + SUBSTRING(@input,@ctr,1)
END
SET @ctr = @ctr + 1
END
END
RETURN @output
END