Link to home
Start Free TrialLog in
Avatar of Olukayode Oluwole
Olukayode OluwoleFlag 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

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

Olukay
Avatar of ste5an
ste5an
Flag of Germany image

The only two ways:

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

The last is not advisable.
Avatar of 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
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.
ASKER CERTIFIED SOLUTION
Avatar of Pavel Celba
Pavel Celba
Flag of Czechia image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Thanks noted that i can not pass table names this way