Dynamic queries with varchar as input variable

Dynamic queries:



For this month, am bit concentrating on dynamic queries. I found lot of interesting things in dynamic queries and wish to blog the same.

This article primarily deals with dynamic query with varchar as input variable

-- Am creating a table named customers

drop table Customers
CREATE TABLE Customers
(
Cus_ID int PRIMARY KEY ,
Cus_Name varchar(30) NOT NULL,
Cus_City varchar(30) NOT NULL,
Cus_Country varchar(30) NOT NULL
)
insert into customers values(1,'venkat','dpi','india')
select * from customers

In the below query am assigning the value to the variable. For dynamic queries, providing an input value will be bit different. We need to use 3 single quotes before and after the input value.

DECLARE @SQL varchar(1000)
declare @cus_name varchar(1000)
declare @Cus_City varchar(1000)
declare @Cus_Country varchar(1000)

set @Cus_Name ='''venkat'''
SET @SQL = 'SELECT Cus_Name, Cus_City, Cus_Country FROM Customers '
SET @SQL = @SQL + 'WHERE '
SET @SQL = @SQL + 'Cus_Name =' + @cus_Name

print @sql

EXEC(@SQL)

Print @sql will provide you the output "
SELECT Cus_Name, Cus_City, Cus_Country FROM Customers WHERE Cus_Name ='venkat'

The above query is executed dynamically.

Thanks and Regards,

Venkatesan prabu .J