FUNCTION TO ADD FIELDS AND CONSTRAINTS TO A TABLE.

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

FUNCTION TO ADD FIELDS AND CONSTRAINTS TO A TABLE.

Author

Topic

KBM
Starting Member

4 Posts

Posted - 2014-03-06 : 20:38:59

As an Access programmer this just doesn't seem like it should be that tough. I want to create a function to ad fields and constraints to a table. Pass a Field name, table name, default value.
The problem I run into is you can't use GO in a function and you have to add the field before you add the constraint(s) so I tried EXEC but it turns out you can't use EXEC in a function either.
Below is the code I tried to turn into a function with the code to run it after. Remove the Function stuff and move the stuff after the function to the top and it runs fine.

Any ideas appreciated.

USE [SkDataDemo]

IF OBJECT_ID('dbo.KimsAddField') IS NOT NULL
  DROP FUNCTION FMT_PHONE_NBR
GO




CREATE FUNCTION [dbo].[KimsAddField] (@FldName VARCHAR(50), @tblName VARCHAR(50))
RETURNS VARCHAR(500)
AS
BEGIN
	DECLARE @RetVal VARCHAR(500)
	IF EXISTS(SELECT * FROM sys.columns WHERE [name] = @FldName AND [object_id] = OBJECT_ID(@tblName))
	BEGIN
		SET @RetVal =  N'' + @tblName + '.'+ @FldName +' EXISTS'
	END
	ELSE
	BEGIN
		/*With Constraint >'' Have to ADD the field THEN the constraint*/
		EXEC('ALTER TABLE [dbo].[' + @tblName + '] ADD [' + @FldName + '] nvarchar(10) NULL')
		IF EXISTS(SELECT * FROM sys.columns WHERE [name] = @FldName AND [object_id] = OBJECT_ID(@tblName))
		BEGIN
			SET @RetVal =  N'' + @tblName + '.'+ @FldName +' created'
		END
	END

	IF EXISTS(SELECT * FROM sys.columns WHERE [name] = @FldName AND [object_id] = OBJECT_ID(@tblName))
	BEGIN
		IF EXISTS(SELECT * FROM INFORMATION_SCHEMA.CHECK_CONSTRAINTS WHERE CONSTRAINT_NAME = @FldName + 'No0Str')
		BEGIN 
			SET @RetVal = @RetVal + N'CONSTRAINT ' + @tblName + '.'+ @FldName +'No0Str ALREADY EXISTS'
		END
		ELSE
		BEGIN
			EXEC('ALTER TABLE [dbo].[' + @tblName + ']  WITH CHECK ADD CONSTRAINT [' + @FldName + 'No0Str] CHECK ((len([' + @FldName + '])>(0)))')
			/*Not sure what this does but is always there*/
			EXEC('ALTER TABLE [dbo].[' + @tblName + '] CHECK CONSTRAINT [' + @FldName + 'No0Str]')
			SET @RetVal = @RetVal +  N'CONSTRAINT ' + @tblName + '.' + @FldName + 'No0Str CREATED'
		END

	END
	RETURN @RetVal
END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

DECLARE @FldName VARCHAR(50), @tblName VARCHAR(50)
SET @FldName ='Field6'
SET @tblName ='AAAATableName'
PRINT [dbo].[KimsAddField](@FldName, @tblName)
GO

The error I get is:
Invalid use of a side-effecting operator 'EXECUTE STRING' within a function.

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2014-03-06 : 21:29:47

you can't use exec() inside a function. You will need to use Stored Procedure to do that

KH
[spoiler]Time is always against us[/spoiler]

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources