Click here to Skip to main content
Rate this: bad
good
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 at 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
0 CPallini 385
1 Sergey Alexandrovich Kryukov 262
2 OriginalGriff 200
3 George Jonsson 134
4 Richard MacCutchan 110
0 OriginalGriff 6,344
1 Sergey Alexandrovich Kryukov 5,890
2 CPallini 5,175
3 George Jonsson 3,559
4 Gihan Liyanage 2,522


Advertise | Privacy | Mobile
Web03 | 2.8.140916.1 | Last Updated 27 Dec 2012
Copyright © CodeProject, 1999-2014
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