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?

11 Answers

You don't need EXEC clause. Simply use

proc_name paramValue1, paramValue2

(and you need commas as Misnomer mentioned)

3

You are missing ,

EXEC proc_name 'paramValue1','paramValue2'

5

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'

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

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 

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

EXEC proc_name 'paramValue1','paramValue2'

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.

There are two way's we can call stored procedure

1. 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);

2. From your MySQL workbench also you can do that. i. Right-click on stored procedure. ii. Send to SQL editor iii. Procedure call.

1

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...

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.

1

Select * from Table name ..i.e(are you save table name in sql(TEST) k.
Select * from TEST then you will execute your project.

1

Your Answer

Sign up or log in

Sign up using Google Sign up using Facebook Sign up using Email and Password Submit

Post as a guest

Post Your Answer Discard

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy