SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author: Christian Coppes
-- Create date: 30.12.2014
-- Description: Returns a table with the fields of
-- the desired source table and their default values
-- as sql_variant datatype
-- =============================================
CREATE PROCEDURE dbo.procDefaultValues_Get
@strSchemaName nvarchar(128) = '',
@strTableName nvarchar(128) = ''
WITH EXECUTE AS 'UserWithPermission'
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
DECLARE @strColumnName nvarchar(128);
DECLARE @strDefault nvarchar(MAX);
DECLARE @strSQL nvarchar(MAX) ='';
DECLARE @intCount AS int = 1;
DECLARE @intMaxCount AS int = 0;
DECLARE @tblDefaults AS table (ID int IDENTITY(1,1) PRIMARY KEY NOT NULL,
ColumnName nvarchar(128),
DefaultText nvarchar(MAX));
-- write the column name and default expressions for all columns with default constraint
-- into the temp table variable @tblDefaults
-- (changed to INFORMATION_SCHEMA method, thanks for the suggestion to Mark Wills)
INSERT INTO @tblDefaults (ColumnName,DefaultText)
SELECT column_name, column_default
FROM INFORMATION_SCHEMA.COLUMNS
WHERE table_schema = @strSchemaName
AND table_name = @strTableName
AND column_default IS NOT NULL
-- Use this for the INSERT command above if the INFORMATION_SCHEMA method above does not work
--SELECT SCOL.name,SCOM.text
--FROM syscomments AS SCOM
--INNER JOIN (SELECT SC.cdefault,SC.name
-- FROM syscolumns AS SC
-- WHERE SC.id = OBJECT_ID(@strSchemaName + '.' + @strTableName)
-- AND SC.cdefault > 0
-- ) AS SCOL
--ON SCOM.id = SCOL.cdefault
-- create a temp table which can be used (and extended)
-- inside the dynamic SQL string and insert one row
CREATE TABLE #tmp (ID int PRIMARY KEY NOT NULL)
INSERT INTO #tmp (ID) VALUES (1);
-- determine the loop counter end
SELECT @intMaxCount = COUNT(*) FROM @tblDefaults;
-- only start loop if there are any defaults in the table
IF @intMaxCount > 0
BEGIN
SET @intCount = 1;
SET @strSQL = '';
-- loop through all rows of the default table
-- and create one additional field in the #tmp table
-- with datatype "sql_variant" so that the output to the frontend
-- contains the right datatype and no conversion between
WHILE @intCount <= @intMaxCount
BEGIN
SET @strSQL = @strSQL + (SELECT D.ColumnName FROM @tblDefaults AS D WHERE D.ID = @intCount) + ' sql_variant,'
SET @intCount = @intCount + 1;
END
SET @strSQL = LEFT(@strSQL,LEN(@strSQL)-1)
SET @strSQL = 'ALTER TABLE #tmp ADD ' + @strSQL
EXECUTE sp_executesql @strSQL
SET @strSQL = '';
SET @intCount = 1;
-- loop again through all rows of the default table
-- and update the value of the corresponding field
-- with the evalued return value which results from the default expression
WHILE @intCount <= @intMaxCount
BEGIN
SET @strColumnName = (SELECT D.ColumnName FROM @tblDefaults AS D WHERE D.ID = @intCount);
SET @strDefault = (SELECT DefaultText FROM @tblDefaults AS D WHERE D.ID = @intCount);
SET @strSQL = 'DECLARE @strResult AS sql_variant; SELECT @strResult = ' + @strDefault + ';';
SET @strSQL = @strSQL + 'UPDATE #tmp SET ' + @strColumnName + ' = @strResult WHERE ID = 1;'
PRINT @strSQL
EXECUTE sp_executesql @strSQL
SET @intCount = @intCount + 1;
END
END
-- as the #tmp table has always another number of columns it is important to keep "SELECT *" here
SELECT * FROM #tmp;
DROP TABLE #tmp;
END
GO
rs.Fields("MyTableField").Value
You can then assign to the "DefaultValue" property of a control or directly assign it to its value property.
Have a question about something in this article? You can receive help directly from the article author. Sign up for a free trial to get started.
Comments (0)