CODEWITHSUNDEEP
c#sql-serverstored-procedures
Thomas
Thomas

Reputation: 34188

SQL Server stored procedure and C#

I heard that stored procedure can be written with the help of C# but I want to know that is there any added advantage of writing stored procedure using C#. When one should write stored procedure using c#. please discuss. thanks

Upvotes: 1

Views: 896

Answers (2)

marc_s
marc_s

Reputation: 754488

This article here sums it up quite nicely:

Choosing between CLR and T-SQL stored procedures: a simple benchmark

Basically, when doing set-oriented operations, stick to T-SQL.

When doing more compute-intensive operations (like string and date manipulations, external calls etc.), use C#.

I see the CLR stored procedures mostly as a replacement for the "extended stored procedures" in SQL Server 7 / 2000 - not as a potential replacement for straight T-SQL stored procs.

Upvotes: 4

Pranay Rana
Pranay Rana

Reputation: 176896

Check more about this : http://searchsqlserver.techtarget.com/tutorial/CLR-stored-procedures

With the CLR hosted in Microsoft SQL Server (called CLR integration), you can author stored procedures, triggers, user-defined functions, user-defined types, and user-defined aggregates in managed code. Because managed code compiles to native code prior to execution, you can achieve significant performance increases in some scenarios.

Advantage of CLR Integration

Transact-SQL is specifically designed for direct data access and manipulation in the database. While Transact-SQL excels at data access and management, it is not a full-fledged programming language. For example, Transact-SQL does not support arrays, collections, for-each loops, bit shifting, or classes. While some of these constructs can be simulated in Transact-SQL, managed code has integrated support for these constructs. Depending on the scenario, these features can provide a compelling reason to implement certain database functionality in managed code. Microsoft Visual Basic .NET and Microsoft Visual C# offer object-oriented capabilities such as encapsulation, inheritance, and polymorphism. Related code can now be easily organized into classes and namespaces. When you are working with large amounts of server code, this allows you to more easily organize and maintain your code. Managed code is better suited than Transact-SQL for calculations and complicated execution logic, and features extensive support for many complex tasks, including string handling and regular expressions. With the functionality found in the .NET Framework Library, you have access to thousands of pre-built classes and routines. These can be easily accessed from any stored procedure, trigger or user defined function. The Base Class Library (BCL) includes classes that provide functionality for string manipulation, advanced math operations, file access, cryptography, and more.

Upvotes: 3

Related Questions