Search code examples
sql-servert-sqlvariablesssmsdeclare

T-SQL: insert variable inside declared URL

I have an easy problem that I cannot solve.

If you visit this URL with your browser:

http://overpass-api.de/api/interpreter?data=[out:json];area[name="Auckland"]->.a;(node(area.a)[amenity=cinema];);out;

OSM returns for you all the cinemas in Auckland in JSON.

I would like to dynamically query that through T-SQL but first of all I need to find out how to insert the variable of the city name inside the URL:

DECLARE @place as NVARCHAR(30) SET @place = 'Auckland'

DECLARE @URL as VARCHAR(MAX) 
SET @URL = 'http://overpass-api.de/api/interpreter?data=[out:json];area[name="' + @place +'"]->.a;(node(area.a)[amenity=cinema];);out;';

This declaration doesn't work and SSMS phosphatize it in red:

enter image description here

Not sure how to come out from this problem.

I followed multiple guides but they all say to use ' + @variable + ' but in my case is not working.

Why?

Solution

  • Its just a syntaxic coloration of editor. You can config him into you IDE.

    Note you can do it too:

    DECLARE @place as VARCHAR(30) = 'Auckland'

DECLARE @URL as VARCHAR(MAX)=FORMATMESSAGE('http://overpass-api.de/api/interpreter?data=[out:json];area[name="%s"]->.a;(node(area.a)[amenity=cinema];);out;',@place) ;