Link to home
Start Free TrialLog in
Avatar of rwallacej
rwallacej

asked on

Result of custom SQL string

I have following SQL. It adds a column specified as parameter. The section to add column is fine, I need it extended so that only adds column if it does not exist already.
The line
if exec sp_executesql @checkExists
fails to run - not sure what the syntax should be here.
Thanks in advance  for help
ALTER PROCEDURE [dbo].[CreateColumn]
	-- Add the parameters for the stored procedure here
	@NewColumnName varchar(300)
AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;
 
	-- add column if it does not already exist
	declare @checkExists nvarchar(3000)
	set @checkExists = 'IF NOT EXISTS ( SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE ' +
	                   'TABLE_NAME = HistorianData AND COLUMN_NAME = ' + @NewColumnName + ')'
	-- exec SQL to check if column exists...fails here
	if exec sp_executesql @checkExists
		BEGIN
			-- add new column
			declare @n nvarchar(3000)
 
			set @n = 'ALTER TABLE HistorianData
						ADD ' + @NewColumnName + ' FLOAT'
 
			exec sp_executesql @n
		END
END

Open in new window

ASKER CERTIFIED SOLUTION
Avatar of momi_sabag
momi_sabag
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of rwallacej
rwallacej

ASKER

thank-you both for response, this works well and I hope the split points is fine with you both.
regards,
rwallacej