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.

Tags - SQL

Last Modified : June 2023