FB Group Questions · Real Scenario Logic's · User Q & A

SQL Question and Answer #1

Question

Question

Here, the User wants us to add the column name through stored procedure and if the given column name already exists, then we have to show the alert message instead of adding it.

Answer

We are going to use CREATE TABLE and DROP TABLE, EXEC SP_HELP, CREATE OR ALTER PROC, System View – SYS.COLUMNS and Dynamic SQL. Please check the below SQL Script. Question link is here.

SQL SCRIPT

IF OBJECT_ID('My_table') IS NOT NULL
	DROP TABLE My_table;
GO

CREATE TABLE My_table
(
A VARCHAR(100),
B VARCHAR(100),
C VARCHAR(100),
D VARCHAR(100),
E VARCHAR(100)
);
GO

--To View Structure of Table (Before)
EXEC SP_HELP My_table;
GO

CREATE OR ALTER PROC Update_Records 
(
@InputParameter VARCHAR(100) 
)
AS
BEGIN
	IF EXISTS(SELECT 1 FROM SYS.COLUMNS WHERE OBJECT_ID = OBJECT_ID('My_table') AND [name]=@InputParameter )
	   BEGIN
			SELECT 'Column already exists.' AS [OUTPUT];
	   END
	ELSE
	   BEGIN
			DECLARE @SELECT_QUERY NVARCHAR(MAX);
			SELECT @SELECT_QUERY=N' ALTER TABLE My_table
									ADD ' +@InputParameter+ ' VARCHAR(100) ';   
			--	PRINT @SELECT_QUERY;
			EXEC (@SELECT_QUERY);
		SELECT 'Table Altered' AS [OUTPUT];
	   END;
END;
GO

-- UNIT TESTING
EXEC Update_Records 'B';
GO
EXEC Update_Records 'N'
GO

--To View Structure of Table (After)
EXEC SP_HELP My_table;
GO

Thanks for reading and Follow Blog and Show your Support for many more interesting upcoming Posts!

Advertisement

2 thoughts on “SQL Question and Answer #1

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s