In my today post, we will be discussing how to declare a single or multiple variables with different approaches by using SQL Statements. I will be using MS SQL Server to write SQL Statements to achieve the task.

Lets get logged in to your SQL Server Database Engine using Microsoft SQL Server Management Studio. 

Create a New Query, I know that you are most familiar with how to open a new Query Window in MS SQL Server. 

Here i am using a Sample Database with a Name sampledatabase I will be using the same database for writing the examples with my Blog posts. 

I have Created a Table to add some Records so that we can use them in our on the go post.

create table MembersList
(
 id bigint not null primary key identity(1,1)
 ,Name nvarchar(100)
 ,ContactNo nvarchar(50)
 ,EmailID nvarchar(100)
 ,Age int
 ,FK_MemberType bigint
 ,ListedDateTime DateTime
 ,AddressLocation nvarchar(1000)
)

Now i will be adding some Records in the Table.  

The Insert Query Helps me to add the details in the Table.

insert into MembersList(Name,ContactNo ,EmailID,Age,FK_MemberType,ListedDateTime,AddressLocation)
select 'Liam','+44 -0125365422','Liam@Email.com',65,1,GetDate(),'Location Address 1' union
select 'Noah','+44 -0125365423','Noah@Email.com',25,2,GetDate(),'Location Address 2' union
select 'Oliver','+44 -0125365424','Oliver@Email.com',45,1,GetDate(),'Location Address 3' union
select 'Elijah','+44 -0125365425','Elijah@Email.com',23,2,GetDate(),'Location Address 4' union
select 'William','+44 -0125365426','William@Email.com',45,3,GetDate(),'Location Address 5' union
select 'James','+44 -0125365427','James@Email.com',50,1,GetDate(),'Location Address 6' union
select 'Benjamin','+44 -0125365428','Benjamin@Email.com',21,1,GetDate(),'Location Address 7' union
select 'Lucas','+44 -0125365429','Lucas@Email.com',20,1,GetDate(),'Location Address 8' 

Here this is quite interesting to open your mind and start thinking, to save your time in writing a text with giving a Naming Convention to your Variables and start typing to do so. I would be saving my time to and write some interesting query trick to achieve the task.

select 'declare @' + COLUMN_NAME + ' as ' + DATA_TYPE from INFORMATION_SCHEMA.COLUMNS where TABLE_NAME='MembersList' 

the results of the above query is as under 

declare @id as bigint
declare @Name as nvarchar
declare @ContactNo as nvarchar
declare @EmailID as nvarchar
declare @Age as int
declare @FK_MemberType as bigint
declare @ListedDateTime as datetime
declare @AddressLocation as nvarchar

So finally my variables are declare, I was thinking that this Tables has 08 Column, if i would have more columns such as 20 to 50 and i need to declare those variables in by development procedures that could be waste of time. 

It would be easy for us to initialize a default value while we are creating a table, thus we can utilize multiple helping columns with the Information Schema defined of any database table set.

So i write a Query to get a complete Information Schema set of any Table in a Database with a where clause of TableName 

select * from INFORMATION_SCHEMA.COLUMNS where TABLE_NAME='MembersList'

So i would get some Columns that we can use to perform such tasks that is some times get headache for us.

  1. TABLE_CATALOG
  2. TABLE_SCHEMA
  3. TABLE_NAME
  4. COLUMN_NAME
  5. ORDINAL_POSITION
  6. COLUMN_DEFAULT
  7. IS_NULLABLE
  8. DATA_TYPE
  9. CHARACTER_MAXIMUM_LENGTH
  10. CHARACTER_OCTET_LENGTH
  11. NUMERIC_PRECISION
  12. NUMERIC_PRECISION_RADIX
  13. NUMERIC_SCALE
  14. DATETIME_PRECISION
  15. CHARACTER_SET_CATALOG
  16. CHARACTER_SET_SCHEMA
  17. CHARACTER_SET_NAME
  18. COLLATION_CATALOG
  19. COLLATION_SCHEMA
  20. COLLATION_NAME
  21. DOMAIN_CATALOG
  22. DOMAIN_SCHEMA
  23. DOMAIN_NAME
Anyways get back to some more interesting SQL TechnTricks over here. 

Now i will writing a Query to Initialized the Declared Variables with the any defined values in my Tables set.

Here is a Select Statement usage to Initialize a value to my variables with a clause of id = 1

Declaring and Initializing a single or multiple Variables styling 1 

select 'set @' + COLUMN_NAME + ' = ' + '(select ' + COLUMN_NAME + ' from '+ TABLE_NAME + ' where id=1)'  from INFORMATION_SCHEMA.COLUMNS where TABLE_NAME='MembersList' 

The results of the above query is 

set @id = (select id from MembersList where id=1)
set @Name = (select Name from MembersList where id=1)
set @ContactNo = (select ContactNo from MembersList where id=1)
set @EmailID = (select EmailID from MembersList where id=1)
set @Age = (select Age from MembersList where id=1)
set @FK_MemberType = (select FK_MemberType from MembersList where id=1)
set @ListedDateTime = (select ListedDateTime from MembersList where id=1)
set @AddressLocation = (select AddressLocation from MembersList where id=1)

Declaring and Initializing a single or multiple Variables styling 2

select '@' + COLUMN_NAME + ' = ' + COLUMN_NAME + ','  from INFORMATION_SCHEMA.COLUMNS where TABLE_NAME='MembersList'

The results of the able query 

@id = id,
@Name = Name,
@ContactNo = ContactNo,
@EmailID = EmailID,
@Age = Age,
@FK_MemberType = FK_MemberType,
@ListedDateTime = ListedDateTime,
@AddressLocation = AddressLocation,

So i have append the above results with, 

select 
@id = id,
@Name = Name,
@ContactNo = ContactNo,
@EmailID = EmailID,
@Age = Age,
@FK_MemberType = FK_MemberType,
@ListedDateTime = ListedDateTime,
@AddressLocation = AddressLocation
from MembersList where id = 1

So both queries do the same task with initializing the values of ID = 1 to the declared variables.

Hope the blog readers, will follow the procedures with your practice and trying some new interesting tricks for writing SQL Query Statements.