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

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'

Assuming that you are using Microsoft SQL Server (2005 or better)...

You can create a stored procedure that accepts your country parameter and returns a tabular record set (as mentioned in another answer here).

You could also create a “User Defined Table Function” (UDF). The nice thing about table UDF’s is that you can call them just like a table or a view. You can perform table joins against them (unlike stored procedures).

Here is an example table UDF:

CREATE FUNCTION dbo.udfMyFunc( @country VARCHAR(50) )
RETURNS TABLE
AS
RETURN 
    SELECT col1, col2, col3
    FROM mytable 
    WHERE country_name = @country

You can do more complex things (multi-statement UDF) if you add a BEGIN after the AS, and an END after everything else... so long as your final statement is the RETURN (result set).

I’ve often found that when calling a UDF you have to include the schema name prefix (dbo Iin this case).

SELECT cou.col1, cou.col2, cou.col3
FROM dbo.udfMyFunc(‘France’) AS cou

Have fun with this...

the exact syntax will depend on the dialect of SQL you're using, but this should generally be possible. In PostgreSQL, for example, this concept is called a User Defined Function (UDF).

The syntax would look something like this:

CREATE TABLE purchase (
  id                   varchar,
  product_purchased    varchar,
  country              varchar
);

CREATE FUNCTION products_purchased_byCountry(varchar) RETURNS purchase AS $$
  SELECT customer.id,
       product_purchased,
       country
  FROM sales_tables
  WHERE country = '$1'
  $$ LANGUAGE SQL;

SELECT country('Germany') AS answer;

However, generalizing basic queries like this would quickly get cumbersome - UDFs are best used for highly reusable, more complex queries. In this case, you'll probably want to format in your variable in the language you're using to wrap your SQL, such as Python or Javascript, and then pass the complete query string into SQL as a literal.

Since you mention you want to do this through R or python, there are other options than SQL stored procedures. In R, the DBI package can be used to connect to a database, and the dbGetquery function pulls data from a query. You can write sql injection safe queries with the param argument. Here is a MWE:

library(DBI)
con <- dbConnect(RSQLite::SQLite(), ":memory:")

dbWriteTable(con, "mtcars", mtcars)
dbGetQuery(con, "SELECT * FROM mtcars WHERE hp > :hp", param=list(hp=100))

dbDisconnect(con)

you can do it with a stored procedure (and probably a function as well)

  CREATE PROCEDURE products_purchased_byCountry
      @country varchar(50)
  AS

      SELECT [customer.id],
             product_purchased,
          country
      FROM sales_table
      WHERE country = @country

then call that with

products_purchased_byCountry 'Germany'

For SQL Server use a Table valued Function.

As an answer to the other posts, you use a Procedure when you just want to do something without a return, eg insert into xxx And a function when you want something to be returned to the calling script/procedure.