Hello Bonnie

I have used your generator to great effect thanks.

I am still working out how to write an updating routine that will submit a batch of new records to SQL and have them updated in one go, avoiding loops that send one record at a time over the network.

My problem seems to be that if I write an SP that is generalised enough to do what I want, it is not capable of producing execution plans for all the elements.

It is a big problem with my idea because I wanted to send the name of the table as a parameter to avoid writing a seperate SP for each table.

I think this may also be a problem with the update SP that we are both using, since it contains an INSERT and an UPDATE indicated by an IF statement. This means an execution plan will only be created for the INSERT statement. (I am a newbie so I may have this all wrong)

It looks like this problem can be avoided by producing seperate INSERT and UPDATE SPs and then using a third SP which calls both of them.

So the batch updating SP I need will probably have to be comprised of 4 SPs per table which is not what I had hoped for :(

It would need an SPs for INSERT UPDATE DELETE and another to call the other three.

I am wondering if I should just not bother about the execution plan, but I am concerened that my SQL DBA will take issue if the SP can not be optomized.



>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