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]

Go to Top of Page
   

- Advertisement -