sp_delete_jobschedule SQL Server

I had to create a deployment script today which included modifications to an existing job. Ideally the normal process requires taking backup of the existing object and then implementing the modified object.

Could not find a built in function to rename the job, so my approach was to delete schedule of the existing job and create a new job with a different name (appended v2 i.e. version 2) with the same schedule.

sp_delete_jobschedule 
    @job_name = N'my existing job name', 
    @name = N'existing schedule name'

sp_xml_preparedocument and sp_xml_removedocument in SQL Server

Got a chance of using the 2 built in functions below:

sp_xml_preparedocument

sp_xml_removedocument

Implementation: Input to a the procedure is a text datatype which contains XML with account_id's and we just needed the list of account_id's in our procedure.

CREATE PROCEDURE dbo.get_account_info
(account_list text)

AS

BEGIN


DECLARE @ATempTable TABLE    
 (  
  [id] varchar(30) NULL  
 )  


 DECLARE @doc_out int 


 EXEC sp_xml_preparedocument @doc_out OUTPUT, @account_list    
  
 INSERT INTO @ATempTable ([row_id])  
 SELECT [id] FROM OPENXML (@doc_out, '/root/account', 1) WITH (id varchar(30))  
  
 EXEC sp_xml_removedocument @doc_out

SELECT @doc_out

END

Dynamic Sort By in SQL Server

Sort by column is sent as an input to the stored procedure and the sort by field can be different during different web service calls.

Sorting based on different fields at runtime does not need you to generate SQL statements with sort field appended and then execute them dynamically. It can be done as follows:

use mydb
go


set ansi_nulls on
go
set quoted_identifier off
go


create procedure dbo.get_account_info
(@sort_by_column varchar(255))
as
begin

set nocount on

select account_id, name_id, address_id, start_date, end_date, first_name, middle_name, last_name
from dbo.accounts
where status = 'A'

order by
(case @sort_by_column
when 'last_name' then m.last_name
when 'first_name' then first_name
when 'account_id' then account_id
when 'end_date' then end_date
when 'start_date' then start_date
else account_id
end)

end

Hope this helps.