How to read a file using SQL in a SQL Server stored procedure

There might be times when writing a SQL Server stored procedure to read a file from the disk. You could create a .NET program to do the work for you, but that might be a bit cumbersome and may even waste time.

The easiest way is to use the OpenRowset command, as illustrated below.

    SELECT * FROM OPENROWSET(BULK 'C:\temp\temp.txt', SINGLE_CLOB) AS FileCont;

That's fine, but what if you want the name of the file to be in a variable rather than hard-coded, you can't do....

    declare @File varchar(1000) = 'C:\temp\temp.txt';

    SELECT * FROM OPENROWSET(BULK @File, SINGLE_CLOB) AS Contents;

It will result in a syntax error. The solution to the problem below is to use the sp_executesql function, which is built in.

    declare @File varchar(1000) = 'C:\temp\achievements.txt';

    declare @file_stream Varchar(Max);

    declare @SQL nvarchar(1000) = 'SELECT @file_stream1 = CAST(bulkcolumn AS varchar(MAX)) FROM OPENROWSET(BULK' + @File + ', SINGLE_CLOB) Row_Set;';

    EXEC sp_executesql @SQL, N'@file_stream1 varchar(MAX) OUTPUT',@file_stream1 =@file_stream OUTPUT

    select @file_stream

You can't turn the code above into a function because although sp_executesql may be classed as an extended procedure, it is rejected. The best you will have to do is to create a procedure that you have to customise for each operation, something along the lines of:-

    create or alter Procedure GetContents(@file varchar(1000)) as        declare @file_stream Varchar(Max);

        declare @SQL nvarchar(1000) = 'SELECT @file_stream1 = CAST(bulkcolumn AS varchar(MAX)) FROM OPENROWSET(BULK' + @File + ', SINGLE_CLOB) Row_Set;';

        EXEC sp_executesql @SQL, N'@file_stream1 varchar(MAX) OUTPUT',@file_stream1 =@file_stream OUTPUT

        Select @file_stream as Contents

    go

Hopefully, that has helped. Leave a comment if you can make suggestions or need help.

Other Articles of Interest

Next Article : How to create a CodeFirst Entity Framework Project using Visual Studio
Previous Article : How to test if a file exists in SQL Server

Tags - SQL

Comments and Questions

There's no register feature and no need to give an email address if you don't want to. All messages will be reviewed before being displayed. Comments may be merged or altered slightly, such as if it contains an email or website address.

You can decline to give a name; if that is the case, the comment will be attributed to a random star. A name is preferred, even if it's a random made-up one by yourself.

If you give an email address, you may receive an email notifying you when someone else has added a comment to the same page. In the email will be a link to unsubscribe to further notifications.