Dinamic SQL & Cursor

1. Dinamic SQL&Cursor

2. Why Dinamic SQL ? • Sometimes there is a need to dynamically create a SQL statement on the fly and then run that command. • This can be done quite simply from the application perspective where the statement is built on the fly whether you are using ASP.NET or any other programming language.  • But how do you do this from within a SQL Server stored procedure?

3. Dinamic SQL • Dynamic SQL is a term used to mean SQL code that is generated programatically (in part or fully) by your program before it is executed. • As a result it is a very flexable and powerful tool. • You can use dynamic SQL to accomplish tasks such as adding where clauses to a search based on what fields are filled out on a form or to create tables with varying names.

4. How To Run Dinamic SQL SQL Server offers a few ways of running a dynamically built SQL statement.  These ways are: • Writing a query with parameters • Using EXEC • Using sp_executesql

5. Parameters - EXEC – SP_EXECUTESQL DECLARE @city varchar(75) SET @city ='London' SELECT*FROM customers WHERE City = @city DECLARE @sqlCommand varchar(1000) DECLARE @columnList varchar(75) DECLARE @city varchar(75) SET @columnList ='CustomerID, ContactName, City' SET @city ='''London''' SET @sqlCommand ='SELECT '+ @columnList +' FROM customers WHERE City = '+ @city EXEC(@sqlCommand) DECLARE @sqlCommand nvarchar(1000) DECLARE @columnList varchar(75) DECLARE @city varchar(75) SET @columnList ='CustomerID, ContactName, City' SET @city ='London' SET @sqlCommand ='SELECT '+ @columnList +' FROM customers WHERE City = @city' EXECUTEsp_executesql @sqlCommand, N'@city nvarchar(75)', @city = @city

6. Dinamic SQL in a Stored Procedure Create Procedure GenericTableSelect @TableName VarChar(100) AS SELECT * FROM @TableName Create Procedure GenericTableSelect @TableName VarChar(100) AS Declare @SQL VarChar(1000) SET @SQL = 'SELECT * FROM ' SET @SQL = @SQL + @TableName EXEC (@SQL)

7. SP_EXECUTESQL The system stored procedure sp_executesql behaves very similar to the EXECUTE command, however, it offers two advantages: Parameters can stay in their native data type - with EXECUTE you will have to pass a string; therefore, everything needs to be converted to string data types with EXEC. Query optimizer is more likely to re-use the existing execution plans if you have to run the same query with different parameters. The reason for this is that the text of the query does not change with sp_executesql - with EXEC you pass a different string each time.

8. SP_EXECUTESQL sp_executesql [@SQLStatement],[@ParameterDefinitionList], [@ParameterValueList] • The parameters are specified with a name and type. • We can specify the parameters for both input and output.

9. Dinamic SQL Samples (1) CREATEPROCEDURE GENERAL_SELECT (@TableName nvarchar(100) ,@ColumnList varchar(75)) AS BEGIN DECLARE @sqlCommand NVARCHAR(200) SET @sqlCommand ='SELECT '+ @columnList +' FROM ‘ + @TableName EXECUTEsp_executesql @sqlCommand END EXEC GENERAL_SELECT @TableName='Customers', @ColumnList='*'

10. Dinamic SQL Samples (2) CREATEPROCEDURE usp_GetNumberOfRecord ( @TableName NVARCHAR(2000), @TotalRowsReturned INTOUTPUT ) AS BEGIN DECLARE @SelectStatement NVARCHAR(2000) DECLARE @FullStatement NVARCHAR(4000) DECLARE @ParameterList NVARCHAR(500) SET @ParameterList ='@TotalRowsReturned INT OUTPUT' SET @SelectStatement ='SELECT @TotalRowsReturned = COUNT(*)+1 FROM ' SET @FullStatement = @SelectStatement + @TableName PRINT @FullStatement EXECUTEsp_executesql @FullStatement, @ParameterList, @TotalRowsReturned = @TotalRowsReturned OUTPUT END

11. Dinamic SQL Samples (3) DECLARE @TableName NVARCHAR(2000), @TotalRowsReturned INT SET @TableName =‘Customers' EXECUTE usp_GetNumberOfRecord @TableName = @TableName, @TotalRowsReturned = @TotalRowsReturned OUTPUT SELECT @TotalRowsReturned as NextRow

12. Cursor • Cursor is a database object used by applications to manipulate data in a set on a row-by-row basis, instead of the typical SQL commands that operate on all the rows in the set at one time. • There are times when you want to loop through a series of rows a perform processing for each row. • The should only be used when you truly need to access one row at a time. The only reason for that is to call a stored procedure on each row • For example, you can use cursor to include a list of all user databases and make multiple operations against eachdatabase by passing each database name as a variable.

13. Cursor Life Cycle A cursor life cycle can be described as follows: • Cursor is declared using the DECLARE CURSOR statement. This statement creates a cursor within SQL Server memory • Cursor is activated using OPEN CURSOR statement. At this point you can populate the cursor with a record set. • Data is retrieved from the cursor using the FETCH keyword. • A WHILE loop is executed within the cursor to perform some operation with the rows in the cursor with the condition that the FETCH command is successful. • Cursor is deactivated using CLOSE CURSOR statement. At this point you can't populate the cursor with additional rows. Nor can you work with rows within the cursor. However, you can re-open the cursor with OPEN CURSOR statement and perform additional work with the cursor. • The cursor is destroyed using DEALLOCATE CURSOR statement. Once the cursor is de-allocated it cannot be reopened.

14. Cursor Syntax DECLARE cursor_name [INSENSITIVE] [SCROLL] CURSOR FOR select_statement [FOR {READ ONLY | UPDATE [OF column_name [,...n]]}] OPEN{{ [GLOBAL] cursor_name }| cursor_variable_name} FETCH [[ NEXT | PRIOR | FIRST | LAST | ABSOLUTE {n | @nvar} | RELATIVE {n | @nvar} ] FROM ] {{ [GLOBAL] cursor_name }|@cursor_variable_name} [INTO @variable_name[,...n] ] CLOSE{{[GLOBAL] cursor_name }| cursor_variable_name } DEALLOCATE{{ [GLOBAL] cursor_name }| @cursor_variable_name}

15. Cursor Samples DECLARE @ProductName nvarchar(40) DECLARE @idx int SET @idx=1 DECLARE @getProductName CURSOR SET @getProductName =CURSORFORSELECT ProductName FROM Products OPEN @getProductName FETCH NEXT FROM @getProductName INTO @ProductName WHILE@@FETCH_STATUS= 0 BEGIN PRINT'Product #'+CAST(@idx ASchar(3))+ @ProductName SET @idx= @idx+1 FETCH NEXT FROM @getProductName INTO @ProductName END CLOSE @getProductName DEALLOCATE @getProductName

16. Cursor Functions @@FETCH_STATUS @@CURSOR_ROWS CURSOR_STATUS

17. @@FETCH_STATUS • The most commonly used cursor function is @@FETCH_STATUS. • This function determines whether FETCH keyword has successfully retrieved a row from the current cursor. • @@FETCH_STATUS can take one of the three values:

18. @@FETCH_STATUS Example DECLARE @employee_full_name VARCHAR(85) DECLARE employee_cursor CURSORFORSELECTTOP 5 FirstName +' '+ LastName +' '+ Title FROM Employees OPEN employee_cursor FETCH NEXT FROM employee_cursor INTO @employee_full_name WHILE@@FETCH_STATUS= 0 BEGIN FETCH NEXT FROM employee_cursor INTO @employee_full_name SELECT@@FETCH_STATUSAS fetch_status END CLOSE employee_cursor DEALLOCATE employee_cursor

19. @@CURSOR_ROWS • @@CURSOR_ROWS function returns the number of rows in the cursor which was opened last on the current connection. • This means that if you have 3 cursors open @@CURSOR_ROWS will return the number of rows in the 3rd cursor. • @@CURSOR_ROWS can take the following values:

20. @@CURSOR_ROWS Example DECLARE @last_name VARCHAR(20),@first_name VARCHAR(20) DECLARE MY_CURSOR CURSORFOR SELECTTOP 3 LASTNAME, FIRSTNAME FROM EMPLOYEES SELECT'cursor has '+CAST(@@CURSOR_ROWSASVARCHAR)+' rows' OPEN MY_CURSOR FETCH NEXT FROM MY_CURSOR INTO @last_name, @first_name SELECT'cursor has '+CAST(@@CURSOR_ROWSASVARCHAR)+' rows' WHILE@@FETCH_STATUS= 0 BEGIN FETCH NEXT FROM MY_CURSOR INTO @last_name, @first_name END CLOSE MY_CURSOR SELECT'cursor has '+CAST(@@CURSOR_ROWSASVARCHAR)+' rows' DEALLOCATE MY_CURSOR

21. CURSOR_STATUS • The CURSOR_STATUS function can be used effectively within a stored procedure that calls another stored procedure. • Which returns an output parameter of CURSOR data type. • This function can be used with local or global cursors and determines whether or not the stored procedure has returned a cursor with a result set. CURSOR_STATUS( 'local' or 'global', cursor name) CURSOR_STATUS ('variable', cursor variable name)

22. The values returned by CURSOR_STATUS function

23. CURSOR_STATUS Example (1) CREATEPROCEDURE RETURN_EMPLOYEE_NAMES( @last_name VARCHAR(50), @my_cursor CURSORVARYINGOUTPUT) AS BEGIN SETNOCOUNTON SET @my_cursor =CURSOR STATIC FOR SELECT LastName, FirstName FROM Employees WHERE LastName = @last_name OPEN @my_cursor END

24. CURSOR_STATUS Example (2) CREATEPROCEDURE CALL_THE_OTHER_PROCEDURE @last_name VARCHAR(50) AS SETNOCOUNTON DECLARE @first_name VARCHAR(20) DECLARE @my_cursor CURSOR EXECUTE RETURN_EMPLOYEE_NAMES @last_name,@my_cursor OUTPUT IFCURSOR_STATUS('variable','@my_cursor')= 0 BEGIN PRINT'no records found' RETURN END ELSE BEGIN FETCH NEXT FROM @my_cursor INTO @last_name, @first_name WHILE@@FETCH_STATUS= 0 BEGIN SELECT @last_name, @first_name FETCH NEXT FROM @my_cursor INTO @last_name, @first_name END CLOSE @my_cursor DEALLOCATE @my_cursor END

25. CURSOR_STATUS Example (3) EXECUTE call_the_other_procedure 'king'

26. Daftar Pustaka http://www.mssqltips.com/sqlservertip/1160/execute-dynamic-sql-commands-in-sql-server/ http://www.sommarskog.se/dynamic_sql.html http://www.mssqlcity.com/Articles/General/UseCursor.htm http://sqlserverpedia.com/wiki/Built-in_Functions_-_Cursor_Functions