Create Stored Procedures automatically for Database table

Create Stored Procedures automatically for Database table
7 votes, 5.00 avg. rating (98% score)

Introduction

In this article we will see how to generate basic stored procedures (select, insert, delete, update) for a table in Sql Server automatically using script. This will certainly speed up your development.

Creating Stored Procedures automatically

First download the script file and execute it.
Download Automated Stored Procedure

It will create 4 store procedures

  • pr__SYS_MakeDeleteRecordProc
  • pr__SYS_MakeInsertRecordProc
  • pr__SYS_MakeSelectRecordProc
  • pr__SYS_MakeUpdateRecordProc
  • StoredProcedures

    StoredProcedures


    Create Delete Stored Procedure for table

    exec pr__SYS_MakeDeleteRecordProc "ST_User", 0

    “ST_User” is the Table name, 0 is the bit

    Delete Stored Procedure

    Delete Stored Procedure

    If ‘0’ is the input, this will create the syntax in the output window. You need to copy that and execute separately. This will help to modify if required.

    exec pr__SYS_MakeDeleteRecordProc "ST_User", 1
    If ‘1’ is the input, this will create the syntax in the output window and also executes the procedure for you.

    Delete Stored Procedure 1

    Delete Stored Procedure 1

    To change the syntax or rename or to add any modifications, use ‘0’ and get the script to modify. If no changes are required, use ‘1’

    Creat Insert, Select, Update stored procedures for table

    exec pr__SYS_MakeInsertRecordProc "ST_User" , 0
    exec pr__SYS_MakeSelectRecordProc "ST_User" , 0
    exec pr__SYS_MakeUpdateRecordProc "ST_User" , 0

    Use ‘1’ instead of ‘0’ to automatically create stored procedure.

    Conclusion

    The basic idea is to reduce the development time. In fact this forms a core for any automation tool you want to develop. Happy coding and hope this helps.

    External References

    February 20, 2014 В· Adi В· No Comments
    Tags: , ,  В· Posted in: Automation, SQL

    Leave a Reply

    What is 7 + 10 ?
    Please leave these two fields as-is:
    IMPORTANT! To be able to proceed, you need to solve the following simple math (so we know that you are a human) :-)