ALTER PROCEDURE [dbo].[createTimeFrameReport]
AS
--BEGIN TRAN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
--declare @currentYear varchar (4)
--declare @currentMonth varchar(3)
--declare @currentDay varchar(3)
DECLARE @applicationNo varchar(20);
TYPE ListofIDs IS VARRAY(100) OF NUMBER;
//how to assign value for below code a.APPLICATION_ID into an array
SELECT @ListofIDs =a.APPLICATION_ID from BPM_PROCESS_INSTANCE a,BPM_TASK_INSTANCE b,BPM_PROCESS c where b.PROCESS_INSTANCE_ID=a.ID and c.ID=a.TYPE_ID and a.TYPE_ID=42
AND b.ASSIGNED_ROLE IN('IDB_Reviewer','IFP_TechReviewerPermitting','IFP_ProcessManager','IFP_TechReviewerAssessment')
select @ListofIDs
Add a comment
|
2 Answers
In SQL there is not Array variable, however some SQL features replaces the logic of that array, it depend on how you use it, and i think what you are looking for is Temporary Tables
how to create temporary tables ? , to create temp table you need to have a hashtag sign # before the name of the temp table. see sample below (2 ways to create temp table
Using CREATE TABLE
CREATE TABLE #testTempTable ( Column1 DataType, Column2 DataType, Column3 DataType, etc... )Using SELECT INTO #testTempTable
SELECT Column1, Column2, Column3 INTO #testTempTable FROM SourceTableNameHere
There is also called Variable Table in SQL , you can google it to know how to use it.
NOTE: it is best practice to drop the temporary table at the end of the script to avoid errors when the script contains temp table runs in the 2nd time.
sytanx:
DROP TABLE #testTempTable
Hope it helps.
Comments
SQL Server has not array type but you can use table variables or temp tables instead.
Also please don't use outdated comma syntax, use JOIN ON instead.
TEMP TABLE:
SELECT a.APPLICATION_ID
INTO #ListofIDs
FROM BPM_PROCESS_INSTANCE a
JOIN BPM_TASK_INSTANCE b
ON b.PROCESS_INSTANCE_ID = a.ID
JOIN BPM_PROCESS c
ON c.ID = a.TYPE_ID
WHERE a.TYPE_ID = 42
AND b.ASSIGNED_ROLE IN('IDB_Reviewer',
'IFP_TechReviewerPermitting',
'IFP_ProcessManager',
'IFP_TechReviewerAssessment');
SELECT #ListofIDs;
TABLE VARIABLE:
DECLARE @ListofIDs TABLE
(
APPLICATION_ID int
);
INSERT INTO @ListofIDs(APPLICATION_ID)
SELECT a.APPLICATION_ID
FROM BPM_PROCESS_INSTANCE a
JOIN BPM_TASK_INSTANCE b
ON b.PROCESS_INSTANCE_ID = a.ID
JOIN BPM_PROCESS c
ON c.ID = a.TYPE_ID
WHERE a.TYPE_ID = 42
AND b.ASSIGNED_ROLE IN('IDB_Reviewer',
'IFP_TechReviewerPermitting',
'IFP_ProcessManager',
'IFP_TechReviewerAssessment');
SELECT @ListofIDs;
