How to Execute SQL Server Stored Procedure in SQL Developer?

Question

I've been given a user account to a SQL Server database that only has privileges to execute a stored procedure. I added the JTDS SQL Server JDBC jar file to SQL Developer and added it as a Third Party JDBC driver. I can successfully log in to the SQL Server database. I was given this syntax for running the procedure:

EXEC proc_name 'paramValue1' 'paramValue2'

When I run this as either a statement or a script, I get this error:

Error starting at line 1 in command:
EXEC proc_name 'paramValue1' 'paramValue2'
Error report:
Incorrect syntax near the keyword 'BEGIN'.

I tried wrapping the statement in BEGIN/END, but get the same error. Is it possible to call the procedure from SQL Developer? If so, what syntax do I need to use?

Tema · Accepted Answer · 2014-06-24T15:54:25.443

259

You don't need EXEC clause. Simply use

proc_name paramValue1, paramValue2

(and you need commas as Misnomer mentioned)

edited Jun 24 '14 at 15:54

answered Sep 21 '12 at 07:24

Tema

4,100
2
22
12

5

EXEC prior proc_name is required in my case – César León Jan 20 '17 at 14:37

score 83 · Answer 2 · answered Nov 09 '10 at 17:56

83

You are missing ,

EXEC proc_name 'paramValue1','paramValue2'

answered Nov 09 '10 at 17:56

Vishal

12,133
17
82
128

1

I added the missing comma, but again, still get the same error. – sdoca Nov 09 '10 at 18:05
What are your parameters if they are varchar then only you need the quotes...other than that can't think of anything...you could try this just run only this in a new windows- `EXEC proc_name` and see if it asks you for the second parameter...then at least you know your sytax is right..if doesnt work means you probanly dont have right stored proc name...try full qualified name.. – Vishal Nov 09 '10 at 18:14
I removed the second paramater and still the same error. I don't think it's running procedure at all. The EXEC command isn't syntax highlighted, so I'm guessing Developer doesn't recognize it even though the connection is to a SQL Server database. But I can't find anything on the web to confirm/deny that. – sdoca Nov 09 '10 at 18:18
Yea..try selecting some records and executing some simple commands see if anything at all works!..best of luck.. – Vishal Nov 09 '10 at 18:22
Unfortunately, my user only has privileges to run the stored procedure. – sdoca Nov 09 '10 at 20:41

score 24 · Answer 3 · edited Jun 09 '21 at 11:02

24

You need to do this:

exec procName 
@parameter_1_Name = 'parameter_1_Value', 
@parameter_2_name = 'parameter_2_value',
@parameter_z_name = 'parameter_z_value'

edited Jun 09 '21 at 11:02

Jaroslav Bezděk

6,967
6
29
46

answered Jul 12 '13 at 16:09

Daniel

241
2
2

score 4 · Answer 4 · edited Jun 09 '21 at 11:02

4

EXECUTE [or EXEC] procedure_name
@parameter_1_Name = 'parameter_1_Value', 
@parameter_2_name = 'parameter_2_value',
@parameter_z_name = 'parameter_z_value'

edited Jun 09 '21 at 11:02

Jaroslav Bezděk

6,967
6
29
46

answered Mar 30 '15 at 13:44

Prasanna Gulhane

70
1
8

score 1 · Answer 5 · answered May 21 '18 at 11:34

I know this is the old one. But this may help others.

I have added SP calling function between BEGIN/END. Here is a working script.

ALTER Proc [dbo].[DepartmentAddOrEdit]
@Id int,
@Code varchar(100),
@Name varchar(100),
@IsActive bit ,
@LocationId int,
@CreatedBy int,
@UpdatedBy int
AS
    IF(@Id = 0)

    BEGIN
    INSERT INTO Department (Code,Name,IsActive,LocationId,CreatedBy,UpdatedBy,CreatedAt)
        VALUES(@Code,@Name,@IsActive,@LocationId,@CreatedBy,@UpdatedBy,CURRENT_TIMESTAMP)

    EXEC dbo.LogAdd @CreatedBy,'DEPARTMENT',@Name
    END

    ELSE

    UPDATE Department SET
        Code = @Code,
        Name = @Name,
        IsActive = @IsActive,
        LocationId = @LocationId,
        CreatedBy = @CreatedBy,
        UpdatedBy = @UpdatedBy,
        UpdatedAt =  CURRENT_TIMESTAMP 
    where Id = @Id

score 1 · Answer 6 · edited Oct 31 '22 at 14:10

1

You need to add a , between the paramValue1 and paramValue2. You missed it.

EXEC proc_name 'paramValue1','paramValue2'

edited Oct 31 '22 at 14:10

TylerH

20,799
66
75
101

answered Dec 09 '20 at 14:03

Pramodi Samaratunga

515
4
13

score 0 · Answer 7 · edited Nov 07 '16 at 17:48

0

EXEC proc_name @paramValue1 = 0, @paramValue2 = 'some text';
GO

If the Stored Procedure objective is to perform an INSERT on a table that has an Identity field declared, then the field, in this scenario @paramValue1, should be declared and just pass the value 0, because it will be auto-increment.

edited Nov 07 '16 at 17:48

Divyang Desai

7,483
13
50
76

answered Nov 07 '16 at 17:16

chri3g91

1,196
14
16

score 0 · Answer 8 · answered Mar 04 '23 at 06:24

0

--worked for me in sql 5.731
--use db;
call procedureName;

answered Mar 04 '23 at 06:24

Aginjith GJ

31
4

score -1 · Answer 9 · edited Oct 31 '22 at 14:10

-1

There are two ways we can call stored procedure

CALL database name'. 'stored procedure name(parameter values); example:- CALL dbs_nexopay_sisd1_dec_23.spr_v2_invoice_details_for_invoice_receipt_sub_swiss(1, 1, 1, 1);
From your MySQL workbench also you can do that. i. Right-click on stored procedure. ii. Send to SQL editor iii. Procedure call.

edited Oct 31 '22 at 14:10

TylerH

20,799
66
75
101

answered Feb 08 '22 at 05:52

madhav

11
2

Your answer could be improved with additional supporting information. Please [edit] to add further details, such as citations or documentation, so that others can confirm that your answer is correct. You can find more information on how to write good answers [in the help center](/help/how-to-answer). – Community Feb 08 '22 at 07:14

score -3 · Answer 10 · edited Jan 16 '19 at 11:19

-3

If you simply need to excute your stored procedure proc_name 'paramValue1' , 'paramValue2'... at the same time you are executing more than one query like one select query and stored procedure you have to add select * from tableName EXEC proc_name paramValue1 , paramValue2...

edited Jan 16 '19 at 11:19

bruno

32,421
7
25
37

answered Apr 08 '15 at 08:09

Joel Prabhu

1
1
5

score -8 · Answer 11 · answered Jul 09 '13 at 14:01

-8

The stored procedures can be run in sql developer tool using the below syntax

BEGIN procedurename(); END;

If there are any parameters then it has to be passed.

answered Jul 09 '13 at 14:01

Santhosh

1

Why are you enclosing a single statement by a statement block? You have to do it for a [series of T-SQL statements](https://msdn.microsoft.com/en-us/library/ms190487.aspx) only. – David Ferenczy Rogožan Nov 10 '15 at 15:36

score -11 · Answer 12 · edited Dec 17 '13 at 11:29

-11

Select * from Table name ..i.e(are you save table name in sql(TEST) k.

Select * from TEST then you will execute your project.

edited Dec 17 '13 at 11:29

Ondrej Janacek

12,486
14
59
93

answered Dec 17 '13 at 11:06

user3110888

1

1

Welcome to stackoverflow. Take [this](http://stackoverflow.com/tour) tour to get to know how this site works and what it is for. What is your answer? – Devraj Gadhavi Dec 17 '13 at 11:26

How to Execute SQL Server Stored Procedure in SQL Developer?

12 Answers12

Linked