Today I wrote a script to generate the SQL for a stored procedure based on an Access database table design. Creating a stored procedure is a tedious chore that usually requires a lot of copy and paste of the field names and careful selection of the correct data type. When you have a table with a lot of fields you’ll want to automate the task.
Fortunately there is a way to query the database schema of an Access database which allows you to get the field names, data types, and field lengths. My script simply translates this into the parameters required for my stored procedure. My script also creates the SQL statement that the stored procedure will execute.
I also wrote a script to generate the Active Server Pages code I’ll need to use my new SQL Server stored procedure. Every parameter needs to be set in your ASP code so that is a chore as well.
Writing code to generate code has many advantages:
- It saves time and makes you more productive
- It reduces the chances of making coding errors due to misspelled field names or incorrect data types.
- It standardizes your code
- You understand the code that is generated. Not always true when you use a wizard.
- You can tweak the generated code for a particular project
There may be an easier way to generate stored procedures using a commercial product or some wizard in Visual Studio. I always keep my eye out for shortcuts and smarter ways of doing tedious tasks.
