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
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
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
regards,
rwallacej