CODEWITHSUNDEEP
sql-servert-sql
user1386445
user1386445

Reputation: 17

t-sql calling function in stored procedure assistance

Could you please assist me in writing this code?

//** 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

Not sure how to call the Fngethandle function created above:

Update tbl_list_delete  
Set deletehandle- @handle 
Where card_id - @card_id, deletehandle = 0  --soft delete

Upvotes: 1

Views: 9366

Answers (1)

marc_s
marc_s

Reputation: 754348

You just need to call the function once before your update statement - something like:

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

Word of warning: you should not use the sp_ prefix for your stored procedures! That prefix has been reserved by Microsoft for its own use - try to avoid it at all costs. Depending on how you call your stored procedures with that prefix, you might also incur an unnecessary lookup in the master database first - so just try to stay away from that prefix!

Update: it's bad design to actually insert data in a function that's called Get.... - you're might be surprising your callers...

What I would do: do all this logic directly in the stored procedure; I'm not sure what your table looks like - but you could try something like this:

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

Upvotes: 4

Related Questions