SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2008 Forums
 Transact-SQL (2008)
 FUNCTION TO ADD FIELDS AND CONSTRAINTS TO A TABLE.
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

KBM
Starting Member

4 Posts

Posted - 03/06/2014 :  20:38:59  Show Profile  Reply with Quote
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.

Edited by - KBM on 03/06/2014 20:42:17

khtan
In (Som, Ni, Yak)

Singapore
17598 Posts

Posted - 03/06/2014 :  21:29:47  Show Profile  Reply with Quote
you can't use exec() inside a function. You will need to use Stored Procedure to do that


KH
Time is always against us

Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.03 seconds. Powered By: Snitz Forums 2000