Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 286
  • Last Modified:

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

0
rwallacej
Asked:
rwallacej
2 Solutions
 
momi_sabagCommented:
just try this, no need for dynamic sql:


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
      -- exec SQL to check if column exists...fails here
      if exists (select 1 from information_schema.columns where TABLE_NAME = 'HistorianData' AND COLUMN_NAME = @NewColumnName)
            BEGIN
                  -- add new column
                  declare @n nvarchar(3000)
 
                  set @n = 'ALTER TABLE HistorianData
                                    ADD ' + @NewColumnName + ' FLOAT'
 
                  exec sp_executesql @n
            END
END
0
 
reb73Commented:
Your @checkexists value is an incomplete SQL statement, you don't actually need to do it that way..

Try -
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 new column
        declare @n nvarchar(3000)
 
        set @n = 'IF NOT EXISTS ( SELECT 1 FROM INFORMATION_SCHEMA.COLUMNS WHERE ' +
                   'TABLE_NAME = ''HistorianData'' AND COLUMN_NAME = ''' + @NewColumnName + ''') ' +
		    'ALTER TABLE HistorianData ADD ' + @NewColumnName + ' FLOAT'
 
        exec sp_executesql @n
END

Open in new window

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

Featured Post

NEW Veeam Backup for Microsoft Office 365 1.5

With Office 365, it’s your data and your responsibility to protect it. NEW Veeam Backup for Microsoft Office 365 eliminates the risk of losing access to your Office 365 data.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now