Add a sql server table-valued function in schema

Data Abstract
1

Hi,

is there a way to add to the schema a function that has two parameters and returns a dataset based on these parameters?

If not is there another way to have that kind of functionality?

Thanks in advance

Yiannis

2

Hi,

You can create a stored procedure that will accept two parameters and return required dataset. this stored procedure can be registered as a table in Schema.

3

how do i get the daschema to pull in the column names?

preview works when filling in the right parameters but it can’t add the column names it seems?

4

Hi,

something like

CREATE PROCEDURE dbo.GetTwoParams
    @Param1 VARCHAR(100),
    @Param2 VARCHAR(100)
AS
BEGIN
    SET NOCOUNT ON;
    
    SELECT @Param1 AS Param1, @Param2 AS Param2;
END

drop this SP to tables nodes:

Untitled
Untitled797×605 28 KB

you can check how it works even in design-time. just fill parameters and set table.active = true:

{38FDA8CB-A50F-4AA3-846D-177D2E6D5D2A}
{38FDA8CB-A50F-4AA3-846D-177D2E6D5D2A}1262×885 167 KB

5

Hi,

as I see, DASM can’t preview table created from SP.
Logged as bugs://D19713.

6

that was the question yes…

7

Hi,

you can change column names inside SP:

SELECT @Param1 AS One_Param, @Param2 AS Two_Param;

as a result, fields were changed in DASM too:

Untitled
Untitled323×338 11 KB

8

hmmm i can’t get the field names to appear…

in my case it does not return the params as a select but a straightforward

select fields from temptable

if i fill in the params of the procedure and preview data than that shows but a soon as i try to recreate the fields it throws an error

image

same error for mappings as for fields

9

Hi,

can you create a simple DDL and some instructions how I can reproduce your case, pls?
you can drop it to support@

as a temporary workaround - you can create fields manually for this table (i.e. table from sp) in DASM

10

yes i know that i can create the fields manually…

11

created a simple sp that returns from a simple test table and that works…

create PROCEDURE testDA
@PKParam int = 0,
@StrParam varchar(10)
AS
BEGIN
– SET NOCOUNT ON added to prevent extra result sets from
– interfering with SELECT statements.
SET NOCOUNT ON;

CREATE TABLE #forDA (
    pk       int    NOT NULL PRIMARY KEY,
    n       int    NOT NULL,
    prod    varchar(10) NOT NULL
);

CREATE TABLE #forDA2 (
    pk       int    NOT NULL PRIMARY KEY,
    n       int    NOT NULL,
    prod    varchar(10) NOT NULL
);


/* same box may be taken more than once */
DECLARE @c int = 0;

WHILE @c <= @pkparam
BEGIN
    INSERT INTO #forDA (pk, n, prod)
	select @c, @pkparam, @strparam;

    INSERT INTO #forDA2 (pk, n, prod)
	select @c, @pkparam * 2, @strparam+'bla';


	select @c=@c+1
end;

select @pkparam as original, 
	x.pk, x.prod,
	y.pk, y.prod
from #forda x
left join #forda2 y on x.pk=y.pk

END
GO

so i don’t see why the other does not work, it’s more complex but i see no reason why… unless DA is processing the stored proc to retrieve the field names?

can i send a private email with the stored proc in question, i guess that would shed a light?

12

Hi,

it generates EXEC sp_name sp_params statements like

EXEC testDA :PKParam, :StrParam

and opens it.
you can try the same with your “broken” SP in Microsoft SQL Server Management Studio

13

all works fine in sql server, and even in the schema when i want to preview the data but pulling it in it won’t create the fields…

recreate the fields neither

14

even if i explicit cast every field to the right type in the final select statement in the stored proc, DA can’t obtain the fields…

15

ok i found the culprit:

the stored proc aborted if the parameters could not produce a result, instead i set an output param ‘found’

so i eliminated the returns and made sure it always returns the table, even if empty and now DA correctly shows the fields