A tough nut to crack

I have a class C# which dynamically generates the SQL to create a stored procedure, one of the parameters i write into to the SQL is @.Keyname, and i use it in the following way

WHERE @.KeyName = CONVERT(VARCHAR, @.KeyValue)

However when i run the generated SQL to actually create the stored proc is is created with the line exactly as it is above. I need to be able to take the keyname as specified when the proc is called, which is a quoted string (i.e 'keynamefield' and write it into the SQL as keynamefield without quotes to make the field lookup dynamic based on this keyname field.

I am presuming that i will need to use function against the keyname variable inside the stored procedure (as this cannot be called from C#) something like GetValue(@.KeyName)

??

Any Ideas would be greatly appreciated

You need to use dynamic SQL but that is not what you want to do. You should not write applications that passes column names and table names dynamically for manipulation. There are lot of security risks, performance issues among other things. Best is to create the SP in such a manner that you don't need dynamic SQL. There are many ways to do this. The link below discusses the techniques to do something like this:

http://www.sommarskog.se/dyn-search.html

|||

As Umachandar said, its highly risk to create a object from your code. Your database is widly open to any one. Security Issues..

Comming to your issue, you have to use NVARCHAR instead of VARCHAR for unicode characters(non-english alphabets).

|||

Got it sussed thanks, the procs use the sql in a pre-generated fashion (i.e the compiler calculates the select and then it is added to a stored proc and which sql is executed is controlled by paremeters.) The procs are also encrypted so no one can execute any code they fancy on the database. I wonder though, can you encrypt tables the same way toy can procedures (i.e. WITH ENCRYPTION)?

Thanks

|||No. You can't encrypt the table definitions.

A new PL/SQL nut...

I'm using Oracle, BTW...

Now...: This one is working, thanks to andrewst.

create or replace procedure upd_balance (p_pnr number,p_amount number)
is
begin
update player
set balance=balance+p_amount
where pnr=p_pnr;
end upd_balance;
/

But, I'd like this procedure to do one more thing for me:

insert into trans values(v_next,p_amount,[todays date],p_ttype,p_pnr,'Y');

...given that the "p_ttype" attribute is sent with the exec command, and
..."v_next" is "select max(tnr)+1 from trans;", and
...[todays date] is SYSDATE

Here's the output of "desc trans;"
Name Null? Type
------------- --- ----
TNR NOT NULL NUMBER(5)
AMOUNT NUMBER(7,2)
TDATE DATE
TTNR NUMBER(2)
PNR NUMBER(2)
OK CHAR(1)

CAN SOMEONE MAKE THIS HAPPEN? I'd be ever so grateful.

BR, hermasito.Hello hermasito,

what do you think about

create or replace procedure upd_balance (p_pnr number,p_amount number, p_next number, p_ttype number, p_today DATE)
is
v_next NUMBER := NULL;
v_ttype NUMBER := NULL;
begin

update player
set balance=balance+p_amount
where pnr=p_pnr;

select max(tnr) + 1 INTO v_next from trans;",

IF v_next = p_next AND TO_CHAR(p_today, 'YYYYMMDD') = TO_CHAR(SYSDATE, 'YYYYMMDD') THEN
v_ttype := p_ttype;
END IF;

insert into trans values(p_next, p_amount, SYSDATE, v_ttype, p_pnt, 'Y');

end upd_balance;

Hope that helps a little bit ?

Best regards
Manfred
(Alligator Company GmbH)
http://www.alligatorsql.com|||Well, the insert statement is simple enough - but you MUST specify the columns like this:

INSERT INTO trans (tnr, amount, tdate, ttnr, pnr, ok)
VALUES( v_next, p_amount, SYSDATE, p_ttype, p_pnr, 'Y' );

... otherwise your code may fail some time in the future (e.g. if a new column is added to table TRANS).

(Note: SYSDATE includes time. If you want JUST the date use TRUNC(SYSDATE))

Now, how to get the v_next value. You should be aware that this MAX(trn)+1 solution is not a good idea at all unless your application is single user, because it locks out other users from inserting any data into TRANS until the first user commits. In a multi-user system, all your users will experience potentially long waits to get the next tnr value for their inserts. The correct solution is to use a SEQUENCE:

CREATE SEQUENCE tnr_seq;

Then in your trigger:

INSERT INTO trans (tnr, amount, tdate, ttnr, pnr, ok)
VALUES( tnr_seq.NEXTVAL, p_amount, SYSDATE, p_ttype, p_pnr, 'Y' );

You will have to accept when you use sequences that there WILL be gaps in the numbers, they CANNOT be guaranteed to be sequential. You have to accept that this doesn't matter (it really doesn't matter -an ID is just an ID - but when people are used to gap-free numbers they THINK it matters!) It is this "no gap-free guarantee" feature that makes sequences work efficiently for multi-user systems.

If after reading all the above you still want to use MAX(tnr)+1, then all you have to do is:

SELECT MAX(trn)+1
INTO v_next
FROM trans;

INSERT INTO trans( tnr,...) VALUES (v_next,...);

But don't say I didn't warn you!