如何在SQL Developer中执行SQL Server存储过程?

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:

我已经获得了一个SQL Server数据库的用户帐户，该数据库只具有执行存储过程的特权。我将JTDS SQL Server JDBC jar文件添加到SQL Developer中，并将其作为第三方JDBC驱动程序添加进来。我可以成功登录到SQL Server数据库。我得到了运行过程的语法:

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?

我尝试在开始/结束中包装语句，但是得到了相同的错误。是否可以从SQL Developer中调用过程?如果有，我需要使用什么语法?

9 个解决方案

#1

193

You don't need EXEC clause. Simply use

你不需要执行条款。简单地使用

proc_name paramValue1, paramValue2

(and you need commas as Misnomer mentioned)

(你还需要逗号，这是错误的。)

#2

You are missing ,

你是失踪,

EXEC proc_name 'paramValue1','paramValue2'

#3

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'

#4

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

#5

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.

如果存储过程的目标是在具有标识字段声明的表上执行插入，那么在这个场景中，应该声明@paramValue1字段，并且只传递值0，因为它将自动递增。

#6

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.

我在开始/结束之间添加了SP调用函数。这是一个可用的脚本。

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

#7

-2

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

如果您只是需要取消存储过程proc_name 'paramValue1'、'paramValue2'…与此同时，您还可以进行更多的查询，比如一个select查询和存储过程，您必须从tableName EXCE proc_name paramValue1, paramValue2…

#8

-6

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

可以使用下面的语法在sql developer工具中运行存储过程。

BEGIN procedurename(); END;

开始procedurename();结束;

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

如果有任何参数，就必须通过。

#9

-9

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

193