CF/Stored Procedure Question

[programmher]

I am re-creating a pre-existing stored procedure for a web application.

The stored procedure uses an embedded Select statement instead of values within an Insert statement. Ex:

INSERT INTO my_table
(field1, field2, field3, field4)
SELECT field1, field2, field3
FROM another_table
WHERE another_table field1 = #field1#
AND another_table field5 = 0
UPDATE another_table
SET field5 = 'something', field5 = 1
WHERE another_table.field1 = #field1" AND another_table.field5 = 0

Can I duplicate this code within CF EXACTLY the way it is in the stored procedure or will I have to use the standard syntax - supplying VALUES with the INSERT query, then referencing the INSERT query's values as variables in the second SELECT query?

 

[GunJack]

Hey programmher,

There is nothing in CF that will prevent you from using this syntax. The only problem may be in the database or your odbc driver. If the odbc driver doesn't support the syntax, it won't successfully run. Also, if you're taking a stored proc from Sql and want to run it against MS Access, you will likely find certain sql syntax that Sql server will run but that Access can't. In either of these cases, you will have to re-structure the code.

One example that I always have to deal with is the fact that Access won't allow "from table1,table2 where table1.f1 = table2.f1". I always have to write out the "left join on" syntax for joins in MS Access.

Hope this helps,
GJ

 

[programmher]

Thanks for the information, GJ!

I will proceed with my coding.
Fortunately, I am working with a SQL database, not Access. (I rather cringe at the thought of using Access for anything more than basic database warehousing. I prefer SQL for anything that involves a web front-end...)