Click here to Skip to main content
12,357,929 members (70,662 online)
Rate this:
 
Please Sign up or sign in to vote.
See more: SQL-Server
Hello guys. I have a problem in sql server .
I create a table and I create a stored proc in another stored proc.
When i execute USP_CRE_USP_INS the sqlserver give me an error.
please help me

CREATE DATABASE TESTDB
GO
CREATE TABLE TEST
(
   ID INT IDENTITY PRIMARY KEY,
   FNAME VARCHAR(50),
   LNAME VARCHAR (50)
)
GO
CREATE PROC USP_CRE_USP_INS
AS
BEGIN
     DECLARE @RESULT VARCHAR(500)
     SET @RESULT=
        'CREATE PROC USP_INS_TEST
         @FNAME VARCHAR(50),
         @LNAME VARCHAR(50)
         AS
         BEGIN
         INSERT TEST(FNAME,LNAME)
         VALUES (@FNAME,@LNAME)
         END  '
     EXEC @RESULT '123','123'
END

And sql server error is:

Msg 203, Level 16, State 2, Procedure USP_CRE_USP_INS, Line 14
The name 'CREATE PROC USP_INS_TEST
         @FNAME VARCHAR(50),
         @LNAME VARCHAR(50)
         AS
         BEGIN
         INSERT TEST(FNAME,LNAME)
         VALUES (@FNAME,@LNAME)
         END  ' is not a valid identifier.
Posted 27-Dec-12 5:31am
Rate this: bad
 
good
Please Sign up or sign in to vote.

Solution 1

I believe you need to surround it with parentheses -- EXEC ( @RESULT '123','123' )
  Permalink  
Comments
@AmitGajjar 27-Dec-12 12:28pm
   
i don't think so...
Rate this: bad
 
good
Please Sign up or sign in to vote.

Solution 2

Looking at your stored procedure here are some suggestions.
If you want to execute another stored procedure(SP) inside a stored procedure this is how you would do it
EXEC USP_INS_TEST '123','123'
Having said that there is no necessity of the stored procedure USP_CRE_USP_INS as it is just trying to execute the stored procedure USP_INS_TEST and there are no other SQL commands in that SP. So you can just directly call the SP USP_INS_TEST instead of calling SP USP_CRE_USP_INS.

SP = Stored Procedure.
  Permalink  
v2

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

  Print Answers RSS
Top Experts
Last 24hrsThis month


Advertise | Privacy | Mobile
Web02 | 2.8.160621.1 | Last Updated 27 Dec 2012
Copyright © CodeProject, 1999-2016
All Rights Reserved. Terms of Service
Layout: fixed | fluid

CodeProject, 503-250 Ferrand Drive Toronto Ontario, M3C 3G8 Canada +1 416-849-8900 x 100