Thanks all guys for your valuable suggestions.........especially gail & jeff. thanks again

Here i'm giving a proc for youtr reference.

like this we have near 50 procs in our process.

******************************************************

SET QUOTED_IDENTIFIER OFF

GO

SET ANSI_NULLS ON

GO

CREATE PROCEDURE xMKT_CMD_User.PR_MKT_BUILD_REDEF_LIM_CRIT_FOR_TEMP_TBL

@mktdef_vrsn_id INTEGER, @calling_nbr INTEGER,...

Here it looks in my proc

*********************************

IF @src_id = 3 AND @calling_nbr = 1

BEGIN

CREATE TABLE #PNLPPK_TABLE_LIM_CRT_MSTR(

CTRY_CD nvarchar(3),

PANEL_CD nvarchar(3),

PROD_ID integer,

PPK_ID integer,

ROOT_PROD_REDEFINE_NM nvarchar(90),

PROD_REDEFINE_ID integer,

LOGICAL_DEL_IND CHAR(1) DEFAULT 'I')

CREATE INDEX LIM_MSTR_GBL_IDX1 ON #PNLPPK_TABLE_LIM_CRT_MSTR...