SQL Server - Dynamic stored procedure to create table and indices ...

This a two parts question:

(1) I appended to a stored procedure that creates a table to include the step of creating a non-clustered index however, when I run the procedure the section that creates the non-clustered index is not executed.  Can someone tell me what I'm doing wrong?  Below is the code.

(2) The second part of my question is I want to call this stored procedure from another store procedure.  Do I need to do anything special to call a stored procedure from another stored procedure?  The initial procedure will call  and pass two parameters to this procedure; once executed it will then proceed to call another procedure to perform additional steps.  (I tried to call this procedure from an initial procedure but the part that is to create the non-clustered index is not being executed; I'm not sure if this a separate issue as to how I'm calling the procedure.)

Thank you for your help.

USE [HCDA]
GO
/****** Object:  StoredProcedure [dbo].[makeHAC_tbl]    Script Date: 03/21/2016 11:55:01 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

-- =============================================
-- Author:		XXXXXXXXXXXX
-- Create date:       March, 2016
-- Description:	This routine creates HAC table
-- =============================================

ALTER PROCEDURE [dbo].[makeHAC_tbl] 
	@dataSrc	NVARCHAR(2), 
	@dataYear	NVARCHAR(2)
AS

BEGIN TRY
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	
	SET NOCOUNT ON;

	DECLARE @sql varchar(max), 
	        @tablename    varchar(max), 
	        @dbFileGroup  nvarchar(max), 
	        @ndxFileGroup nvarchar(max),
	        @primaryKey   varchar(max), 
	        @indexName    varchar(max)
	
	SET @tablename    =  @dataSrc + '_HAC_' + @dataYear;
	SET @dbFileGroup  =  'SQL_' + @dataSrc + '_FILEGRP';
	SET @primaryKey   =  @tablename + '_PK';
	SET @ndxFileGroup =  'SQL_' + @dataSrc + '_NDX_FILEGRP';
	SET @indexName    =  @tablename + '_NDX01$BMAP';
	
	------------------------------------------------------------
	-- If table that is to be created exist, drop existing table
	
	IF EXISTS(SELECT name 
			  FROM   sys.objects 
			  WHERE  name = @tablename AND type = 'U')
				BEGIN
					SELECT @sql = 'DROP TABLE dbo.[' + @tablename + '];'
					EXEC  (@sql);
					PRINT 'Dropped table ' + @tablename + '.'
				END
	
	--------------------------------------------------------------
	-- Create HAC table
	
	SELECT @sql = 'CREATE TABLE [dbo].['+ @tablename + '] 
				   (
						cnvrsn_src_cd		varchar (5)		NOT NULL,
						lhs_rec_num			numeric (8,0)	NOT NULL,
						rcv_dt				datetime2 (0)	NULL,
						hac_flg_1			varchar (1)		NULL,
						hac_flg_2			varchar (1)		NULL,
						hac_flg_3			varchar (1)		NULL,
						hac_flg_4			varchar (1)		NULL,
						hac_flg_5			varchar (1)		NULL,
						hac_flg_6			varchar (1)		NULL,
						hac_flg_7			varchar (1)		NULL,
						hac_flg_8			varchar (1)		NULL,
						hac_flg_9			varchar (1)		NULL,
						hac_flg_10			varchar (1)		NULL,
						hac_flg_11			varchar (1)		NULL,
						hac_flg_12			varchar (1)		NULL,
						hac_flg_13			varchar (1)		NULL,
						hac_flg_14			varchar (1)		NULL,
						
						CONSTRAINT [' + @primaryKey + '] PRIMARY KEY CLUSTERED 
							(cnvrsn_src_cd ASC, 
							 lhs_rec_num   ASC)
						WITH (IGNORE_DUP_KEY = OFF) ON ' + @dbFileGroup +
						
				   ') ON ' + @dbFileGroup + ';'

		EXEC(@sql);
		PRINT 'HAC table created.'
		
--------------------------------------------------------------
	-- If HAC index exist, first drop index and then Create HAC index


	IF EXISTS (	SELECT name 
				FROM   sys.indexes 
				WHERE  name = @indexName ) 
				BEGIN
					print 'calling drop index statement';
					SELECT @sql = 'DROP INDEX [' + @indexName + '] ON [dbo].[' + @tablename + '];'
					EXEC  (@sql);
					PRINT 'Dropped index ' + @indexName + '.';
				END

		
	SELECT @sql = 'CREATE NONCLUSTERED INDEX [' + @indexName + '] ON [dbo].[' + @tablename + ']
				   (  
						HAC_FLG_1     ASC,
						CNVRSN_SRC_CD ASC,
						LHS_REC_NUM   ASC
				   ) ON [' + @ndxFileGroup + '];'
				
	EXEC (@sql);
	PRINT 'HAC index created.'		
	
END TRY


BEGIN CATCH
	SELECT ERROR_NUMBER()	AS ErrorNumber
		,  ERROR_SEVERITY() AS ErrorSeverity
		,  ERROR_STATE()	AS ErrorState
		,  ERROR_PROCEDURE()AS ErrorProcedure
		,  ERROR_LINE()		AS ErrorLine
		,  ERROR_MESSAGE()	AS ErrorMessage;
END CATCH

Open in new window

Maria TorresData AnalystAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

dbaSQLCommented:
I would encourage you to reconsider the dynamic.  Or at least be aware of the risks of using it within your environment.    here's a good piece on it:
https://blogs.msdn.microsoft.com/raulga/2007/01/04/dynamic-sql-sql-injection/

if and when you have to use dynamic, it is very good to give yourself the ability to actually see what SQL is firing with each of your @sql statements.  Take a look at the attached.  You will see I've enabled a @debug parameter that you can use with your statements to do just that.  Note the sample execution statement that I have included up top, as well as the notes I have input down by the index drop statement.

Just run it as is, and look at your output. That should tell you what's going on.  

Personally, my thought was that you were using @sql parm all the way through it, with the table and index drop and recreate.  I was thinking you may want to use multiple cmd's, and concatenate them into a single statement, but I didn't go that far.  I just wanted to show you how to see what your statement is running, and that I didn't actually think you needed to run the index drop.  I say that because your statement drops the table if it exists.  hence, there is no need to also drop the index.  Assuming, of course, that the same named index is not on another non-hac table in the database.

but i digress.  please just look at what I have attached.  It should really clear things up with your dynamic executions.  The @dataSrc and @dataYear was just me winging it.
makeHAC_tbl.sql

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Maria TorresData AnalystAuthor Commented:
Thank you for clarifying that if I drop a table there is no need to drop the index; that all associated elements are dropped as well.  Thank you again.
Maria TorresData AnalystAuthor Commented:
I didn't realize that dropping a table would also drop all associated elements.  Thank you for your prompt response to my problem.
dbaSQLCommented:
My pleasure, CarmenMTorres.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server 2008

From novice to tech pro — start learning today.