SQL SERVER 2008 – How to Use User Defined Variable in SQL Query

Sometimes we come on a situation where we cannot use joins and other operation on tables in Sql Query in those kind of situations building a Sql Query using the user defined variables is best option to use. Here I have discussed a simple example to show how we can use this concept.

We want the customer residing in a certain part of geography and for that we can declare a variable and store the values in comma separated way and later use it in query and Build an Sql Query out of  it and Finally Execute it to get the complete list of customers residing  in that particular location.

use AdventureWorksDW2008R2

Go

declare @Key varchar(10),@Sql nvarchar(100)

Set @Key = ‘23,40,3’

Set @Sql = ‘select * from DimCustomer where GeographyKey in (‘+ @Key +’)’

exec (@Sql)

Note: Execute the total procedure at a go.

The result is somehing like this:

There can be many complex situation where this concept can be handy.

Reference : Dilip Kumar Jena ( https://sqlexplore.wordpress.com )

Advertisements

2 thoughts on “SQL SERVER 2008 – How to Use User Defined Variable in SQL Query

    • Yes A valid Question We Can pass the values from the Application it self or the Stored Procedure or a function anything is file …. the main thing is that one statement we are able to get all the things

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s