AutoProc - The Stored Procedure Generator is a set of stored procedures which generates getter and setter procedures for a given SQL Server 2000/2005/2008 table.
How to use
We'll be generating the stored procedures as text, so, in Management Studio, go to Tools->Options->Query Results->Sql Server->Results To Text and set Max number of characters in each column to 8192. And when you execute the get_sp.sql or set_sp.sql, press Ctrl+T to get the text. The stored procedure will be created in your database, which you we'll be using. For the stored procedures that get_sp and set_sp generates, you'll at the most need to change the operators (eg. LIKE clauses etc.).
get_sp will generate a getter stored procedure. You may or may not provide a parameter apart from the tablename to it. In the absence of a columnname parameter, it'll generate a stored procedure to return all rows, otherwise it'll return the rows that matches (WHERE clause) the given columnname parameter.
set_sp will generate a setter (doing inserts and updates) stored procedure for a given tablename. set_sp automatically detects your primary key, even composite ones and does insert or update based on them. The arguments for the generated procedure are optional, unless they are primary key(s).
The stored procedures will be automatically executed on your database, and you can modify them later.
Steps to use :
1. Execute the downloaded procedures on your database.
2. Use the following syntaxes -
get_sp 'tablename', 'columnname'
get_sp 'Authors' -- Generates GetAuthors stored procedure
get_sp 'Authors', 'AuthorId' -- Generates GetAuthorsByAuthorId stored procedure
set_sp 'Authors' -- Generates SetAuthors stored procedure
Try them out and shoot your questions/comments/wishes...?