About .net Execution Stored Procedure Null Automatically Becomes Default Exception Handling Method

Little scum · Posted on 11/9/2015 4:28:20 PM

ALTER PROCEDURE [dbo].[pUpdateSite]
@ID BIGINT,
@SiteName NVARCHAR(50),
@SiteDesc NVARCHAR(500),
@UpdateBy BIGINT,
@C3Confirm BIT,
@SSOUrl VARCHAR(255),
@AuthConfirm BIT,
@AuthUrl VARCHAR(255),
@Remarks NVARCHAR(500)
AS
BEGIN
SET NOCOUNT ON;
DECLARE @Now DATETIME
SET @Now=GETDATE()
UPDATE tbSite SET SiteName=@SiteName,SiteDesc=@SiteDesc,UpdateBy=@UpdateBy,UpdateDate=@Now,C3Confirm=@C3Confirm,SSOUrl=@SSOUrl,AuthConfirm=@AuthConfirm,AuthUrl=@AuthUrl,Remarks=@Remarks WHERE ID=@ID
END

Copy code

This is my original stored procedure, where there must be 2 fields empty, either c3confirm and ssourl are empty, or authconfirm and authurl are empty,

So, I used .net to execute the stored procedure, but it was always abnormal, and I couldn't find a solution from the Internet, I saw that the SQL statement that .net finally executed was:

exec pUpdateSite @ID=20,@SiteName=N'ohoh',@SiteDesc=N'wait',@UpdateBy=1,@C3Confirm=default,@SSOUrl=default,@AuthConfirm=1,@AuthUrl=N'http://www.baidu.com',@Remarks= N'Win more and lose less'

Finally, my friend told me that the null value should be assigned a default value, so I modified it to the following code, and then it was normal.

ALTER PROCEDURE [dbo].[pUpdateSite]
@ID BIGINT,
@SiteName NVARCHAR(50),
@SiteDesc NVARCHAR(500),
@UpdateBy BIGINT,
@C3Confirm BIT=NULL,
@SSOUrl VARCHAR(255)=NULL,
@AuthConfirm BIT=NULL,
@AuthUrl VARCHAR(255)=NULL,
@Remarks NVARCHAR(500)=NULL
AS
BEGIN
SET NOCOUNT ON;
DECLARE @Now DATETIME
SET @Now=GETDATE()
UPDATE tbSite SET SiteName=@SiteName,SiteDesc=@SiteDesc,UpdateBy=@UpdateBy,UpdateDate=@Now,C3Confirm=@C3Confirm,SSOUrl=@SSOUrl,AuthConfirm=@AuthConfirm,AuthUrl=@AuthUrl,Remarks=@Remarks WHERE ID=@ID
END

Copy code

summer · Posted on 11/10/2015 2:31:22 PM

There are examples of stored procedures, it's okay, I'll search on the Internet

[Source] About .net Execution Stored Procedure Null Automatically Becomes Default Exception Handling Method

Related Posts

Sections viewed