SQL存储过程突然询问参数 [英] SQL Stored Procedure asking for Parameters all of a sudden
问题描述
大家好，
我有一个问题，我希望你们可以帮助它。我有一台服务器在客户端运行SQL 2012，并且正在运行一组已恢复的数据（最后一次在6月左右恢复）。没有安装任何SQL累积更新。
我有许多存储过程需要相当多的参数，但只有部分参数是必需的。直到最近我才一直这么做（尽管最近我不知道，但它之前确实有效）。但现在每个参数都是必需的。有什么可以改变的？我在哪里可以开始寻找。
所以总结一下：在我能够将空白参数传递给SP之前，但现在我不能。我可以指定默认值，但我想知道实际发生了什么。
编辑：这是SProc和调用它的代码：
使用[MyDB]
GO
/ ******对象：StoredProcedure [dbo]。[sp_CCNEW]脚本日期：10/11/2014 7:56 ：56 AM ****** /
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER OFF
GO
ALTER PROCEDURE [dbo]。[sp_CCNEW]
@ipkCID int，
@sLastName VARCHAR（40 ），
@sFirstName VARCHAR（40），
@sInitials VARCHAR（10），
@sSalutation VARCHAR（20），
@sHobbies VARCHAR（100），
@sNotes text
AS
DECLARE @ifkCID INT
SET @ifkCID =（选择ifkCsID FROM tblCC WHERE ipkCCID = @ipkCID）
更新tblCC SET
sLastName = @sLastName，
sFirstName = @sFirstName，
sInitials = @sInitials，
sSalutation = @sSalutation，
sHobbies = @sHobbies，
sNotes = @sNotes
在哪里ipkCCID = @ipkCID
调用它的代码：
Hi all,
I have a bit of a problem that I'm hoping you guys can assist with it. I have a server running SQL 2012 at a client and am running a restored set of their data (last restored around June). Neither has any SQL Cumulative updates installed.
I have a number of Stored Procedures that expects quite a few parameters, but only some of these are mandatory. I have been running this fine until recently (although exactly how recently I don't know, but it did work before). But now every single parameter is required. What could have changed? Where can I start looking.
So just to sum up: Before I was able to pass blank parameters to the SP, but now I can't. i can assign default values, but I want to know what actually happened.
Here is the SProc and the code calling it:
USE [MyDB]
GO
/****** Object: StoredProcedure [dbo].[sp_CCNEW] Script Date: 10/11/2014 7:56:56 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER OFF
GO
ALTER PROCEDURE [dbo].[sp_CCNEW]
@ipkCID int,
@sLastName VARCHAR (40),
@sFirstName VARCHAR (40),
@sInitials VARCHAR (10),
@sSalutation VARCHAR (20),
@sHobbies VARCHAR (100),
@sNotes text
AS
DECLARE @ifkCID INT
SET @ifkCID = (SELECT ifkCsID FROM tblCC WHERE ipkCCID = @ipkCID)
Update tblCC SET
sLastName = @sLastName,
sFirstName = @sFirstName,
sInitials = @sInitials ,
sSalutation = @sSalutation ,
sHobbies = @sHobbies ,
sNotes = @sNotes
WHERE ipkCCID = @ipkCID
The code calling it:
conDB.Execute ("EXEC sp_CCNew " & _
"@ipkCID=" & txtContactID & ", " & _
"@sLastName='" & SQLReplace(txtLastName.Text) & "', " & _
"@sFirstName='" & SQLReplace(txtFirstName.Text) & "', " & _
"@sInitials='" & SQLReplace(txtInitials.Text) & "', " & _
"@sSalutation='" & SQLReplace(txtSalutation.Text) & "', " & _
"@sHobbies='" & SQLReplace(txtHobbies.Text) & "', " & _
"@sNotes='" & SQLReplace(txtContactNotes.Text)
SQLReplace是一个用''替换空格的函数。 />
我最初认为它可能是设置的报价标识符，但我在客户端做了同样的事情（将其更改为OFF），并没有改变他们的一面...... />
PS：我刚刚重新检查了客户端和我的一面，它确实有效。引用标识符可以负责吗？
SQLReplace is a function that replaces blanks with ''.
I originally thought it might be the set quote identifiers, but I did the same at the client (changed it to OFF) with no change on their side...
PS:I have just rechecked the client's side and mine and it works. Could the Quote Identifier be responsible?
推荐答案
SQL存储过程中不需要的参数是什么设置它的默认值 - http://technet.microsoft.com/en-us/library/ms189330(v=sql.105） .aspx [ ^ ]
在您的SP中，没有任何参数具有默认值，因此所有参数都需要...
What turns a parameter in SQL stored procedure non-required is setting a default value for it - http://technet.microsoft.com/en-us/library/ms189330(v=sql.105).aspx[^]
In your SP none of the parameters has default value, so all of them required...
这篇关于SQL存储过程突然询问参数的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
