Avatar of Olukayode Oluwole
Olukayode Oluwole
Flag for Canada asked on

How do I introduce table name into my stored procedure as a parameter

I have a c# application running against a postgresql database.

I want to use a delete statement in my stored procedure where
the table name i am deleting from is sent in as parameter This will allow me use the
same script for different tables.

To test the concept,  I did a stored procedure with the table name 'locations ' explicitly stated
(see procedure below ) and it worked
1. The script that worked stating table name  as 'locations'

[]CREATE OR REPLACE FUNCTION public.spalltables_delete(
IN companycodex text,
IN tablex  text,
IN idx integer,
IN deletetypex text)
RETURNS void AS
$BODY$
BEGIN
UPDATE locations
SET
validrecordyn = 'N'
where  id = idx and companycode = companycodex and  'DM' = deletetypex;
IF NOT FOUND THEN
delete from locations where  id = idx and companycode = companycodex and 'DP' = deletetypex;
END IF;
END;
$BODY$
  LANGUAGE plpgsql VOLATILE
  COST 100;
ALTER FUNCTION public.spalltables_delete(text,text,integer,text)
  OWNER TO postgres;[/code]

-------------------------------
When I now brought in the table name  'locations 'with a parameter tablex (see below)
i got the error attached

2. The script with table name as a  parameter

[][CREATE OR REPLACE FUNCTION public.spalltables_delete(
IN companycodex text,
IN tablex  text,
IN idx integer,
IN deletetypex text)
RETURNS void AS
$BODY$
BEGIN
UPDATE tablex
SET
validrecordyn = 'N'
where  id = idx and companycode = companycodex and  'DM' = deletetypex;
IF NOT FOUND THEN
delete from tablex where  id = idx and companycode = companycodex and 'DP' = deletetypex;
END IF;
END;
$BODY$
  LANGUAGE plpgsql VOLATILE
  COST 100;
ALTER FUNCTION public.spalltables_delete(text,text,integer,text)
  OWNER TO postgres;[/code]
 
The script above compiles properly as a stored procedure but give the error below during execution.

3. Error Page


How can i bring in table name as parameter  and get it to work in my stored procedure

Olukay
Databases* stored prcedure* Postgresdatabase

Avatar of undefined
Last Comment
Olukayode Oluwole

8/22/2022 - Mon
ste5an

The only two ways:

1) Use an IF statement to switch between the possible tables.
2) Use dynamic SQL.

The last is not advisable.
Olukayode Oluwole

ASKER
The problem is not switching between 2 Tables.

The issue is that the stored procedure would not accept table name as a

parameter passed in from the  application

Olukay
ste5an

SQL as a declarative language is not made for what you want to do.

The correct place to select or parameterize the table name is your code in .NET.
This is the best money I have ever spent. I cannot not tell you how many times these folks have saved my bacon. I learn so much from the contributors.
rwheeler23
ASKER CERTIFIED SOLUTION
Pavel Celba

THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
See Pricing Options
Start Free Trial
GET A PERSONALIZED SOLUTION
Ask your own question & get feedback from real experts
Find out why thousands trust the EE community with their toughest problems.
Olukayode Oluwole

ASKER
Thanks noted that i can not pass table names this way