嗨,我一直在尝试制作这个存储过程,以便它可以更新单个参数,而无需放入所有参数。 如果传递的值为空或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=projectIDend 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=projectIDI 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更多推荐
发布评论