Using stored procedure variables in a SAMPLE clause

Database
N/A

Using stored procedure variables in a SAMPLE clause

This doesn't compile:

replace procedure A(K integer)
BEGIN
INSERT INTO C
SELECT YV.*
FROM YV, (SELECT DISTINCT i FROM YV sample K) AS Sam
WHERE YV.i=Sam.i;
END;

SPL1027:E(L5), Missing/Invalid SQL statement'E(3706):Syntax error: Expecting the word RANDOMIZED.'.
SPL1027:E(L6), Missing/Invalid SQL statement'E(3707):Syntax error, expected something like a name or a Unicode delimited identifier between the word 'K' and ')'.'.

But this does compile:

replace procedure A(K integer)
BEGIN
INSERT INTO C
SELECT YV.*
FROM YV, (SELECT DISTINCT i FROM YV sample 5) AS Sam
WHERE YV.i=Sam.i;
END;

Seems the "K" in the first form is not recognized as a stored procedure parameter.

In general, the value of K will not be 5! Can someone explain why the first example fails, and what I can do instead?
5 REPLIES
Senior Apprentice

Re: Using stored procedure variables in a SAMPLE clause

You can't pass the sample size as a parameter.
The only way is to construct a string and submit the SQL using dbc.sysexecsql.

Dieter
Enthusiast

Re: Using stored procedure variables in a SAMPLE clause

And what if i want to insert K inside a table either i will use :K or simply K ?

Re: Using stored procedure variables in a SAMPLE clause

I have a similar requirement as mbeddo.

Hi Dieter, Can you please explain your answer using an example. 

Senior Apprentice

Re: Using stored procedure variables in a SAMPLE clause

Re: Using stored procedure variables in a SAMPLE clause

Thank you.