How to get the relative path in t sql? Take for example a .sql file is located in the folder D:\temp, I want to get path of the file hello.txt in the folder D:\temp\App_Data. How to use the relative path reference?
Let's say I am executing the sql file inside the SQL server management studio.
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
Actually I am executing the t sql file from the sql server management studio, clarification
I don't get it then, do you want to get a relative path using the path of the file you're opening in sql server management studio? I don't think it's possible. Maybe you can use SSIS to automate file processing.
When T-SQL is executing, it is running in a batch on the server, not on the client machine running Management Studio (or any other SQL client). The client just sends the text contents of the .sql file to the server to be executed. So, unless that file is located on the database server, I highly doubt you're going to be able to interact with it from a SQL script.
The server is executing the t-sql. It doesn't know where the client loaded the file from. You'll have to have the path embedded within the script.
DECLARE @RelDir varchar(1000)
SET @RelDir = 'D:\temp\'
...
Perhaps you can programmatically place the path into the SET command within the .sql script file, or perhaps you can use sqlcmd and pass the relative directory in as a variable.