Question

SQL下，写了一个存储过程，但是在执行时总是提示【没有参数,但却为该过程提供了参数】。

存储过程为：
USE [ecology]
GO

/****** Object: StoredProcedure [dbo].[crm_shareinfo_contract] Script Date: 01/05/2017 19:04:56 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE procedure [dbo].[crm_shareinfo_contract] as
declare @crmid INT
declare @contractid INT
declare @contents varchar(600) set @contents=''
declare @sharetype int set @sharetype=0
declare @seclevel int set @seclevel=0
declare @sharelevel int set @sharelevel=0
declare @seclevelMax int set @seclevelMax=100
declare @foralluser int set @foralluser=0
declare @isdefault int set @isdefault=1
declare @rolelevel int set @rolelevel=0
begin
declare cursor0 cursor for
select sharetype,contents,seclevel,sharelevel,seclevelMax,foralluser from CRM_shareInfo where relateditemid=@crmid and deleted=0 order by id;
open cursor0
fetch next from cursor0 into @sharetype,@contents,@seclevel,@sharelevel,@seclevelMax,@foralluser
while (@@fetch_status=0)
begin
insert into Contract_ShareInfo
(relateditemid,sharetype,seclevel,sharelevel,seclevelMax,foralluser,userid,departmentid,roleid,subcompanyid,rolelevel,isdefault)
values
(@contractid,@sharetype,@seclevel,@sharelevel,@seclevelMax,@foralluser,@contents,@contents,@contents,@contents,@rolelevel,@isdefault)
fetch next from cursor0 into @sharetype,@contents,@seclevel,@sharelevel,@seclevelMax,@foralluser
end
close cursor0
deallocate cursor0
end

GO

执行为：EXECUTE crm_shareinfo_contract 3,3

Answer 1

你没有定义参数，而在执行时却提供了2个参数！
估计是你语法不熟悉吧。
----------------
CREATE PROCEDURE proName (
@param1 DataType

,@param2 DataType = 缺省值

) AS
BEGIN
...

END

追问

确实不熟，能把我的存储过程改好了发给我吗？非常感谢。

Answer 2

存储过程的参数不需要declare

追问

我尝试着去了，但是去了后执行的时候，提示必须要定义参数才行

追答

参数写完下面跟个begin

追问

我不是很懂，能麻烦改好了发给我吗？过程我已经提供了

追答

CREATE procedure [dbo].[crm_shareinfo_contract]
@crmid INT,
@contractid INT
as
begin

追问

不行，报错

追答

第一个as 去掉啊