How execute/open multiple SQL statements in 1 go with ODBC / SQL-Server

Question

What I'm basically trying to do is fire off multiple SQL statements in one go. This works fine as long as they don't return results.

What I want to do is make a temporary table fill it and join it on my existing data:

CREATE TABLE #JaarMaandTable(jaarm int,maandm int)
INSERT INTO #JaarMaandTable (jaarm,maandm) VALUES (2013,9), (2013,10), (2013,11)

SELECT jaarm,maandm, kr.* 
FROM  #JaarMaandTable jm 
LEFT JOIN (
   SELECT  DATEPART(Month, datum) as maand, DATEPART(Year, datum) as jaar ,count(*) as regels mytable 
   FROM agenda 
   WHERE datum >= '20130901' 
   AND datum <= '20131130' 
   GROUP BY DATEPART(Year, datum), DATEPART(Month, datum)
)kr  ON jm.jaarm = kr.jaar AND jm.maandm = kr.maand ORDER BY jaarm, maandm

This is to make use of a temp table to split up results in months even when there's no data for those months.

It works fine in query analyser.

When I try to use "open" on this query, it tells me it doesn't return a cursor. When I "execsql" it, it won't return results. When I split it up, it immediately forgets the #temptable.

You don't need to create a temporary table to do what you want. Would a single query statement fix your problem? — Gordon Linoff
– Gordon Linoff, Commented Sep 19, 2013 at 12:01
Show your code please. Normally, you use a different command when executing a query that doesn't return a result set (ExecSQL, or something similar). — Marcus Adams
– Marcus Adams, Commented Sep 19, 2013 at 12:26
The problem was that with an OEQry.ExeSsql, it executed the SQL fine but didn't return a result set and with OEQry.Open it complained the result is not a cursor type. Splitting it up, then the database "forgot" the temptable between execsql en open. — Pieter B
– Pieter B, Commented Sep 19, 2013 at 12:52

Gordon Linoff · Accepted Answer · 2013-09-19 12:03:25Z

4

You can write the query using a with statement, to avoid the need for a temporary table:

with JaarMaandTable(jaarm int,maandm int) as (
       select 2013, 9 union all
       select 2013, 10 union all
       select 2013, 11
      )
SELECT jaarm,maandm, kr.* 
FROM  JaarMaandTable jm 
LEFT JOIN (
   SELECT  DATEPART(Month, datum) as maand, DATEPART(Year, datum) as jaar ,count(*) as regels mytable 
   FROM agenda 
   WHERE datum >= '20130901' 
   AND datum <= '20131130' 
   GROUP BY DATEPART(Year, datum), DATEPART(Month, datum)
)kr  ON jm.jaarm = kr.jaar AND jm.maandm = kr.maand ORDER BY jaarm, maandm

answered Sep 19, 2013 at 12:03

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Pieter B Over a year ago

I had to change JaarMaandTable(jaarm int,maandm int) to JaarMaandTable(jaarm,maandm) but for the rest it works like a charm.

Collectives™ on Stack Overflow

How execute/open multiple SQL statements in 1 go with ODBC / SQL-Server

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related