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.
declare @Key varchar(10),@Sql nvarchar(100)
Set @Key = ‘23,40,3’
Set @Sql = ‘select * from DimCustomer where GeographyKey in (‘+ @Key +’)’
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 )