Category: SQL-Stored Procedure

SQL Server – Stored Procedure with Output Parameters

SQL, SQL-Advanced, SQL-Stored Procedure, SQL-Tips and Tricks, Varinder Sandhu

In SQL Server, there are two ways with we can pass parameters to procedures. INPut parameter OUTPut parameter INPut parameter These types of parameters are used to send values to stored procedures. OUTPut parameter These types of parameters are used to get values from stored procedures. This is similar to a return type in functions. In this post we will try to understand how stored procedure returns the parameter Example — Create a table CREATE TABLE myTable ( ID INT

» Read more
,

SQL Server – Stored Procedure – WITH RECOMPILE

SQL, SQL-Advanced, SQL-Scripts, SQL-Stored Procedure, SQL-Tips and Tricks, Varinder Sandhu

Stored Procedure has ability to execute faster than the same T-SQL statements executed individually because SQL Server recorded the query execution plan for Stored Procedure. In case you need to execute the stored procedure with different execution plan then use WITH RECOMPILE options Different ways to use the WITH RECOMPILE options You can create the stored procedure using WITH RECOMPILE option. This way every time SQL Server uses the recompile the execution plan. CREATE PROCEDURE sp_orders ( @StartDate Datetime, @EndDate

» Read more
, ,

SQL Server – Table-Valued Parameters

SQL, SQL-Advanced, SQL-General, SQL-Query, SQL-Scripts, SQL-Stored Procedure, SQL-Tips and Tricks, Varinder Sandhu

Table-valued parameters are a new parameter type introduced in SQL Server 2008. With the help of table-valued parameters, you can send multiple rows of data to a stored procedure or function, without creating a temporary table or many parameters. Steps to create Table-Valued Parameters Create a table type and define the table structure. Declare a stored procedure or function that has a parameter of the table type. Declare a variable of the table type, and reference the table type. Fill

» Read more
, , ,

SQL Server – User Defined Functions vs. Stored Procedures

SQL, SQL-Functions(UDF), SQL-General, SQL-Stored Procedure, SQL-Tips and Tricks, Varinder Sandhu

SQL Server user-defined functions and stored procedures have almost similar functionality. Both allow you to create a batch of SQL statements to execute on SQL server. Benefits: We can reuse the code from one program to another, this way we can increase the productivity. Centralize control, to make business logic changes in a single place that automatically affect all dependent applications. Difference between User Defined Functions and Stored Procedures   Stored procedures are called independently, using the EXEC command. Example: EXEC dbo.procedure_name ‘Parameter1’Functions are

» Read more
, , , , ,