How to Generate Crud Stored Procedures from a Table in SQL Server Management Studio

How do I generate CRUD stored procedures from a table in SQL Server Management Studio

SSMS doesn't have the capability to generate CRUD procedures. You can generate INSERT, UPDATE statements etc. by right-clicking, Script Table As > but I think you will have better luck with Mladen Prajdic's SSMS Tools Pack.

Generating simple CRUD stored procs

SSMS Tools Pack from Mladen Prajdić sounds like it might fit the bill. You can customise the templates it uses.

It's well worth trying anyway for its other handy features.

SSMS / SQLCMD batch to create all stored procedures within a sub-folder

yes there is a way ,
SQLCMD is not able to loop through files but you can use Windows Batch Scripting to accomplish that.

here is a good blog about how to do it :
SQLCMD and Batch File magic

so basically you need to make a .bat file ex: CRUDE.bat and edit and paste below code in it and save it ,

@@echo off
cd "[MY_PARENT_PATH]\Stored Procedures"

FOR %%A IN (*.SQL) DO ( sqlcmd -S [SERVERNAME] -d [DATABASE] -U [][username -P password] -i "%%A")

you need to replace these values with your values :

MY_PARENT_PATH : Your path or Directory where your suborder is.

SERVERNAME : Your Database Server Name.

DATABASE1 : Your Database name.

Username : Your SQL Username.

Password : Your SQL Password.

now you can run the batch file and it does the magic.

also here you can find out more about SQLCMD utility

How do you create SQL Server 2005 stored procedure templates in SQL Server 2005 Management Studio?

Another little nugget that I think will help people developing and being more productive in their database development. I am a fan of stored procedures and functions when I develop software solutions. I like my actual CRUD methods to be implemented at the database level. It allows me to balance out my work between the application software (business logic and data access) and the database itself. Not wanting to start a religious war, but I want to allow people to develop stored procedures more quickly and with best practices through templates.

Let’s start with making your own templates in the SQL Server 2005 management Studio. First, you need to show the Template Explorer in the Studio.

alt text http://www.cloudsocket.com/images/image-thumb10.png

This will show the following:

alt text http://www.cloudsocket.com/images/image-thumb11.png

alt text http://www.cloudsocket.com/images/image-thumb12.png

alt text http://www.cloudsocket.com/images/image-thumb13.png

The IDE will create a blank template. To edit the template, right click on the template and select Edit. You will get a blank Query window in the IDE. You can now insert your template implementation. I have here the template of the new stored procedure to include a TRY CATCH. I like to include error handling in my stored procedures. With the new TRY CATCH addition to TSQL in SQL Server 2005, we should try to use this powerful exception handling mechanism through our code including database code. Save the template and you are all ready to use your new template for stored procedure creation.

-- ======================================================
-- Create basic stored procedure template with TRY CATCH
-- ======================================================

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:        <Author,,Name>
-- Create date: <Create Date,,>
-- Description:    <Description,,>
-- =============================================
CREATE PROCEDURE <Procedure_Name, sysname, ProcedureName>
    -- Add the parameters for the stored procedure here
    <@Param1, sysname, @p1> <Datatype_For_Param1, , int> = <Default_Value_For_Param1, , 0>,
    <@Param2, sysname, @p2> <Datatype_For_Param2, , int> = <Default_Value_For_Param2, , 0>
AS
    BEGIN TRY
        BEGIN TRANSACTION    -- Start the transaction

        SELECT @p1, @p2

        -- If we reach here, success!
        COMMIT
    END TRY
    BEGIN CATCH
        -- there was an error
        IF @@TRANCOUNT > 0
        ROLLBACK

        -- Raise an error with the details of the exception
        DECLARE @ErrMsg nvarchar(4000), @ErrSeverity int
        SELECT @ErrMsg = ERROR_MESSAGE(), @ErrSeverity = ERROR_SEVERITY()

        RAISERROR(@ErrMsg, @ErrSeverity, 1)
    END CATCH
GO

Are there any advantages in using a single stored procedure for multiple operations or is my teacher wrong?

Are there any advantages? Probably - opening Management Studio could be quicker.

Is your teacher wrong? Yes, most certainly, but as SMor and Jeroen write in the comments: Your teacher does the grading, so he's right until you graduate your class.

Take a look at this question: Generic Stored Procedure for ALL the tables

If you could write a single stored procedure for each table with Create, Read, Update and Delete, then why not build a single generic stored procedure with CRUD operations for ALL the tables? One database - One stored procedure.

Now, apply the logic you would use to determine that this is a bad idea to your problem.

Or apply SOLID principles - they apply to stored procedures as well. You wouldn't write a single method to do 4 wildly different operations based on an input parameter, would you?

Or have a look here: If logic in stored proc.

On first execution SQL Server will explore all the branches, and build a plan - but it will use the parameters seen at that first execution. So assume your first execution is an insert with @IdCiudad = null, then that's what SQL Server will optimize for in the option 4 branch.

Your initial insert will build a select plan (for option 4) where all rows are expected to be returned, meaning a too large memory grant and maybe a parallel plan with hash joins to boot.

Oh, and

WHERE Foo = @foo OR @foo = 0 OR @foo IS NULL

SQL Server won't know how to optimize for this. See Aaron Bertrands article here Kitchen sink design pattern.

Generate class from database table

Set @TableName to the name of your table.

declare @TableName sysname = 'TableName'
declare @Result varchar(max) = 'public class ' + @TableName + '
{'

select @Result = @Result + '
    public ' + ColumnType + NullableSign + ' ' + ColumnName + ' { get; set; }
'
from
(
    select 
        replace(col.name, ' ', '_') ColumnName,
        column_id ColumnId,
        case typ.name 
            when 'bigint' then 'long'
            when 'binary' then 'byte[]'
            when 'bit' then 'bool'
            when 'char' then 'string'
            when 'date' then 'DateTime'
            when 'datetime' then 'DateTime'
            when 'datetime2' then 'DateTime'
            when 'datetimeoffset' then 'DateTimeOffset'
            when 'decimal' then 'decimal'
            when 'float' then 'double'
            when 'image' then 'byte[]'
            when 'int' then 'int'
            when 'money' then 'decimal'
            when 'nchar' then 'string'
            when 'ntext' then 'string'
            when 'numeric' then 'decimal'
            when 'nvarchar' then 'string'
            when 'real' then 'float'
            when 'smalldatetime' then 'DateTime'
            when 'smallint' then 'short'
            when 'smallmoney' then 'decimal'
            when 'text' then 'string'
            when 'time' then 'TimeSpan'
            when 'timestamp' then 'long'
            when 'tinyint' then 'byte'
            when 'uniqueidentifier' then 'Guid'
            when 'varbinary' then 'byte[]'
            when 'varchar' then 'string'
            else 'UNKNOWN_' + typ.name
        end ColumnType,
        case 
            when col.is_nullable = 1 and typ.name in ('bigint', 'bit', 'date', 'datetime', 'datetime2', 'datetimeoffset', 'decimal', 'float', 'int', 'money', 'numeric', 'real', 'smalldatetime', 'smallint', 'smallmoney', 'time', 'tinyint', 'uniqueidentifier') 
            then '?' 
            else '' 
        end NullableSign
    from sys.columns col
        join sys.types typ on
            col.system_type_id = typ.system_type_id AND col.user_type_id = typ.user_type_id
    where object_id = object_id(@TableName)
) t
order by ColumnId

set @Result = @Result  + '
}'

print @Result

---

Related Topics