how to generate barcode in c# Best PraCtiCes in C#.NET

Print Quick Response Code in C#.NET Best PraCtiCes

If the stored procedure being executed is the first line in the batch, the EXEC keyword is optional. However, the EXEC keyword is required for a stored procedure call anywhere else in the batch. Even if the only code before the stored procedure is a comment, the EXEC keyword is still required. To avoid confusion and ensure that your code always runs, regardless of the structure of the batch, you should always include the EXEC keyword.

To use an output parameter, you need to specify the OUT or OUTPUT keyword following each output parameter:

If you need to capture the return code from a stored procedure, you must store it in a variable, as follows:

While dynamic command execution is very rare within stored procedures that applications use, many administrative procedures need to construct commands and dynamically execute them. T-SQL has two ways to execute dynamically constructed statements: EXEC(<command>) and sp_executesql <command>. The following shows how to use each method.

VARCHAR(MAX)

SET @var = 'SELECT OrderID, CustomerID FROM Sales.SalesOrderHeader WHERE OrderID = 1' EXEC(@var) GO

NVARCHAR(MAX)

SET @var = 'SELECT OrderID, CustomerID FROM Sales.SalesOrderHeader WHERE OrderID = 1' EXEC sp_executesql @var GO

Any time you are building a string for dynamic execution, you have the potential of an SQL injection attack. You should always validate any parameters passed to the stored procedure that will be used for a dynamically created command. You should also use the sp_executesql system stored procedure with parameter substitution to avoid many of the SQL injection problems that could be created. SQL injection is beyond the scope of this book, but you should read the many articles published on SQL injection and understand the risks before writing code that takes advantage of dynamic execution.

Programming Microsoft SQL Server with T-SQL User-Defined Stored Procedures, Functions, Triggers, and Views

Functions and stored procedures allow you to modify the security context under which the object is running by using the EXECUTE AS option. EXECUTE AS has three possible arguments:

Executes under the security context of the specified database user. This account can t be a role, group, certificate, or asymmetric key.

The EXECUTE AS clause also has two additional arguments: NO REVERT and COOKIE INTO. The NO REVERT option specifies that once the security context is changed, it can t be changed back. The COOKIE INTO option sets a cookie that allows the security context to be returned to a specific, previous security context.

SQL Server is built to process sets of data. However, there are times when you need to process data one row at a time. The result of a SELECT statement is returned to a server-side object called a cursor, which allows you to access one row at a time within the result set and even allows scrolling forward as well as backward through the result set.

SQL Server is built and optimized for set-based operations. A cursor causes the engine to perform row-based processing. A cursor never performs as well as an equivalent set-based process.

Cursors have five components. DECLARE is used to define the SELECT statement that is the basis for the rows in the cursor. OPEN causes the SELECT statement to be executed and load the rows into a memory structure. FETCH is used to retrieve one row at a time from the cursor. CLOSE is used to close the processing on the cursor. DEALLOCATE is used to remove the cursor and release the memory structures containing the cursor result set.

If a cursor is used within a stored procedure, it is not necessary to close and deallocate the cursor. When the stored procedure exits, SQL Server automatically closes and deallocates any cursors created within the procedure to reclaim memory space.