How to write an SQL function to cut down on code repetition

Question

I need to run an SQL function for multiple countries

I'm wondering if there is a way to create a function where I can just input the country name and get a table for each. Here's my query for Germany

SELECT customer.id,
       product_purchased,
       country
FROM sales_tables
WHERE country = 'Germany'

So instead of changing each country in the code, can I do something like this

FUNCTION products_purchased_byCountry($1):
    SELECT customer.id,
           product_purchased,
           country
    FROM sales_tables
    WHERE country = '$1'

Then just call this function with the following

products_purchased_byCountry(Germany)
products_purchased_byCountry(France)
products_purchased_byCountry(UK)

Or maybe to run such a function I have to do it through R, or Python?

Answer 1

You could use a stored procedure and set @country as a variable.

Create procedure products_purchased_byCountry

@country varchar(100)

AS BEGIN

SELECT customer.id,
       product_purchased,
       country
FROM sales_tables
WHERE country = @country

END

Then call it like this

EXEC products_purchased_byCountry 
@country = 'Germany'