USE OLAP
--Filtering Using Dynamic SQL]
go
CREATE PROCEDURE build_dyamic_sql_statment @filter char(2000) AS
BEGIN 
	declare @sql_statement char(2500) =
	concat('SELECT order_method_type,format(sum(profit),''C'') as total_profit '
	        ,char(13),'FROM sales_enriched'
			,char(13),'WHERE '
			,@filter
			,char(13),'GROUP BY order_method_type')
	print(@sql_statement)
END
go

--Notes
print 'Note the use of '' in the string.'
print concat('The char(13) character',char(13),'is used to denote a new line') 
print concat('Some text',null)
print 'Some text'+null
print concat('Some text',2) 
print 'Some text'+2 

--Running procedure
declare @condition char(2000) = 
	'date = ''2016-01-01'' and retailer_city = ''Cologne'' and retailer_type = ''Sports Store'''

exec build_dyamic_sql_statment @condition

--To run this string as a sql command we use exec
go
ALTER PROCEDURE build_dyamic_sql_statment @filter char(2000) AS
BEGIN 
	declare @sql_statement char(2500) =
	concat('SELECT order_method_type,format(sum(profit),''C'') as total_profit '
	        ,char(13),'FROM sales_enriched'
			,char(13),'WHERE '
			,@filter
			,char(13),'GROUP BY order_method_type')
	--print(@sql_statement)  --leave this here for future debugging
	exec(@sql_statement)
END
go

--Final Test
declare @condition char(2000) = 
	'date = ''2016-01-01'' and retailer_city = ''Cologne'' and retailer_type = ''Sports Store'''

exec build_dyamic_sql_statment @condition