Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 SQL Server 2000 Forums
 Transact-SQL (2000)
 Run stored procedure within stored procedure

Author  Topic 

vanbomh
Starting Member

1 Post
Posted - 2005-09-22 : 12:30:59
I am new to SQL programming and thought that someone may be able to help. I'm using Microsoft SQL 7.0.

I want to run a stored procedure that creates a table based on the results of another stored procedure. What I have is something like this:

CREATE PROCEDURE make_table_wk_civilian_position AS
SELECT GETDATE() AS [Table As of Date/Time], * INTO wk_civilian_position
FROM CSU_Civilian_Position_SECV

When I run this, I get the following error:

Server: Msg 208, Level 16, State 3, Procedure make_table_wk_civilian_position, Line 2
Invalid object name 'CSU_Civilian_Position_SECV'.

What do I have to do to get the second stored procedure to run?

The second procedure looks like this:

CREATE PROCEDURE CSU_Civilian_Position_SECV AS
SELECT
CSUDB.CIVILIAN_POSITION_SECV.Position_ID,
POSITION_EMPLOYEE_SECV.EMPLOYEE_NUMBER,
POSITION_EMPLOYEE_SECV.SSAN_EMPL_CON_NR "SSAN",
CSUDB.CIVILIAN_POSITION_SECV.CPCN "Position_Description_NR",
CSUDB.CIVILIAN_POSITION_SECV.POSITION_SEQUENCE,
CSUDB.CIVILIAN_POSITION_SECV.CPCN +'.'+ CSUDB.CIVILIAN_POSITION_SECV.POSN_TITLE + '.' + CSUDB.CIVILIAN_POSITION_SECV.POSITION_SEQUENCE +'.'+ CSUDB.CIVILIAN_POSITION_SECV.GP_AGCY_FLAG + '.' + CSUDB.Position_Employee_Secv.Posn_Type "Position",
CSUDB.CIVILIAN_POSITION_SECV.AGCY_CD,
CSUDB.CIVILIAN_POSITION_SECV.AGCY_SUB_ELM_FLAG,
EULOWNER.GET_CLEAR_TEXT(450,CSUDB.CIVILIAN_POSITION_SECV.AGCY_SUB_ELM_FLAG) "AGCY_SUB_ELM_FLAG_Clt",
CSUDB.CIVILIAN_POSITION_SECV.AGENCY_SUPPORT_FLAG,
CSUDB.CIVILIAN_POSITION_SECV.APPROPRIATION_CODE_1,
CSUDB.CIVILIAN_POSITION_SECV.APPROPRIATION_CODE_2,
CSUDB.CIVILIAN_POSITION_SECV.ASG_CIV_ART_ID,
CSUDB.CIVILIAN_POSITION_SECV.AUTH_CIV_ART_ID,
CSUDB.CIVILIAN_POSITION_SECV.CCPO_ID,
CSUDB.CIVILIAN_POSITION_SECV.CIV_EMP_CAT_GRP,
CSUDB.CIVILIAN_POSITION_SECV.CIV_POS_SCTY_ACS_RQMT,
CSUDB.CIVILIAN_POSITION_SECV.COMP_LEV,
CSUDB.CIVILIAN_POSITION_SECV.CURR_PAY_PLAN,
CSUDB.CIVILIAN_POSITION_SECV.DATE_POSITION_FILLED,
CSUDB.CIVILIAN_POSITION_SECV.DRUG_TEST_RQD,
CSUDB.CIVILIAN_POSITION_SECV.DT_LAST_POSN_AUDIT,
CSUDB.CIVILIAN_POSITION_SECV.DT_OBLN_EXPIR,
CSUDB.CIVILIAN_POSITION_SECV.DT_PD_CLASSIFIED,
CSUDB.CIVILIAN_POSITION_SECV.DT_POSN_ESTB,
CSUDB.CIVILIAN_POSITION_SECV.DT_SES_PROB_EXP,
CSUDB.CIVILIAN_POSITION_SECV.DY_TTL_LN_POS_TTL,
CSUDB.CIVILIAN_POSITION_SECV.ENCUMBERED_FLAG,
CSUDB.CIVILIAN_POSITION_SECV.EXECUTIVE_ALLOCATION_SOURCE,
POSITION_EMPLOYEE_SECV.EXTRACT_AS_OF_DATE,
CSUDB.CIVILIAN_POSITION_SECV.FINANCIAL_STATEMENT,
CSUDB.CIVILIAN_POSITION_SECV.FLSA_CAT,
CSUDB.CIVILIAN_POSITION_SECV.FUNCT_ACCT_AND_SHRED,
CSUDB.CIVILIAN_POSITION_SECV.FUNCT_CLASS_IDF,
CSUDB.CIVILIAN_POSITION_SECV.FY_CONV_CREDIT,
CSUDB.CIVILIAN_POSITION_SECV.GP_AGCY_FLAG,
CSUDB.CIVILIAN_POSITION_SECV.GSA_GEOLOC,
EULOWNER.GET_CLEAR_TEXT(38,CSUDB.CIVILIAN_POSITION_SECV.GSA_GEOLOC) "GSA_Geoloc_Clt",
CSUDB.CIVILIAN_POSITION_SECV.GUN_AMMO_ACCESS_ID,
CSUDB.CIVILIAN_POSITION_SECV.INTELL_PSN_IND,
CSUDB.CIVILIAN_POSITION_SECV.KEY_EMER_ESNTL_POSN,
EULOWNER.GET_CLEAR_TEXT(371,CSUDB.CIVILIAN_POSITION_SECV.KEY_EMER_ESNTL_POSN) "KEY_EMER_ESNTL_POSN_Clt",
CSUDB.CIVILIAN_POSITION_SECV.LANGUAGE_REQUIREMENT,
CSUDB.CIVILIAN_POSITION_SECV.LEO_IND,
CSUDB.CIVILIAN_POSITION_SECV.MOB_PSN_INDICATOR,
CSUDB.CIVILIAN_POSITION_SECV.MOBIL_SERV_REQMT_REASON,
CSUDB.CIVILIAN_POSITION_SECV.MPWR_PCN,
CSUDB.CIVILIAN_POSITION_SECV.MPWR_PCN_DTRA,
CSUDB.CIVILIAN_POSITION_SECV.MPWR_RMKS,
CSUDB.CIVILIAN_POSITION_SECV.NG_AF_AUTH_MIL_GRADE,
EULOWNER.GET_CLEAR_TEXT(519,CSUDB.CIVILIAN_POSITION_SECV.NG_AF_AUTH_MIL_GRADE) "AF_Auth_GR_Clt",
CSUDB.CIVILIAN_POSITION_SECV.NG_AF_MNT,
CSUDB.CIVILIAN_POSITION_SECV.NG_AMSCO,
CSUDB.CIVILIAN_POSITION_SECV.NG_APPT_REQT,
CSUDB.CIVILIAN_POSITION_SECV.NG_AR_AUTH_MIL_GRADE,
EULOWNER.GET_CLEAR_TEXT(519,CSUDB.CIVILIAN_POSITION_SECV.NG_AR_AUTH_MIL_GRADE) "AR_Auth_GR_Clt",
CSUDB.CIVILIAN_POSITION_SECV.NG_AR_MTOE_TDA,
CSUDB.CIVILIAN_POSITION_SECV.NG_AR_MTOE_TDA_PAR_LIN,
CSUDB.CIVILIAN_POSITION_SECV.NG_AR_MTOE_TDA_PAR_LIN_SP,
CSUDB.CIVILIAN_POSITION_SECV.NG_AR_POS_AUTH,
CSUDB.CIVILIAN_POSITION_SECV.NG_AR_POS_REQ,
CSUDB.CIVILIAN_POSITION_SECV.NG_AR_SUB_FIELD,
CSUDB.CIVILIAN_POSITION_SECV.NG_AR_UNIT,
CSUDB.CIVILIAN_POSITION_SECV.NG_AUTH_MOS_AFSC,
CSUDB.CIVILIAN_POSITION_SECV.NG_CPCN_EXCEPTION_CD,
CSUDB.CIVILIAN_POSITION_SECV.NG_FTM_OPT,
EULOWNER.GET_CLEAR_TEXT(697,CSUDB.CIVILIAN_POSITION_SECV.NG_FTM_OPT) "NG_FTM_OPT_Clt",
CSUDB.CIVILIAN_POSITION_SECV.NG_POSN_ID,
CSUDB.CIVILIAN_POSITION_SECV.NG_POSN_IN_LIEU_OF,
CSUDB.CIVILIAN_POSITION_SECV.OCUPTNL_SRS,
EULOWNER.GET_CLEAR_TEXT(466,CSUDB.CIVILIAN_POSITION_SECV.Ocuptnl_Srs) "Ocuptnl_Srs_Clt",
CSUDB.CIVILIAN_POSITION_SECV.OFFICE_SYMBOL,
CSUDB.CIVILIAN_POSITION_SECV.ORG_COMPONENT,
CSUDB.CIVILIAN_POSITION_SECV.ORG_FUNC_CD,
CSUDB.CIVILIAN_POSITION_SECV.ORG_STRUCT_ID_SHRED,
CSUDB.CIVILIAN_POSITION_SECV.ORGANIZATION,
CSUDB.CIVILIAN_POSITION_SECV.PAS_AUTH "PAS",
CSUDB.CIVILIAN_POSITION_SECV.PAS_PERS_OFF_ID,
CSUDB.CIVILIAN_POSITION_SECV.PATCOB,
EULOWNER.GET_CLEAR_TEXT(274,CSUDB.CIVILIAN_POSITION_SECV.PATCOB) "PATCOB_Clt",
CSUDB.CIVILIAN_POSITION_SECV.POSITION_ORGANIZATION,
CSUDB.CIVILIAN_POSITION_SECV.POSITION_STATUS,
CSUDB.CIVILIAN_POSITION_SECV.POSITION_WORKING_TITLE,
CSUDB.CIVILIAN_POSITION_SECV.POSN_ACTION_EFF_DT,
CSUDB.CIVILIAN_POSITION_SECV.POSN_END_DATE,
CSUDB.CIVILIAN_POSITION_SECV.POSN_GR_CIV,
CSUDB.CIVILIAN_POSITION_SECV.POSN_LOC_INDIV,
CSUDB.CIVILIAN_POSITION_SECV.POSN_MGT_REVIEW,
CSUDB.CIVILIAN_POSITION_SECV.POSN_MIL_TO_CIV_CONV,
CSUDB.CIVILIAN_POSITION_SECV.POSN_NTE_DATE,
CSUDB.CIVILIAN_POSITION_SECV.POSN_OBLIG_TYPE,
CSUDB.CIVILIAN_POSITION_SECV.POSN_OCCUPD_ID,
CSUDB.CIVILIAN_POSITION_SECV.POSN_RMRKS_AREA,
CSUDB.CIVILIAN_POSITION_SECV.POSN_SNSVY,
CSUDB.CIVILIAN_POSITION_SECV.POSN_STATUS_IND_CIV,
CSUDB.CIVILIAN_POSITION_SECV.POSN_TITLE,
CSUDB.Position_Employee_Secv.Posn_Type,
CSUDB.CIVILIAN_POSITION_SECV.POSN_WRK_SCHED,
POSITION_EMPLOYEE_SECV.CURR_PAY_PLAN + '-' + POSITION_EMPLOYEE_SECV.OCUPTNL_SRS + '-' + POSITION_EMPLOYEE_SECV.POSN_GR_CIV + '-' + POSITION_EMPLOYEE_SECV.STEP_IN_GRADE_CIV "PP_OCC_Grade_Step",
CSUDB.CIVILIAN_POSITION_SECV.PREM_PAY_IND,
CSUDB.CIVILIAN_POSITION_SECV.PROD_DIST_FLAG,
CSUDB.CIVILIAN_POSITION_SECV.PROG_ELEMENT,
CSUDB.CIVILIAN_POSITION_SECV.PRP_SCI_STATUS_POSN,
CSUDB.CIVILIAN_POSITION_SECV.RCD_ID,
CSUDB.CIVILIAN_POSITION_SECV.RESPONSIBILITY_LEVEL,
CSUDB.CIVILIAN_POSITION_SECV.RIP_DISTRIBUTION_FLAG,
CSUDB.CIVILIAN_POSITION_SECV.SSAN_EMPL_CON_NR_OBL,
CSUDB.CIVILIAN_POSITION_SECV.SUPV_STATUS,
EULOWNER.GET_CLEAR_TEXT(114,CSUDB.CIVILIAN_POSITION_SECV.SUPV_STATUS) "SUPV_STATUS_Clt",
CSUDB.CIVILIAN_POSITION_SECV.TARGET_GR_CIV,
CSUDB.CIVILIAN_POSITION_SECV.TNG_PROG_ID,
CSUDB.CIVILIAN_POSITION_SECV.TYPE_AUTH_REQ,
CSUDB.CIVILIAN_POSITION_SECV.TYPE_EMP_SUPV,
CSUDB.CIVILIAN_POSITION_SECV.UNIT_ID_CD,
CSUDB.CIVILIAN_POSITION_SECV.WARTIME_GRADE,
CSUDB.CIVILIAN_POSITION_SECV.WARTIME_KSC_CO,
CSUDB.CIVILIAN_POSITION_SECV.WARTIME_PAY_PLAN,
CSUDB.CIVILIAN_POSITION_SECV.WARTIME_POSITION,
CSUDB.CIVILIAN_POSITION_SECV.WARTIME_SPT_UNIT,
CSUDB.CIVILIAN_POSITION_SECV.WCP_TYPE,
CSUDB.CIVILIAN_POSITION_SECV.ACQ_CAREER_LVL_REQD,
CSUDB.CIVILIAN_POSITION_SECV.ACQ_CONTRTOR_JOB_SITE,
CSUDB.CIVILIAN_POSITION_SECV.ACQ_CRIT_POSN_ID,
CSUDB.CIVILIAN_POSITION_SECV.ACQ_JOB_SPECIALTY_1,
CSUDB.CIVILIAN_POSITION_SECV.ACQ_JOB_SPECIALTY_2,
CSUDB.CIVILIAN_POSITION_SECV.ACQ_POSN_CAREER_CAT,
CSUDB.CIVILIAN_POSITION_SECV.ACQ_PROG_IND,
CSUDB.CIVILIAN_POSITION_SECV.ACQ_SPECIAL_ASSIGN,
CSUDB.CIVILIAN_POSITION_SECV.ACQ_SYS_PROG_OFFICE,
POSITION_EMPLOYEE_SECV.PAYROLL_OFF_ID,
POSITION_EMPLOYEE_SECV.PAYROLL_ORG_CODE,
POSITION_EMPLOYEE_SECV.DATE_POSITION_VACATED,
POSITION_EMPLOYEE_SECV.EMP_DT_OBLIG_EXPIR,
POSITION_EMPLOYEE_SECV.EMP_OBLIG_POSN_TYPE,
POSITION_EMPLOYEE_SECV.PAY_BASIS,
POSITION_EMPLOYEE_SECV.PAY_RATE_DETERM,
POSITION_EMPLOYEE_SECV.PAY_TABLE_IDENT,
POSITION_EMPLOYEE_SECV.TYPE_EMP_SUPV,
POSITION_EMPLOYEE_SECV.PHY_CL_EXAM_OR_TEST,
CSUDB.POSITION_EMPLOYEE_SECV.OCCUPANCY_DATE
FROM CSUDB.CIVILIAN_POSITION_SECV CIVILIAN_POSITION_SECV, CSUDB.Position_Employee_Secv Position_Employee_Secv
WHERE CSUDB.CIVILIAN_POSITION_SECV.Position_ID = CSUDB.Position_Employee_Secv.Position_ID

eyechart
Master Smack Fu Yak Hacker

3575 Posts
Posted - 2005-09-22 : 15:52:05
first thing is start using aliases in your FROM clause so your code is a little more legible.

your are using SELECT INTO syntax which only works with other tables, to create a table that contains the resultset of a stored proc, you can use this syntax:

INSERT INTO tablename
EXEC stored procedure name


You will have to create the empty table prior to populating it with the above command. It won't creat the table for you.

You might want to explore using a table derived function instead of a sproc for your 2nd sproc. You can reference this kind of function from the FROM clause, thus avoiding time consuming step.

Search BOL for examples.



-ec
Go to Top of Page
   

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources

Articles

Forums

Blogs

Contact Us

About the Site