Dynamic SQL in sql server

Dynamic SQL in sql server:

Most of the cases, our requirements will be dynamic. Sometimes, the person will request for one column to be fetched from the database, may be two columns. The decisions will be taken at run time.

I used to face a requirement to fetch the data based on the login user. userA needs 5 columns and userB needs 2 columns.

I need to have a common code to satisfy both the requirement. Such kind of scenario can be handled using dynamic SQL.

Query will be generated at the run time and it will be executed to fetch the result. The dynamically created query will be executed by a special system stored procedure namely sp_executeSQL. Let's see a small code snippet on this.

-- Creating a table named venkatTable
drop table venkatTable
create table venkatTable(id int identity(1,1) primary key, nam varchar(100))
insert into venkatTable values('Venkatesan Prabu')
insert into venkatTable values('Subashini')
insert into venkatTable values('Jayakantham')
insert into venkatTable values('Arunachalam')
insert into venkatTable values('Santhi')
select * from venkattable

-- Am declaring three variables @val3 will be a query created.
-- It should be executed dynamically using sp_executesql

declare @val1 varchar(100)
declare @val2 varchar(100)
declare @val3 nvarchar(100)

set @val1 ='id'
set @val2='nam'

set @val3= 'select '+ @val1 + ','+ @val2 + ' from venkattable'
print @val3

exec sp_executesql @val3

Thanks and Regards,

Venkatesan Prabu .J

Inner Join in SQL Server

Inner join in SQL Server:

Usually, in a normalized database the values related to a particular column will be referenced in other table.

Considering, there is company with 1000 engineers. In storing the employees, instead of storing engineer as the designation for each employee. Normal forms advised
us to store the engineer value in a small table with id as reference. This id can be stored in the employee table.

So that, it will improve the storage.

Now, I got all the data segregated into different tables and I need to join it into a single result set.

In that case,we will opt join operations in SQL Server. It will enable you to consolidate the data and provide you in an expected format. There are various joins available in SQL Server.

Let's see inner join option in SQL Server. This will join the table mutually based on a common column. The syntax looks like,

table1 inner join table2 on table1.column=table2.column

Let's see some example to learn the join operation in sql server.

-- lets see a chatting messenger example,
-- Am creating three tables, 1 holds the name of the person and 2 holds the mapping between the friends.
--Considering am giving a friends request to another user

drop table venkatTable
create table venkatTable(id int identity(1,1) primary key, nam varchar(100))
insert into venkatTable values('Venkatesan Prabu')
insert into venkatTable values('Subashini')
insert into venkatTable values('Jayakantham')
insert into venkatTable values('Arunachalam')
insert into venkatTable values('Santhi')

drop table friendTable
create table friendTable(id int, friendid int, valueStatus int)
insert into friendTable values(1,2,0)
insert into friendTable values(1,3,1)
insert into friendTable values(2,4,0)
insert into friendTable values(2,3,2)

drop table status
create table status(id int, status varchar(100))
insert into status values(1,'Approved')
insert into status values(0,'pending')
insert into status values(2,'DisApproved')

Now, Lets join tableA, TableB and TableC directly :

select a.nam,b.id,b.friendid,c.status
from venkatTable a inner join friendTable b on a.id=b.id
inner join status c on b.valueStatus = c.id




Joining tableA and TableB on another combination:

select a.nam,b.id,b.friendid
from venkatTable a inner join friendTable b on b.friendid=a.id

Fetching the name, friendname and Status (By joining three tables) :
select distinct t.nam,t1.nam,t.status,t.id,t.friendid,t1.id ,t1.friendid from
(
select row_number() over(order by b.id,b.friendid) as r1, a.nam,b.id,b.friendid,c.status
from venkatTable a inner join friendTable b on a.id=b.id
inner join status c on b.valueStatus = c.id
)t
inner join (
select row_number() over(order by b.id,b.friendid) as r2,a.nam,b.id,b.friendid
from venkatTable a inner join friendTable b on b.friendid=a.id
)
t1 on t.r1=t1.r2
Here we have used row_number() function of SQL Server 2005. It's used to generate the row numbers in your result set.
Thanks and Regards,

Venkatesan Prabu .J

Reset identity column in SQL Server

Identity column in SQL Server:

Identity column is an easiest way to generate sequence unique number in a column of the table. Considering am having a column named "ID" with property as identity with starting value as 1 and it should generate values increased by 1. The syntax is,

id int identity(1,1) -- This column will generate the values 1,2,3,4,....etc...

Now, let's goto our typical problem statement reseeding the identity column.

Problem statement:

Am having five records in the table, the id values 1,2,3,4,5. Now, I am deleting the record 5. After wards am trying to insert another record. Unfortunately we will miss 5 and the record will be inserted with the value 6. How to sort down this issue?

Solution:

SQL server is providing a handy solution to solve this issue. The command is DBCC Checkident. Lets see the syntax for this,

DBCC checkident(tablename, reseed, value to start or reseed the column)

Lets see a small example on this,

First code snippet is to show the problem and the second code snippet is the solution for the problem.

drop table venkatTable
create table venkatTable(id int identity(1,1) primary key, nam varchar(100))
insert into venkatTable values('Venkatesan Prabu')
insert into venkatTable values('Subashini')
insert into venkatTable values('Jayakantham')
insert into venkatTable values('Arunachalam')
insert into venkatTable values('Santhi')

select * from venkatTable
delete venkatTable where id=5

insert into venkatTable values('Santhi')

select * from venkatTable

drop table venkatTable
create table venkatTable(id int identity(1,1) primary key, nam varchar(100))
insert into venkatTable values('Venkatesan Prabu')
insert into venkatTable values('Subashini')
insert into venkatTable values('Jayakantham')
insert into venkatTable values('Arunachalam')
insert into venkatTable values('Santhi')

select * from venkatTable
delete venkatTable where id=5

--Am resetting the id columnwith the maximum value of the column.

declare @val int
select @val=max(id) from venkattable
dbcc checkident (venkatTable,reseed,@val)

insert into venkatTable values('Santhi')

select * from venkatTable

If you dont want to reset the seed value of the column, we can use noreseed option in the abov command,

DBCC checkident(tablename,noreseed)

Thanks and Regards,

Venkatesan Prabu .J

ASP.Net error

While trying my website creation, I got a peculiar error in ASP.net.

I have dragged the SQLDatasource and placed it in my web page. On pointing the data source, I got the below error.
"Following error occured while getting connection string information from configuration. "Cannot get web application service"

On checking microsoft site, I came to know that its a familiar bug and the solution seems to be closing the IDE and reopen the same.
This solution seems to be working fine.

Thanks and Regards,
Venkatesan Prabu .J