I would like to create three stored procedures (create, update, delete) that would perform the task for any of the lookup tables.
For example, table AGENCY has three fields (Agency_id (identity field), Agency_Code, Agency_Desc) and I need to add a new record to AGENCY. I would like to call a stored procedure that would take the parameters @.tablename, @.code, @.desc, and then create a new record in the table specified by @.tablename.
I could then use a single "create" stored procedure for all of the lookup tables when adding a new record, rather than creating a separate "create" stored proc for each of my lookup tables.
Any help would be greatly appreciated!::I would like to create three stored procedures (create, update, delete) that would perform
::the task for any of the lookup tables.
Forget it. Not a feasible way.|||Thanks for your input!
I'm currently writing separate stored procs for each of my lookup tables and will continue to do so until somebody shows me a better way.|||Skip the SP's, go with dynamic SQL and use an intelligent DAL to never maintain the SQL anymore.|||I've written generic stored procedures in the past. The trick is to use the EVAL function of SQL. As an example, here is a stored procedure that would take a table name as parameter and return all records from this table.
The stored procedure is defined as follow:
CREATE PROCEDURE SP_Query
(
@.table nvarchar(100)
)
AS
EXEC('SELECT * FROM ' + @.table + ' ORDER BY ID')
GO
You must call it as follow:
SP_Query 'Customers'
As your tables have the same columns it should be easy to built your 3 generic stored procedures in the same way as my example.
Good luck,
Olivier Giulieri
www.kakoo.net|||You can use a mixture of dynamic as static.
Have a utility the writes stored proces and either map them or construct their name.
So...
Template:
Update<table>
Gives:
UpdateMyTable1
UpdateMyTable2
...etc
Then the dynamic part is constructing the name of the stored proc...
CommandText = "Update" + strTableName
PS Don't prefix stored procs with "SP_" it's bad news.|||Hm,
you guys do know the disadvantage of using the EVAL function in SQL?
And why do you prefix your stored procedures with "sp_", which is "system procedure" and has special treatment in SQL Server - and none you will like.
I suggest a look into the documentation. Personally I am always reluctant to take advice from people who have not read the basics in the documentation (such as NOT naming stored procedures with 'sp_').|||I was just making a quick example of stored procedure to illustrate the use of "EVAL". For sure, I picked a very bad name. Here is my example again:
CREATE PROCEDURE GenericQuery
(
@.table nvarchar(100)
)
AS
EXEC('SELECT * FROM ' + @.table + ' ORDER BY ID')
GO
PS: Am I accused of not reading documentations because you said "Forget it. Not a feasible way." and my solution may work? Please let's just try to be constructive here.|||*If* you do need to use an "EVAL" function then consider using sp_executeSQL instead. It's geared up for param' queries.|||<Hm,
you guys do know the disadvantage of using the EVAL function in SQL?
And why do you prefix your stored procedures with "sp_", which is "system procedure" and has special treatment in SQL Server - and none you will like.
I suggest a look into the documentation. Personally I am always reluctant to take advice from people who have not read the basics in the documentation (such as NOT naming stored procedures with 'sp_').
Easy now big fella! ;)
Check every website that has examples, I will bet 99% use sp_ . For the record I do not, but if you take samples from the net, or even Microsoft you will see they almost always use sp_ so you cannot get too uptight at people for following the convention, even if it does reduce processing efficiency.|||There is no 'good' way to do this.
If you use a new proc for each it's a lot of writing and a lot of changing if it changes. If you use dynamic stored procs you lose the security of refusing anyone direct access to your tables (dynamic stored procs require the use to have table access) and they run much more slowly.
My work around when this happens,
Write one proc per table.
Write one proc which the application calls, it then calls the correct proc. That way your application programmers only need to remember one stored procedure to call and you do the rest.
Avoid using IF statements in an SQL proc.
If you have something like this:
IF X
Begin
{code code code}
End
Else
Begin
{code code code}
End
When you execute only half of the proc will be in the plan and run efficiently. The other half will be adhoc.
Consider this:
If X
Begin
exec prCode1
End
Else
Begin
exec prCode2
End
Since each If calls a procedure that has an execution plan it runs more efficiently (AKA faster).
</code>
