Hello @M_rtenH,
I do not have a Snowflake instance to test but I tested with an Azure SQL Database and the corresponding JDBC driver.
In short: yes it works
However, there are a few things to note:
- Using the result of a stored procedure in a
select is not possible. Looking into this, I found a solution using a temp table
- I had an issue with multiple statements in the same query. Therefore, I used another trick to force the result set (see below)
The query I used to:
- create the temp table
- insert the result of the stored procedure
GetUsers
- join the temp table with other tables
IF OBJECT_ID(N'tempdb..#tempusers`') IS NOT NULL
BEGIN
DROP TABLE #tempusers;
END;
create table #tempusers([id] [int] NOT NULL,
[login] [varchar](30) NOT NULL,
[description] [varchar](1024) NULL,
[first] [varchar](128) NULL,
[last] [varchar](128) NULL,
[password] [varchar](26) NULL,
[status] [char](1) NULL,
[locked] [char](1) NULL,
[lastLogin] [date] NULL);
insert into #tempusers exec [dbo].[GetUsers] ;
select t.id, t.[login],t.[first], t.[last]
, groups.name as groups
from #tempusers t left join dbo.users_groups g on g.user_id=t.id left join dbo.groups on groups.id=g.group_id order by t.id;
Wrapping all this in an EXEC (beware to quotes) and forcing the result set:
EXEC('
IF OBJECT_ID(N''tempdb..#tempusers`'') IS NOT NULL
BEGIN
DROP TABLE #tempusers;
END;
create table #tempusers([id] [int] NOT NULL,
[login] [varchar](30) NOT NULL,
[description] [varchar](1024) NULL,
[first] [varchar](128) NULL,
[last] [varchar](128) NULL,
[password] [varchar](26) NULL,
[status] [char](1) NULL,
[locked] [char](1) NULL,
[lastLogin] [date] NULL);
insert into #tempusers exec [dbo].[GetUsers] ;
select t.id, t.[login],t.[first], t.[last]
, groups.name as groups
from #tempusers t left join dbo.users_groups g on g.user_id=t.id left join dbo.groups on groups.id=g.group_id order by t.id;')
WITH RESULT SETS (
(
[id] [int],
[login] [varchar](30),
[first] [varchar](128) ,
[last] [varchar](128),
[groups] [varchar](30)
)
)
I was able to use this last query in IDN as the “Account SQL Query” in the config of my source and it worked!
Hope this helps!
//<account_identifier>.snowflakecomputing.com/