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?
You don't need EXEC clause. Simply use
proc_name paramValue1, paramValue2
(and you need commas as Misnomer mentioned)
You are missing ,
EXEC proc_name 'paramValue1','paramValue2'
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..
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
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.
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.
Select * from Table name ..i.e(are you save table name in sql(TEST) k.
Select * from TEST then you will execute your project.
Success story sharing