Create stored procedure to make satic html content

[fayola]

hey,
first off I'd like to state that I am pretty new to ms sql and stored procedures tho I have worked with sql for quite some time.

My employer would like me to write a script which takes html content from a mssql db (originally delivered dynamically) and create static pages from them and also would like those tables locked until the script is run again. Now not sure if this will (should) fly because it may be limiting the admins to make changes to their content only during specific times.

But nonetheless, it has been requested. I just found out that ms sql can have access to the servers filesystem, which is cool because the web site and mssql are hosted on the same server.

Now I came a across a simple stored procedure that writes contents to a specified file:

CREATE PROCEDURE sp_OutputtoFile(@FileName varchar(255), @Text1 varchar(255)) AS
DECLARE @FS int, @OLEResult int, @FileID int


EXECUTE @OLEResult = sp_OACreate 'Scripting.FileSystemObject', @FS OUT
IF @OLEResult <> 0 PRINT 'Scripting.FileSystemObject'

--Open a file
execute @OLEResult = sp_OAMethod @FS, 'OpenTextFile', @FileID OUT, @FileName, 8, 1
IF @OLEResult <> 0 PRINT 'OpenTextFile'

--Write Text1
execute @OLEResult = sp_OAMethod @FileID, 'WriteLine', Null, @Text1
IF @OLEResult <> 0 PRINT 'WriteLine'

EXECUTE @OLEResult = sp_OADestroy @FileID
EXECUTE @OLEResult = sp_OADestroy @FS

What I would like is to have content from certain tables in their own folder and have an html file for each record in the tables. So for instance, say I have five tables, Article, Resource, Advice, Job, Company, then I would like to create folders (or goto predefined folders) for each table and have files for each record so that it's labled with it's primary key ID. So in the Articles folder I'd like to have article_1234.html, article_1235.html, article_1236.html ....

I'm sure you get the just of it. Is this possible with a stored procedure and if so, can anyone post some sample code I can use as a base?

[cutepinkbunnies]

This old function I made may help as it demonstrates the concepts of looping with SQL (without a yucky cursor!).

CREATE   function [dbo].[fn_Is_Uppercase]
    (@string varchar(1000))
returns bit
as

begin
	declare @r bit
	declare @l int
	declare @i int
	
	--trim it up
	set @string = mrm.dbo.trim(@string)
	
	--need to check every character
	set @l = len(@string)
	set @i = 0
	set @r = 1
	
	while @i <= @l + 1 and @r = 1
		begin
			if ascii(substring(@string,@i - 1, 1)) <> ascii(upper(substring(@string,@i - 1, 1)))
				begin
					set @r = 0
				end
			set @i = @i + 1
		end

	return @r
end

This may not be the most efficient way to iterate through the characters, but it gets the job done.

I would suggest setting up a table variable with the columns you want, along with an identity (1,1) or (0,1) column to iterate through it. For each pass through the loop, write to a file in a given directory. You can check which directory/file to write to on each iteration and while properly incrementing the LCV.

Hope this helps.

[fayola]

Thanks this helps a lot! Like you said I just need to set it to loop the set of records for each table type that will be mapped it it's own folder, so loop within a loop. Marking as solved!