存储过程更新需要传递所有参数才能工作(Stored procedure update needs all parameters passed in order to work)

嗨，我一直在尝试制作这个存储过程，以便它可以更新单个参数，而无需放入所有参数。如果传递的值为空或null，我希望以前的值保持不变。正如我现在所知，我必须传递所有参数才能使程序正常工作。请提前帮助，谢谢。

create procedure U_DIS_UpdateProject ( @projectID CHAR(4), @projectName varchar(50), @firmName varchar(50), @fundedbudget decimal(16,2), @startDate date, @status varchar(25), @projectTypeCode char(5), @projectManager char(8) ) as begin UPDATE Project SET projectName =ISNULL(@projectName,projectName), firmName =ISNULL(@firmName,firmName), fundedbudget =ISNULL(@fundedbudget,fundedbudget), startDate =ISNULL(@startDate,startDate), status =ISNULL(@status,status), projectTypeCode =ISNULL(@projectTypeCode,projectTypeCode), projectManager =ISNULL(@projectManager,projectManager) WHERE @projectID=projectID

结束了

exec U_DIS_UpdateProject @projectID ='A003'，@ projectName ='Le Chateau'，@ firmName ='Gotham Restaurants'去

Hi I've been trying to make this stored procedure so that it can update an individual parameter without having to put all the parameters. I want the previous values to remain the same if the passed value is empty or null. As I have it now I have to pass all the parameters in order for the procedure to work. Please help, thanks in advance.

create procedure U_DIS_UpdateProject ( @projectID CHAR(4), @projectName varchar(50), @firmName varchar(50), @fundedbudget decimal(16,2), @startDate date, @status varchar(25), @projectTypeCode char(5), @projectManager char(8) ) as begin UPDATE Project SET projectName =ISNULL(@projectName,projectName), firmName =ISNULL(@firmName,firmName), fundedbudget =ISNULL(@fundedbudget,fundedbudget), startDate =ISNULL(@startDate,startDate), status =ISNULL(@status,status), projectTypeCode =ISNULL(@projectTypeCode,projectTypeCode), projectManager =ISNULL(@projectManager,projectManager) WHERE @projectID=projectID

end go

exec U_DIS_UpdateProject @projectID = 'A003', @projectName = 'Le Chateau', @firmName ='Gotham Restaurants' go

最满意答案

我只想使用CASE语句来检查每一个。它与您的示例中的内容类似，但这也会捕获空字符串。

create procedure U_DIS_UpdateProject ( @projectID CHAR(4), @projectName varchar(50), @firmName varchar(50), @fundedbudget decimal(16,2), @startDate date, @status varchar(25), @projectTypeCode char(5), @projectManager char(8) ) as begin UPDATE Project SET projectName = (CASE WHEN ISNULL(@projectName,'')='' THEN projectName ELSE @projectName END), firmName = (CASE WHEN ISNULL(@firmName,'')='' THEN firmName ELSE @firmName END), fundedbudget = (CASE WHEN ISNULL(@fundedbudget,'')='' THEN fundedbudget ELSE @fundedbudget END), startDate = (CASE WHEN ISNULL(@startDate,'')='' THEN startDate ELSE @startDate END), status = (CASE WHEN ISNULL(@status,'')='' THEN [status] ELSE @status END), projectTypeCode = (CASE WHEN ISNULL(@projectTypeCode,'')='' THEN projectTypeCode ELSE @projectTypeCode END), projectManager = (CASE WHEN ISNULL(@projectManager,'')='' THEN projectManager ELSE @projectManager END) WHERE @projectID=projectID

I would just use CASE statements to check each one. It's similar to what you have in your example, but this will also catch empty strings.

create procedure U_DIS_UpdateProject ( @projectID CHAR(4), @projectName varchar(50), @firmName varchar(50), @fundedbudget decimal(16,2), @startDate date, @status varchar(25), @projectTypeCode char(5), @projectManager char(8) ) as begin UPDATE Project SET projectName = (CASE WHEN ISNULL(@projectName,'')='' THEN projectName ELSE @projectName END), firmName = (CASE WHEN ISNULL(@firmName,'')='' THEN firmName ELSE @firmName END), fundedbudget = (CASE WHEN ISNULL(@fundedbudget,'')='' THEN fundedbudget ELSE @fundedbudget END), startDate = (CASE WHEN ISNULL(@startDate,'')='' THEN startDate ELSE @startDate END), status = (CASE WHEN ISNULL(@status,'')='' THEN [status] ELSE @status END), projectTypeCode = (CASE WHEN ISNULL(@projectTypeCode,'')='' THEN projectTypeCode ELSE @projectTypeCode END), projectManager = (CASE WHEN ISNULL(@projectManager,'')='' THEN projectManager ELSE @projectManager END) WHERE @projectID=projectID

更多推荐