SQL examples for SQL Server

DECLARE @MyQuery NVARCHAR(MAX) = 'SELECT * FROM Issues';

EXEC (@MyQuery) AT [LS_TO_JIRA_IN_GATEWAY];

DECLARE @MyQuery NVARCHAR(MAX) = 'SELECT * FROM Issues WITH(SearchBy=''Key'', Key=''10001'')';

EXEC (@MyQuery) AT [LS_TO_JIRA_IN_GATEWAY];

DECLARE @MyQuery NVARCHAR(MAX) = 'SELECT * FROM Issues WITH(SearchBy=''Key'', Key=''10001'', ContineOnErrorForMessage=1, ErrorSubstringToMatch=''Issue does not exist'')';

EXEC (@MyQuery) AT [LS_TO_JIRA_IN_GATEWAY];

DECLARE @MyQuery NVARCHAR(MAX) = 'SELECT * FROM Issues WITH(SearchBy=''Key'', Key=''CS-1'')';

EXEC (@MyQuery) AT [LS_TO_JIRA_IN_GATEWAY];

DECLARE @MyQuery NVARCHAR(MAX) = 'SELECT * FROM Issues WITH(SearchBy=''Key'', Key=''CS-1, CS-2, 10003, 10004'')';

EXEC (@MyQuery) AT [LS_TO_JIRA_IN_GATEWAY];

DECLARE @MyQuery NVARCHAR(MAX) = 'SELECT * FROM Issues WITH(Project=''CS'')';

EXEC (@MyQuery) AT [LS_TO_JIRA_IN_GATEWAY];

DECLARE @MyQuery NVARCHAR(MAX) = 'SELECT * FROM Issues WITH (Jql=''status IN (Done, Closed) AND created > -5d'' )
/*
Useful links:
  https://support.atlassian.com/jira-work-management/docs/jql-fields/
  https://www.atlassian.com/software/jira/guides/jql/tutorials#advanced-search
  https://www.atlassian.com/blog/jira/jql-the-most-flexible-way-to-search-jira-14
  
Other Possible JQL expressions:

Ids (IN):       key IN(10001, 10002, 10003);
Keys (IN):       key IN(CS-1, CS-2, CS-3);
Projects (IN):       project IN(PROJ1, PROJ2, PROJ3);
Status (EQUAL):       status=''Done'';
Date (Expression 1):       created >=-5d;
Date (Expression 2):       created >=startOfMonth() AND created <=now();
Date (Expression 3):       created >=startOfYear() AND created <=startOfDay();
Date (Static):       created >= ''2008/12/31'';
Date (Static with time):       created >= ''2008/12/31 23:59'';
Project and Status (AND + IN):       project=CS AND status NOT IN (''Done'', ''Open'', ''Closed'');
Assignee and Created Date:    assignee is NOT EMPTY and created < -1d;
Text (Contains - Fuzzy):       Summary ~ ''some words'' OR description ~ ''some words'';
Text (Contains - Fuzzy Wildcard):       Summary ~ ''some*'' OR description ~ ''some*'';
Text (Contains - Exact):       Summary ~ ''\"exact words\"'' OR description ~ ''\"exact words\"'';
Text (Does Not Contain - Fuzzy):       Summary !~ ''some words'' OR description !~ ''some words'';
Empty OR Null:   fixVersion is empty OR fixVersion is null;
Is Not Empty OR Is Not Null:    fixVersion is not empty OR fixVersion is not null;
WAS Operator (previous value):       status WAS "Resolved" BY (jsmith,srogen) BEFORE "2019/02/02";
WAS IN Operator:   status WAS IN ("Resolved", "In Progress");
WAS NOT IN Operator:    status WAS NOT IN ("Resolved", "In Progress");
WAS + BY + DURING (date range):       status WAS "Resolved" BY (jsmith,srogen) DURING("2019/02/02", "2020/02/02");
CHANGED operator:    assignee CHANGED;
CHANGED operator (multiple):       status CHANGED FROM "In Progress" TO "Open"

*/';

EXEC (@MyQuery) AT [LS_TO_JIRA_IN_GATEWAY];

DECLARE @MyQuery NVARCHAR(MAX) = 'INSERT INTO Issues(ProjectKey, IssueTypeName, Summary, Description)
VALUES(''SMP'', ''Task'', ''My ticket inserted through the API'', ''A description about an issue'')
WITH (Output=1)';

EXEC (@MyQuery) AT [LS_TO_JIRA_IN_GATEWAY];

DECLARE @MyQuery NVARCHAR(MAX) = 'INSERT INTO Issues(ProjectKey, IssueTypeName, Summary, Description)
SOURCE(''MSSQL'' --OR ODBC
, ''Data Source=localhost;Initial Catalog=Northwind;Integrated Security=true''
--For ODBC
--, ''Dsn=MyOdbcDsn'' 
--, ''Driver={My ODBC Driver Type}...'' 
--Alias must match Supported Input Columns - Use Query Builder to find Input Column Names
,''select top 10 C_ProjKey as ProjectKey, C_Type as IssueTypeName, C_Summary as Summary, C_Desc as Description FROM Orders''

--Static value example
--,''select top 10 ''''CS'''' as ProjectKey, ''''New Feature'''' as IssueTypeName, ''''Task created - <<FUN_SEQUENCE>>'''' Summary, ''''A description <<FUN_NOW>>'''' Description FROM Orders''
)';

EXEC (@MyQuery) AT [LS_TO_JIRA_IN_GATEWAY];

DECLARE @MyQuery NVARCHAR(MAX) = 'UPDATE Issues
SET Summary = ''This is my summary''
   ,Description = ''Lot''''s of stuff to describe''
   ,Labels = ''[ "bugfix" ]''
   ,DueDate = ''2029-10-10''
--WHERE Id=1234   
--WHERE Id=''ISSKEY''   
WITH (
	IssueIdOrKey=''ISSKEY'', --or use Id in where clause 
	Output=1,
	NotifyUsers=0,
	OverrideScreenSecurity=0,
	OverrideEditableFlag=0,
	ContinueOn404Error=0
)''';

EXEC (@MyQuery) AT [LS_TO_JIRA_IN_GATEWAY];

DECLARE @MyQuery NVARCHAR(MAX) = 'UPDATE Issues
SET customfield_10050=''[{"value":"AAA"},{"value":"BBB"}]''	--CUSTOM Checkboxes field update (Must use Raw JSON)
,customfield_10051=''2020-12-31''	--CUSTOM Date field update
,customfield_10052=''2020-12-31T23:59:59''	--CUSTOM DateTime field update

--Custom Dropdown / Radio fields
,customfield_10048_value=''BBB''	--CUSTOM Dropdown field update (Using value - i.e. item label)
--OR--
--,customfield_10048_id=''10022''	--CUSTOM Dropdown field update (Using id - i.e. item id)
--OR--
--,customfield_10048=''{"value":"BBB"}''	--CUSTOM Dropdown (Using Raw value)

,customfield_10053=''["bugfix","test"]''	--CUSTOM Labels field update (Must use Raw JSON)
,customfield_10057=''[{"value":"AAA"},{"value":"BBB"}]''	--CUSTOM Listbox Multiselect field update (Must use Raw JSON)
,customfield_10049=123455555.123	--CUSTOM Number field update
,customfield_10054_text=''Long string...line-1 
	Long string... line-2 
	Long string... line-3''	--CUSTOM Paragraph field update

,customfield_10055_value=''BBB''	--CUSTOM Radio field update by value
--OR--
--,customfield_10055_id=''10023''	--CUSTOM Radio field update

,customfield_10058=''https://zappysys.com''	--CUSTOM Url field update
,customfield_10059_accountId="5dd64082af96bc0efbe55103" --CUSTOM User field update (update by accountId)
--OR--
--,customfield_10059=''{"accountId":"5dd64082af96bc0efbe55103","displayName":"System"}''	--CUSTOM User field update --accountId=5dd64082af96bc0efbe55103
,customfield_10060=''Custom text single line''	--CUSTOM text single line field 
,customfield_10001=''577069e1-1bcd-4b1e-9070-0b2475830d1c''	--CUSTOM Team field update (update by Team Id)
--WHERE Id=10000
WITH (
	IssueIdOrKey=''ISSKEY'', --or use Id in where clause 
	Output=1,
	NotifyUsers=0,
	OverrideScreenSecurity=0,
	OverrideEditableFlag=0,
	ContinueOn404Error=0
)

--For INSERT use same values with same fields like below. For Insert you do not have to specify Id or Key
--INSERT INTO Issues(ProjectKey, IssueTypeName, Summary, Description, customfield_10050,....., customfield_10060) 
--VALUES (''SMP'', ''Task'', ''My ticket inserted through the API'', ''A description about an issue'', ''[{"value":"AAA"},{"value":"BBB"}]'' ,... ''Custom text single line'')

''';

EXEC (@MyQuery) AT [LS_TO_JIRA_IN_GATEWAY];

DECLARE @MyQuery NVARCHAR(MAX) = 'UPDATE Issues
SET Summary = ''This is done on <<fun_now>>''
Where 1=1
WITH (jql=''status=done'' , ContinueOn404Error=0)
--WITH (jql=''key in(10001, 10002, 10003)'' , ContinueOn404Error=0)
--WITH (jql=''key in(CS-1, CS-2, CS-3)'', ContinueOn404Error=0)';

EXEC (@MyQuery) AT [LS_TO_JIRA_IN_GATEWAY];

DECLARE @MyQuery NVARCHAR(MAX) = 'DELETE FROM Issues
WITH (IssueIdOrKey=''10020'', OUTPUT=1, ContinueOn404Error=0)';

EXEC (@MyQuery) AT [LS_TO_JIRA_IN_GATEWAY];

DECLARE @MyQuery NVARCHAR(MAX) = 'DELETE FROM Issues
Where 1=1
WITH (jql=''status=done'' , ContinueOn404Error=0)
--WITH (jql=''key in(10001, 10002, 10003)'' , ContinueOn404Error=0)
--WITH (jql=''key in(CS-1, CS-2, CS-3)'', ContinueOn404Error=0)';

EXEC (@MyQuery) AT [LS_TO_JIRA_IN_GATEWAY];

DECLARE @MyQuery NVARCHAR(MAX) = 'SELECT * FROM Comments';

EXEC (@MyQuery) AT [LS_TO_JIRA_IN_GATEWAY];

DECLARE @MyQuery NVARCHAR(MAX) = 'SELECT * FROM Comments Where IssueId=10003 --OR WITH(Key=''10003'')';

EXEC (@MyQuery) AT [LS_TO_JIRA_IN_GATEWAY];

DECLARE @MyQuery NVARCHAR(MAX) = 'SELECT * FROM Comments WITH(Key=''CS-1'')';

EXEC (@MyQuery) AT [LS_TO_JIRA_IN_GATEWAY];

DECLARE @MyQuery NVARCHAR(MAX) = 'SELECT * FROM Comments WITH (Jql=''status IN (Done, Closed) AND created > -5d'' )';

EXEC (@MyQuery) AT [LS_TO_JIRA_IN_GATEWAY];

DECLARE @MyQuery NVARCHAR(MAX) = 'INSERT INTO Comments(IssueId,Body) VALUES(''CS-2'', ''Commented at <<FUN_NOW>>'')';

EXEC (@MyQuery) AT [LS_TO_JIRA_IN_GATEWAY];

DECLARE @MyQuery NVARCHAR(MAX) = 'INSERT INTO Comments(IssueId,BodyFormatted) VALUES(''CS-3'', ''{ "content": [
    { "content": [
        { "text": "This is a valid ADF formatted comment.",
          "type": "text" }
           ], 
           "type": "paragraph"
        }
       ], 
       "type": "doc", 
       "version": 1 
     } 
}'')';

EXEC (@MyQuery) AT [LS_TO_JIRA_IN_GATEWAY];

DECLARE @MyQuery NVARCHAR(MAX) = 'UPDATE Comments
 SET Body=''Updated at <<FUN_NOW>>''
WHERE Id=10004
WITH(IssueIdOrKey=''CS-2'')';

EXEC (@MyQuery) AT [LS_TO_JIRA_IN_GATEWAY];

DECLARE @MyQuery NVARCHAR(MAX) = 'DELETE FROM Comments WHERE Id=10004 WITH(IssueIdOrKey=''CS-2'')';

EXEC (@MyQuery) AT [LS_TO_JIRA_IN_GATEWAY];

DECLARE @MyQuery NVARCHAR(MAX) = 'SELECT * FROM Fields';

EXEC (@MyQuery) AT [LS_TO_JIRA_IN_GATEWAY];

DECLARE @MyQuery NVARCHAR(MAX) = 'SELECT * FROM Projects';

EXEC (@MyQuery) AT [LS_TO_JIRA_IN_GATEWAY];

DECLARE @MyQuery NVARCHAR(MAX) = 'SELECT * FROM Worklogs Where IssueId=10003 --WITH(Key=''10003'')';

EXEC (@MyQuery) AT [LS_TO_JIRA_IN_GATEWAY];

DECLARE @MyQuery NVARCHAR(MAX) = 'SELECT * FROM Worklogs WITH (Jql=''status IN (Done, Closed) AND created > -5d'' )';

EXEC (@MyQuery) AT [LS_TO_JIRA_IN_GATEWAY];

DECLARE @MyQuery NVARCHAR(MAX) = 'INSERT INTO Projects(ProjectKey, Name, ProjectTypeKey, LeadAccountId, AssigneeType)
VALUES (''TEST'', ''Test Project'', ''software'', ''70122:XXXXXXXX-XXXX-XXXX-XXXX-c5da8c98b9e2'', ''PROJECT_LEAD)
WITH (Output=1)'')';

EXEC (@MyQuery) AT [LS_TO_JIRA_IN_GATEWAY];

DECLARE @MyQuery NVARCHAR(MAX) = 'UPDATE Projects
SET Name = ''My Test Kanban Project''
   ,ProjectCategoryId = 1
WITH (ProjectIdOrKey = ''MYPRJCT'', Output=1, ContinueOn404Error=0)';

EXEC (@MyQuery) AT [LS_TO_JIRA_IN_GATEWAY];

DECLARE @MyQuery NVARCHAR(MAX) = 'DELETE FROM Projects
WITH (ProjectIdOrKey = ''10020'', Output=1, ContinueOn404Error=0)';

EXEC (@MyQuery) AT [LS_TO_JIRA_IN_GATEWAY];

DECLARE @MyQuery NVARCHAR(MAX) = 'SELECT * FROM Users';

EXEC (@MyQuery) AT [LS_TO_JIRA_IN_GATEWAY];

DECLARE @MyQuery NVARCHAR(MAX) = 'INSERT INTO Users(EmailAddress, DisplayName, Name, Password)
VALUES (''my@user.com'', ''John Doe'', ''John'', ''xhedkspstdadaothoua'')
WITH (OUTPUT=1)';

EXEC (@MyQuery) AT [LS_TO_JIRA_IN_GATEWAY];

DECLARE @MyQuery NVARCHAR(MAX) = 'DELETE FROM Users
WITH (OUTPUT=1, accountId = ''547059:136095a0-XXXX-XXXX-XXXX-3e4c66f26551'', ContinueOn404Error=0)';

EXEC (@MyQuery) AT [LS_TO_JIRA_IN_GATEWAY];

DECLARE @MyQuery NVARCHAR(MAX) = 'SELECT * FROM Worklogs';

EXEC (@MyQuery) AT [LS_TO_JIRA_IN_GATEWAY];

DECLARE @MyQuery NVARCHAR(MAX) = 'INSERT INTO Worklogs(TimeSpentInSeconds, Comment, StartedAt)
      VALUES(7200,''My Comment!'',''2020-02-23T16:20:30.123+0000'')
      WITH (IssueIdOrKey=''ISSKEY-1'', OUTPUT=1)';

EXEC (@MyQuery) AT [LS_TO_JIRA_IN_GATEWAY];

DECLARE @MyQuery NVARCHAR(MAX) = 'UPDATE Worklogs
SET TimeSpentInSeconds = 28800
   ,Comment=''My Comment!''
   ,StartedAt=''2020-01-23T16:20:30.123+0000''
WITH (IssueIdOrKey=''MTK-1'', WorklogId=''123465'', OUTPUT=1, ContinueOn404Error=0)';

EXEC (@MyQuery) AT [LS_TO_JIRA_IN_GATEWAY];

DECLARE @MyQuery NVARCHAR(MAX) = 'DELETE FROM Worklogs
WITH (IssueIdOrKey=''10020'', WorklogId=''123465'', OUTPUT=1, ContinueOn404Error=0)';

EXEC (@MyQuery) AT [LS_TO_JIRA_IN_GATEWAY];

DECLARE @MyQuery NVARCHAR(MAX) = '--(By value)        
UPDATE Issues 
SET customfield_10048_value =''BBB'' --supply value (label) of dropdown/radio
WITH (IssueIdOrKey=''10020'')

--OR-- (By item ID)

UPDATE Issues 
SET customfield_10048_id =10023 --supply id of dropdown/radio item
WITH (IssueIdOrKey=''10020'')

--OR-- (Raw id)

UPDATE Issues 
SET customfield_10048=''{"id":"10023"}'' --supply raw json
WITH (IssueIdOrKey=''10020'')

--OR--  (Raw value)
UPDATE Issues 
SET customfield_10048=''{"value":"BBB"}'' --supply raw json
WITH (IssueIdOrKey=''10020'')

--OR--  (set null)

UPDATE Issues 
SET customfield_10048 =null 
WITH (IssueIdOrKey=''10020'')';

EXEC (@MyQuery) AT [LS_TO_JIRA_IN_GATEWAY];

DECLARE @MyQuery NVARCHAR(MAX) = 'UPDATE Issues 
SET customfield_10048=''[{"value":"AAA"}, {"value":"CCC"}]'' --supply raw json
WITH (IssueIdOrKey=''10020'')

--OR-- 

UPDATE Issues 
SET customfield_10048 =null --set to null
WITH (IssueIdOrKey=''10020'')';

EXEC (@MyQuery) AT [LS_TO_JIRA_IN_GATEWAY];