Thanks Bonnie!


This code worked on my tables just fine.
If I ever get round to writing the Get and Delete elements I will be sure to let you have a copy:)





>Mark,
>
>Well, guess it took more than a coupla hours for me to get back to you. Sorry ... meetings and deadlines and such. Here's a sample of the PUT SP:
>

>CREATE PROCEDURE bsp_PersonnelPut
>	@personnelkey		bigint = NULL OUTPUT,
>	@personnelid		varchar(10) = NULL,
>	@lastname		var = NULL,
>	@firstname		udt_firstname = NULL
>	-- etc.etc.etc.
>AS
>	IF (@personnelkey = 0 OR @personnelkey IS NULL)
>	BEGIN
>		-- Insert Values into the Personnel table
>		INSERT Personnel
>			(personnelid,
>			lastname,
>			firstname)
>		SELECT @personnelid,
>			@lastname,
>			@firstname
>
>		SELECT @personnelkey = SCOPE_IDENTITY()
>
>	END
>	ELSE
>	BEGIN
>		-- Update the Personnel table
>		UPDATE Personnel
>		SET personnelid = ISNULL(@personnelid, personnelid),
>		    lastname = ISNULL(@lastname, lastname),
>		    firstname = ISNULL(@firstname, firstname)
>		WHERE personnelkey = @personnelkey
>	END
>
>	RETURN 0
>

>
>>>I feel a bit cheeky....but any chance you could let me have that utility as well :) <
>
>Yeah, you *are* a bit cheeky, aren't you? <g>
>Here's a portion of the VFP program to auto-generate this. It should be enough
>for you to get the idea.
>Add other functions to create GET and DELETE Stored Procs.
>

>*-------------------------------------------------------------------------------*
>FUNCTION GetPutProc
>*-------------------------------------------------------------------------------*
>LPARAMETERS tnConnection, tcTable
>
>tcTable = PROPER(ALLTRIM(tcTable))
>LOCAL lnResults, lcProc, lcName, lcType, lcField, lcKey
>lcProc = ""
>lnResults = SQLCOLUMNS(tnConnection, tcTable, "NATIVE", "cTemp")
>IF lnResults < 1 OR RECCOUNT("ctemp") < 1
>	RETURN ""
>ENDIF
>
>lcName = "bsp_" + tcTable + "Put"
>lcKey = LOWER(tcTable) + "key"
>
>*-- Write the header for the stored proc
>lcProc = GetProcHeader(lcName, tcTable, "Put")
>
>*-- Write the parameter list
>*   First parm is primary key, used only as output variable in insert procs
>SELECT cTemp
>SCAN
>	lcType = LOWER(ALLTRIM(type_name))
>	lcField = LOWER(ALLTRIM(column_name))
>	DO CASE
>	CASE lcField == lcKey
>		*-- Don't insert a key, but get the identity value
>		lcProc = lcProc + CRLF + TAB + "@" + lcField + ;
>			TAB + TAB + lcType + " = NULL OUTPUT,"
>	CASE lcType == "char" OR lcType == "varchar"
>		lcProc = lcProc + CRLF + TAB + "@" + lcField + ;
>			TAB + TAB + lcType + ;
>			"(" + TRANSFORM(column_size) + ")" + ;
>			" = NULL,"
>	CASE lcType == "decimal" OR lcType == "numeric"
>		lcProc = lcProc + CRLF + TAB + "@" + lcField + ;
>			TAB + TAB + lcType + ;
>			"(" + TRANSFORM(column_size) + "," + TRANSFORM(decimal_digits) + ")" + ;
>			" = NULL,"
>	CASE lcField == "rowguid"
>		*-- Skip the rowguid column
>	OTHERWISE
>		lcProc = lcProc + CRLF + TAB + "@" + lcField + ;
>			TAB + TAB + lcType + " = NULL,"
>	ENDCASE
>ENDSCAN
>
>*-- Trim off the last comma
>lcProc = LEFT(lcProc, LEN(lcProc) - 1) + CRLF
>
>*TEXT***************************
>TEXT TO lcProc ADDITIVE TEXTMERGE NOSHOW
>AS
>	SET NOCOUNT ON
>
>	SET XACT_ABORT ON
>
>	BEGIN TRANSACTION
>	IF (@<<lcKey>> = 0 OR @<<lcKey>> IS NULL)
>	BEGIN
>		-- Insert Values into the <<tcTable>> table
>		INSERT <<tcTable>>
>
>ENDTEXT
>*ENDTEXT************************
>
>*-- Create insert field list
>SELECT cTemp
>SCAN
>	lcField = LOWER(ALLTRIM(column_name))
>	
>	*-- Skip if key field or rowguid
>	IF lcField == lcKey OR lcField  == "rowguid"
>		LOOP
>	ENDIF
>
>	IF RIGHT(lcProc, 1) <> ","
>		*-- Must be first line, add parenthesis
>		lcProc = lcProc + TAB + TAB + TAB + "(" + lcField + ","
>	ELSE
>		lcProc = lcProc + CRLF + TAB + TAB + TAB + lcField + ","
>	ENDIF
>ENDSCAN
>
>*-- Trim off the last comma and replace with parenthesis
>lcProc = LEFT(lcProc, LEN(lcProc) - 1) + ")" + CRLF
>lcProc = lcProc + CRLF + CRLF
>
>*-- Create insert value list
>SELECT cTemp
>SCAN
>	lcField = LOWER(ALLTRIM(column_name))
>	
>	*-- Skip if key field or rowguid
>	IF lcField == lcKey OR lcField  == "rowguid"
>		LOOP
>	ENDIF
>	
>	IF RIGHT(lcProc, 1) <> ","
>		*-- Must be first line, add parenthesis
>		lcProc = lcProc + TAB + TAB + "SELECT @" + lcField + ","
>	ELSE
>		lcProc = lcProc + CRLF + TAB + TAB + TAB + "@" + lcField + ","
>	ENDIF
>ENDSCAN
>
>*-- Trim off the last comma and replace with parenthesis
>lcProc = LEFT(lcProc, LEN(lcProc) - 1) + CRLF + CRLF
>
>*-- Get the new primary key
>lcProc = lcProc + TAB + TAB + "SELECT @" + lcKey + " = SCOPE_IDENTITY()" + CRLF + CRLF
>
>*-- Now write the ELSE
>*TEXT***************************
>TEXT TO lcProc ADDITIVE TEXTMERGE NOSHOW
>	END
>	ELSE
>	BEGIN
>		-- Update the <<tcTable>> table
>		UPDATE <<tcTable>>
>		SET
>ENDTEXT
>*ENDTEXT************************
>
>*-- Create insert field list
>SELECT cTemp
>SCAN
>	lcField = LOWER(ALLTRIM(column_name))
>	
>	*-- Skip if key field or rowguid
>	IF lcField == lcKey OR lcField  == "rowguid"
>		LOOP
>	ENDIF
>
>	IF RIGHT(lcProc, 3) == "SET"
>		*-- Must be first line, don't add CRLF
>		lcProc = lcProc + " " + lcField + " = ISNULL(@" + lcField + ", " + lcField + "),"
>	ELSE
>		lcProc = lcProc + CRLF + TAB + TAB + "    " + lcField + " = ISNULL(@" + lcField + ", " + lcField + "),"
>	ENDIF
>ENDSCAN
>
>*-- Trim off the last comma
>lcProc = LEFT(lcProc, LEN(lcProc) - 1) + CRLF
>
>*-- Set the WHERE clause
>lcProc = lcProc + TAB + TAB + "WHERE " + lcKey + " = @" + lcKey + CRLF + TAB + "END" + CRLF + CRLF
>
>*-- Write the footer for the stored proc
>lcProc = lcProc + GetProcFooter()
>
>*-- Clean up and go home
>USE IN cTemp
>RETURN lcProc
>ENDFUNC
>
>

>
>Have fun!
>~~Bonnie