Assigning default values to columns

If external applications (those other than Microsoft Dynamics GP) will be creating records in your SQL tables, you may want to bind default values to each of the table’s columns. If a value for a column isn’t supplied when a record is created, the default value bound to the column will be used.

Microsoft Dynamics GP defines the following default values that can be used for columns in your integration’s tables.

To apply default values to columns, you can use pass-through SQL. The sp_bindefault stored procedure allows you to bind a default value to a column. For example, the following code from the sample integration shows the pass-through SQL used to assign default values to the columns for the IG_Contact_History_MSTR table. This code must be run by a user who is the database owner, such as “sa” or “DYNSA”. Otherwise, the procedure won’t have sufficient privileges to bind defaults to the columns.

local string database;
local long SQL_connection, status;
local text SQL_Statements;

{Get the current database in which the tables were created}
database = 'Intercompany ID' of globals;

if 'SQL Server' of globals > 0 then
	{Connect to the SQL data source.}
	status = SQL_Connect(SQL_connection);
	if status = 0 then
		{Build and execute SQL statement to use the appropriate database.}
		SQL_Statements = "use " + database;
		status = SQL_Execute(SQL_connection, SQL_Statements);
		if status = 0 then
			{Were able to switch databases. Bind the columns}
			{Build the SQL statements.}
			SQL_Statements = "EXEC sp_bindefault 'dbo.GPS_CHAR', 'IG003.[ContactSalespersonID1]'" + char(13);
			SQL_Statements = SQL_Statements + "EXEC sp_bindefault 'dbo.GPS_CHAR', 'IG003.[ContactSalespersonID2]'" + char(13);
			SQL_Statements = SQL_Statements + "EXEC sp_bindefault 'dbo.GPS_CHAR', 'IG003.[CNTCPRSN]'" + char(13);
			SQL_Statements = SQL_Statements + "EXEC sp_bindefault 'dbo.GPS_CHAR', 'IG003.[CUSTNAME]'" + char(13);
			SQL_Statements = SQL_Statements + "EXEC sp_bindefault 'dbo.GPS_CHAR', 'IG003.[CUSTNMBR]'" + char(13);
			SQL_Statements = SQL_Statements + "EXEC sp_bindefault 'dbo.GPS_DATE', 'IG003.[FirstContactDate]'" + char(13);
			SQL_Statements = SQL_Statements + "EXEC sp_bindefault 'dbo.GPS_DATE', 'IG003.[LastContactDate]'" + char(13);
			{Execute the SQL statements}
			status = SQL_Execute(SQL_connection, SQL_Statements);
		else
			error "Could not switch to the correct database when binding default column values.";
		end if;
	end if;
	{Disconnect from the SQL data source.}
	status = SQL_Terminate(SQL_connection);
end if;

Documentation Feedback

---