<p>How to get the relative path in t sql? Take for example a <code>.sql</code> file is located in the folder <code>D:\temp</code>, I want to get path of the file hello.txt in the folder  <code>D:\temp\App_Data</code>. How to use the relative path reference?</p>
<p>Let's say I am executing the sql file inside the SQL server management studio.</p>

The .sql file is just.... a file.  It doesn't have any sense of its own location.  It's the thing that excutes it (which you didn't specify) that would have a sense of its location, the file's location.
I notice that you mentioned an App_Data folder, so I guess that ASP.NET is involved.  If you want to use relative paths in your web app, see MapPath
http://msdn.microsoft.com/en-us/library/system.web.httpserverutility.mappath.aspx