你能帮我写这段代码吗?
//** Stored procedure Generate list of items to be removed **//
Create Sp_Add_pass /*purpose is to generate list to the table to be removed Tbl_list_delete (Handle ,list_id,list_type,delete_handle) */
Parameter
@card_id nvarchar
@card_type nvarchar
Create function Gethandle /* purpose is to auto insert in the table, which returns the handle */
create function [dbo].[fnGetHandle]()
returns int
as
begin
declare @Handle as int;
declare @strUser as nchar(256);
set @Handle = 0 ;
set @strUser = Current_User;
insert into tbl_handle
Output Handle into @Handle output
( UserName )
values
( @strUser );
return @Handle ;
end;
Insert into tbl_list_delete
(Handle ,list_id,list_type,delete_handle)
Values (Handle ,list_id,list_type,delete_handle)
/* once the list of items ready & then I can go ahead and do the soft delete items */
Create Sp_remove_pass
Parameters
@card_id
不知道如何调用上面创建的Fngethandle
函数:
Update tbl_list_delete
Set deletehandle- @handle
Where card_id - @card_id, deletehandle = 0 --soft delete
您只需要在更新语句之前调用该函数一次 - 如下所示:
CREATE PROCEDURE dbo.Sp_remove_pass
@card_id INT
AS BEGIN
DECLARE @Handle INT
SELECT @Handle = dbo.GetHandle()
UPDATE dbo.tbl_list_delete
SET deletehandle - @Handle
WHERE card_id - @card_id, deletehandle = 0 --soft delete
END
警告:不应将sp_
前缀用于存储过程!该前缀已被Microsoft保留供自己使用 - 不惜一切代价避免使用它。根据使用该前缀调用存储过程的方式,您可能还会首先在master
数据库中进行不必要的查找 - 因此请尽量远离该前缀!
更新:在一个名为 Get 的函数中实际插入数据是糟糕的设计...... - 你可能会让你的来电者感到惊讶......
我要做的是:直接在存储过程中执行所有这些逻辑;我不确定你的表格是什么样子的 - 但你可以尝试这样的东西:
CREATE PROCEDURE dbo.proc_RemovePass
@card_id INT
AS BEGIN
DECLARE @Handle INT
INSERT INTO dbo.tbl_handle(UserName)
VALUES(CURRENT_USER)
SELECT @Handle = SCOPE_IDENTITY()
/* I'm assuming here that your table "tbl_Handle" has a column of type
INT IDENTITY which gets a new value for each row inserted - that's the value
my code is reading out here. Change this if that's not the case for you */
UPDATE dbo.tbl_list_delete
-- what do you want to set here? Which column?? Not clear.....
SET DeleteHandle = @Handle
-- your WHERE clause is unclear, too - what are you trying to check for here??
WHERE card_id - @card_id AND DeleteHandle = 0
END