Click here to Skip to main content
15,867,835 members
Please Sign up or sign in to vote.
4.00/5 (1 vote)
See more:
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

I believe you need to surround it with parentheses -- EXEC ( @RESULT '123','123' )
 
Share this answer
 
Comments
AmitGajjar 27-Dec-12 12:28pm    
i don't think so...
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
SQL
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.
 
Share this answer
 
v2

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



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900