We help IT Professionals succeed at work.
Get Started

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

87 Views
Last Modified: 2016-03-23
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

Comment
Watch Question
Commented:
This problem has been solved!
Unlock 1 Answer and 4 Comments.
See Answer
Why Experts Exchange?

Experts Exchange always has the answer, or at the least points me in the correct direction! It is like having another employee that is extremely experienced.

Jim Murphy
Programmer at Smart IT Solutions

When asked, what has been your best career decision?

Deciding to stick with EE.

Mohamed Asif
Technical Department Head

Being involved with EE helped me to grow personally and professionally.

Carl Webster
CTP, Sr Infrastructure Consultant
Ask ANY Question

Connect with Certified Experts to gain insight and support on specific technology challenges including:

  • Troubleshooting
  • Research
  • Professional Opinions
Did You Know?

We've partnered with two important charities to provide clean water and computer science education to those who need it most. READ MORE