VFP SQL SERVER Store Procedure

[WIREMESH]

I need to know how to accomplishing something in SQL Server that I use macro expansion in VFP to accomplish.

I have a table CREATE TABLE TEST (column1 i(4), column2, i(4), .....)

IF I want to iterate thru say column8 thru 11 I use the following syntax in VFP:

PROCEDURE DoSomething
LOCAL c as STRING, lc_fieldname AS STRING
LOCAL i as INTEGER

FOR i = 8 TO 11
c = alltrim(str(i))
lc_fieldname = "column" + c
IF TEST->&lc_fieldname > 0
SELECT TEST
REPLACE &lc_fieldname WITH 888
ENDIF
ENDFOR

ENDPROC

My question is, How can I write the same algorithm as a SQL Server Stored Procedure where I loop thru certain columns in a SQL Server table without macro expansion?

 

[Mike Lewis]

Hi Wiremesh,

It is possible to do this in a stored procedure, but it's much easier to do it in VFP code, and to send the code to the server via SQL pass-through.

Essentially, your SQL Server database has a table called Syscolumns. You can use this to retrieve the names of each column in a given table. See the SQL Server Help topic on Syscolumns.

You could then write stored procedure code to programmatically create a string containing your UPDATE statements, using the column names from the table.

Finally, you "execute" that string by means of the EXECUTE command (again, see the Help for details). This command is similar in concept to VFP's macro-execution.

However, you will find it much easier to use VFP's SQLCOLUMNS() function to retrieve the names of the columns. Then, create the UPDATE statements programmatically in VFP. Finally, execute the statemens by calling VFP's SQLEXEC().

The above is just an outline to get you started. Come back if you need to fill in the details.

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

My Visual FoxPro site: www.ml-consult.co.uk

 

[WIREMESH]

Mike,
Thanks for the update. The reason I inquired about a stored procedure is, although I can code the SQL statement in VFP using SQL Passthru, I have a web application that needs this functionality as well. Rather than code the algorithm in VFP & VB.BET, I wanted to implement the functionality in a stored procedure that both my desktop & web application can call.

 

[bborissov]

CREATE PROCEDURE DoSomething(@lnId as int)
-- @lnId is Unique row identifier of Table
-- Becuase SQL Table has no Record number
-- or record pointer you must explicitly tell what record
-- you want to update, change @lnId type to appropriate type
-- of ID column
BEGIN
    declare @i as int
    declare @lcSql as varchar(2000)
    declare @lcColumn as varchar(2000)
    SET @i = 8
    WHILE @i < 12
          BEGIN 
            SET @lcColumn = 'Column'+CAST(@i as varchar(2))
            SET @lcSQL = 'UPDATE MyTable SET '+@lcColumn+
                         ' WHERE UniqueIdOfrecord = '+
                         CAST(@lnId as varchar(200))+
                         ' AND '+@lcColumn+' > 0'
           EXEC (@lcSQL)
           SET @i = @i + 1 
         END
END

Something like that. Not tested, but this is only example to see how it could be done.

Borislav Borissov
VFP9 SP1, SQL Server 2000/2005.