Dear All,
Happy New Year to you and your family.
New function in DENALI SQL SERVER 2012 (CHOOSE)
Hello friends,
This is another post related to new functions in DENALI (SQL SERVER 2012).
So today I am going to discuss “CHOOSE” functions .By the name it is clear that it will choose value.
In a simple sentence you can say it array which give value according to index parameter.
Its syntax is very simple.
CHOOSE (VALUE,CHOICE1, CHOICE2….CHOICEN)
Below is example.
In the example we are passing month value in CHOOSE function and according to value it provide corresponding string value of month.
DECLARE @tblFriends AS TABLE (Prefix VARCHAR(10) ,
Fname VARCHAR(200),
LName VARCHAR(100),
DOB SMALLDATETIME)
INSERT INTO @tblFriends(Prefix,Fname,LName,DOB) VALUES('DR','Ravindra','Verma','1/1/1981')
INSERT INTO @tblFriends(Prefix,Fname,LName,DOB) VALUES('Mr','Rajul','Verma','3/3/1981')
INSERT INTO @tblFriends(Prefix,Fname,LName,DOB) VALUES('Sir','Suyash','Kokje','4/4/1981')
INSERT INTO @tblFriends(Prefix,Fname,LName,DOB) VALUES('Sardar','Ravindra','Dhariwal','2/2/1981')
INSERT INTO @tblFriends(Prefix,Fname,LName,DOB) VALUES('Pandit','Shyam','Purohit','5/5/1981')
INSERT INTO @tblFriends(Fname,LName,DOB) VALUES('Shrikant','Yadav','6/6/1981')
INSERT INTO @tblFriends(Fname,LName,DOB) VALUES('Sawan','Gupta','7/7/1981')
INSERT INTO @tblFriends(Fname,LName,DOB) VALUES('Pankaj','Tomar','8/8/1981')
INSERT INTO @tblFriends(Fname,LName,DOB) VALUES('Vaibhav','Joshi','9/9/1981')
INSERT INTO @tblFriends(Fname,LName,DOB) VALUES('Vikram','Chouhan','10/10/1981')
INSERT INTO @tblFriends(Fname,LName,DOB) VALUES('Subhash','Patidar','11/11/1981')
INSERT INTO @tblFriends(Fname,LName,DOB) VALUES('Vinay','Karma','12/12/1981')
SELECT CONCAT(Prefix,' ' ,FName, ' ',LName) AS ConcatName,
CHOOSE (datePart(m,DOB),'JAN','FEB','MAR','APR','MAY','JUN','JUL','AUG','SEP','OCT','NOV','DEC') AS DOBMONTH
FROM @tblFriends
so when you execute this script you will get following result.
I hope you enjoyed learning this.
ConcatName DOBMONTH
DR Ravindra Verma JAN
Mr Rajul Verma MAR
Sir Suyash Kokje APR
Sardar Ravindra Dhariwal FEB
Pandit Shyam Purohit MAY
Shrikant Yadav JUN
Sawan Gupta JUL
Pankaj Tomar AUG
Vaibhav Joshi SEP
Vikram Chouhan OCT
Subhash Patidar NOV
Vinay Karma DEC
You can view previous post related with this at
Thanks
Rajat Jaiswal
New Functions in SQL SERVER 2012 ,DENALI (CONCAT)
Hello friends,
This is another post related to new functions in DENALI (SQL SERVER 2012).
So today I am going to discuss “CONCAT” functions .By the name it is clear that it has concatenation property.
Its syntax is very simple.
CONCAT (string1, string2….StringN)
The best part is it handles NULL value also we don’t have to work on NULL value explicitly.
Below is example.
DECLARE @tblFriends AS TABLE (Prefix VARCHAR(10) ,
Fname VARCHAR(200),
LName VARCHAR(100),
DOB SMALLDATETIME)
INSERT INTO @tblFriends(Prefix,Fname,LName,DOB) VALUES(‘DR’,’Ravindra’,’Verma’,’1/1/1981′)
INSERT INTO @tblFriends(Prefix,Fname,LName,DOB) VALUES(‘Mr’,’Rajul’,’Verma’,’3/3/1981′)
INSERT INTO @tblFriends(Prefix,Fname,LName,DOB) VALUES(‘Sir’,’Suyash’,’Kokje’,’4/4/1981′)
INSERT INTO @tblFriends(Prefix,Fname,LName,DOB) VALUES(‘Sardar’,’Ravindra’,’Dhariwal’,’2/2/1981′)
INSERT INTO @tblFriends(Prefix,Fname,LName,DOB) VALUES(‘Pandit’,’Shyam’,’Purohit’,’5/5/1981′)
INSERT INTO @tblFriends(Fname,LName,DOB) VALUES(‘Shrikant’,’Yadav’,’6/6/1981′)
INSERT INTO @tblFriends(Fname,LName,DOB) VALUES(‘Sawan’,’Gupta’,’7/7/1981′)
INSERT INTO @tblFriends(Fname,LName,DOB) VALUES(‘Pankaj’,’Tomar’,’8/8/1981′)
INSERT INTO @tblFriends(Fname,LName,DOB) VALUES(‘Vaibhav’,’Joshi’,’9/9/1981′)
INSERT INTO @tblFriends(Fname,LName,DOB) VALUES(‘Vikram’,’Chouhan’,’10/10/1981′)
INSERT INTO @tblFriends(Fname,LName,DOB) VALUES(‘Subhash’,’Patidar’,’11/11/1981′)
INSERT INTO @tblFriends(Fname,LName,DOB) VALUES(‘Vinay’,’Karma’,’12/12/1981′)
SELECT CONCAT(Prefix,’ ‘ ,FName, ‘ ‘,LName) AS ConcatName
FROM @tblFriends
When you run the above script you will get following resultset.
ConcatName
DR Ravindra Verma
Mr Rajul Verma
Sir Suyash Kokje
Sardar Ravindra Dhariwal
Pandit Shyam Purohit
Shrikant Yadav
Sawan Gupta
Pankaj Tomar
Vaibhav Joshi
Vikram Chouhan
Subhash Patidar
Vinay Karma
I hope you enjoyed learning this.
You can view previous post related with this at https://indiandotnet.wordpress.com/2011/12/23/new-function-in-sql-server-denali-iif/
Thanks
Rajat Jaiswal
A new milestone 125th post on Indiandotnet.wordpress.com so far so good
Dear Friends,
This post is different post from my regular technical articles and it is my 125th post on Indiandotnet.wordpress.com.
I would like to thank all of my friends, family members, colleges & most valuable the reader of this blog who continuously giving feedback and improving my skill also.
So thank you very very much.
Keep reading, Keep commenting, and keep giving feedback.
I need more improvement and I know you people will help me in sharing my thoughts.
God bless you all.
Happy Holidays.
Merry Christmas.
Thanks
Rajat Jaiswal
Unable to Login SQL SERVER forgot password
Hello Friends,
I have an interesting thing to share.
Just day before yesterday I forgot my Denali server password & server not having windows Authentication.
I am unable to login to the server. So I just tried a trick and it worked successfully.
I just open the service account of SQL SERVER and change its login to Local System Account as shown in below fig.
And this trick worked successfully and now I can login using windows account.
I hope this will help to you also if by chance you forgot your password to and same situation exists.
Thanks
Rajat Jaiswal
New function in SQL SERVER DENALI (IIF)
Hello friends,
As a continuation of Denali exploration series
I would like to share some new functions also here. I am sure you like shorthand function “IIF”. Denali now providing IIF function so Instead of writing CASE statement you can easily use IIF function.
Its syntax is same as we doing in our other programming language.
IIF (Condition, true value, false value)
Example:-
DECLARE @genderMale AS BIT
SET @genderMale = 1 -- where SELECT IIF (@genderMale = 1,’ Smart’, ‘Beautiful’) as property
I hope you will use it soon.
Happy Holidays!!!!
Thanks
Rajat Jaiswal
Simplest way of providing Comma seperated column values in SQL SERVER
Hello friends,
Most of the time we require comma separated value of a column according to some relation.
The simplest way of this to create a user define function and create a comma separated value according to their respective other relation value.
Just understand with below example.
Suppose we have a normalize database design in which we have two table
1. State: – State Master is table which contains State of India. And it has following columns
a) StateId (Auto incremental value)
b) Name (State Name)
2. City: – Which contain cities of States according to their respective relation. It has following columns
a) CityId ( Auto incremental Id)
b) StateId (Reference of State table Id
c) City name
Please take a look on below fig with some value of city and state
Now suppose we require following result
Then we are going to use here a simple operator which is “FOR XML Path(‘’)” option so below is use of XML path.
Please run the below query for desire result.
DECLARE @StateCities AS TABLE (StateId INT,
StateName VARCHAR(500),
Cities VARCHAR(5000))
INSERT INTO @StateCities(StateId,StateName )
SELECT StateId,StateName
FROM State
UPDATE tmp
SET Cities = (SELECT c.CityName + ‘,’
FROM dbo.City c
WHERE c.StateId = tmp.StateId
ORDER BY c.CityName
FOR XML PATH (”))
FROM @StateCities tmp
SELECT * FROM @StateCities WHERE Cities IS NOT NULL
.
I hope it would help you.
Happy Holidays
Thanks & best regards,
Rajat Jaiswal
Paging easy in “DENALI” (OFFSET & FETCH OPTION)
Hello friends,
Let’s talk today one more important clause of “DENALI” which is very helpful in fetching records, paging are “OFFSET” & “FETCH”.
It is helpful in performance improvement also.
Just go through below example and it will clear more
Suppose I have state table which have all the state rows of INDIA.
Now with offset clause I can say from where I can start row fetching and FETCH NEXT will give me option how many rows I want.
So suppose I want 5 records after and skip first 2 rows then see below SQL statement
SELECT * FROM state ORDER BY StateName OFFSET 2 ROWS FETCH NEXT FROM 5 ROWS ONLY
Hope you enjoyed it and will use sooner or later.
Thanks & Regards
Rajat Jaiswal
Learn & Share ASP.NET & SQL SERVER 