SQL Double Number Sign – What Does ## Mean in SQL?

sql serverstack-exchange-data-explorer

A meta post gives a nice snippet of SQL

declare @search nvarchar(max) = ##searchfor:string?carefully %listen##
select id [Post Link]
     , score
     , creationdate
     , lastactivitydate
     , closeddate 
     , owneruserid as [User Link]
from posts
where body like concat('%', @search, '%') collate SQL_Latin1_General_CP1_CI_AI
or title like concat('%', @search, '%') collate SQL_Latin1_General_CP1_CI_AI

What does double number sign ## mean in there?

I guess that is some kind of string syntax, but I cannot even guess a keyword for that. googling "number sign ## in SQL" returns "prefix temporary tables", which is not the case.

Best Answer

## surrounds a query parameter in the Stack Exchange Data Explorer (SEDE).

The portion after the ? is the displayed default parameter value.

From the SEDE Tutorial written by Monica Cellio:

Note: the particular syntax used for parameters is specific to SEDE. Everything we've said so far about SQL is true for all flavors of SQL; this is different.

As shown in this query, you refer to a parameter by surrounding its name with doubled pound signs, such as ##MinScore##. Optionally you can specify the data type (int (a whole number), float (a number with a decimal value, like 2.5), or string): ##MinScore:int##. If you specify a type then SEDE will validate values against that type, so that if you're expecting a number for score and somebody types "unicorn" the query won't run. If you don't specify a type, your query may receive unexpected inputs.

## has no meaning in standard SQL. Though not the case here, it is used in Transact-SQL as a prefix to identify a global temporary object:

Rules for regular identifiers

An identifier that starts with a number sign denotes a temporary table or procedure. An identifier that starts with double number signs (##) denotes a global temporary object. Although the number sign or double number sign characters can be used to begin the names of other types of objects, we do not recommend this practice.

Related Question