Olukayode Oluwole
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
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
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
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.
The correct place to select or parameterize the table name is your code in .NET.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Thanks noted that i can not pass table names this way
1) Use an IF statement to switch between the possible tables.
2) Use dynamic SQL.
The last is not advisable.