Is it possible to stop and delete a Workflow instance or to short close using Store Procedure

You can run this query to check for running instances:

SELECT *
FROM Server]..ProcInst]
WHERE Status]=1

To stop the running instance, you can run the following Update query:

UPDATE EServer].]ProcInst]
SET TStatus] = 4
WHERE EStatus] = 1  AND ID = <ProcInstID> 

You may need to pass in an ID or Folio name to filter the instances according to your requirements to stop an active workflow.

You can use the following stored procedure to remove instances:

exec pServer].kProcInstRemove @ProcInstID=ProcInstId],@ServerID=0,@LogData=0x0,@ExpireOnDelete=1

exec /Serverlog].lProcInstRemove @ProcInstID=IProcInstId]

This worked perfect, thank you so much for your help! But just asking out of curiosity, are we allowed to touch K2 DB and use SPs ? I mean does it cause any issue with Nintex K2 Support.

You are 100% correct - you should never run stored procedures against the database from your own code / solutions - this will potentially leave you in an unsupported state.

You should either use the management portal to do this, or if this is required as part of a process then you should design you process in such a way that it has an outcome on the relevant task(s) that allows the WF to be complete by calling that outcome in the process itself.  This will not however delete the process, only complete it.

It would also be possible to create a custom ServiceBroker or an Endpoints Assembly  that uses the SourceCode.Workflow.Management API - the WorkflowManagementServer class has both a StopProcessInstance and DeleteProcessInstance methods.  Have a look here for some examples from that API (but not those two specific methods)

Cheers