Fabric : Creating and executing views and stored procedures in Microsoft Fabric

Introduction

In this blogpost I'll try to install and execute some of the views and stored procedures that comes along with the Pubs database.

Views and procedures

The first VIEW I tried was the following script. It will give an error

'CREATE VIEW' must be the first statement in a query batch.

Msg 111, Level 15, State 9, Code line 3

DROP VIEW IF EXISTS titleview;
CREATE VIEW titleview
AS
select title, au_ord, au_lname, price, ytd_sales, pub_id
from authors, titles, titleauthor
where authors.au_id = titleauthor.au_id
   AND titles.title_id = titleauthor.title_id;

Replacing the ; with a GO succeeds. 

DROP VIEW IF EXISTS titleview
GO
CREATE VIEW titleview
AS
select title, au_ord, au_lname, price, ytd_sales, pub_id
from authors, titles, titleauthor
where authors.au_id = titleauthor.au_id
   AND titles.title_id = titleauthor.title_id;

Later on, I decided to replace all of ; and then all the statements worked very smoothly. This is the script. It succeeds to install, but I noticed that there are "Money" data types in the statements and Money is not supported. I expect that the stored procedure will fail when I try to execute them.

DROP VIEW IF EXISTS titleview
GO
CREATE VIEW titleview
AS
select title, au_ord, au_lname, price, ytd_sales, pub_id
from authors, titles, titleauthor
where authors.au_id = titleauthor.au_id
   AND titles.title_id = titleauthor.title_id
GO
DROP PROCEDURE IF EXISTS byroyalty
GO
CREATE PROCEDURE byroyalty @percentage int
AS
select au_id from titleauthor
where titleauthor.royaltyper = @percentage
GO
DROP PROCEDURE IF EXISTS reptq1
GO
CREATE PROCEDURE reptq1 AS
select 
    case when grouping(pub_id) = 1 then 'ALL' else pub_id end as pub_id, 
    avg(price) as avg_price
from titles
where price is NOT NULL
group by pub_id with rollup
order by pub_id
GO

DROP PROCEDURE IF EXISTS reptq2
GO
CREATE PROCEDURE reptq2 AS
select 
    case when grouping(type) = 1 then 'ALL' else type end as type, 
    case when grouping(pub_id) = 1 then 'ALL' else pub_id end as pub_id, 
    avg(ytd_sales) as avg_ytd_sales
from titles
where pub_id is NOT NULL
group by pub_id, type with rollup
GO
DROP PROCEDURE IF EXISTS reptq3
GO
CREATE PROCEDURE reptq3 @lolimit money, @hilimit money,
@type char(12)
AS
select 
    case when grouping(pub_id) = 1 then 'ALL' else pub_id end as pub_id, 
    case when grouping(type) = 1 then 'ALL' else type end as type, 
    count(title_id) as cnt
from titles
where price >@lolimit AND price <@hilimit AND type = @type OR type LIKE '%cook%'
group by pub_id, type with rollup
GO

Strangly enough when I try to execute the stored procedures, they all work well, ecen the one with the money data type as parameter.

EXEC byroyalty 60

results in :

EXEC reptq1

results in :

EXEC reptq2

results in :

EXEC reptq3 10, 1000, 'psychology'

results in :

Final thoughts

Views and Procedures were easy to install and executing went all very well but the money type as a parameter is accepted and no errors happened. The money data is not supported by Microsoft Fabric but may be this is not checked by the engine.

Hennie