MS SQL Server
Dynamic SQl, T-SQL

• Mostly:

• Dynamic SQL

• Stored Procedure

• Trigger

• Cursor

• Function

• Sayed Ahmed

What are the Most Important
Most Used

• Design ERD

• Convert ERD to database

• Normalization

• Indexing

• SQL

• Stored Procedure

• Dynamic SQL

• These will come, not too frequent

• Function, User Defined Data Types, Temporary Table

• Trigger, Cursor

• SSRS, SSIS, SSAS

• ML in SQL Server (in advanced level)

• May not be important initially

Today

• More on

• Dynamic SQL

• Stored Procedure

• Cursor

Basics of Stored Procedure

• By this time, we are supposed to know

• https://www.sqlservertutorial.net/sql-server-stored-procedures/basic-sql-server-stored-procedures/

• https://www.sqlservertutorial.net/sql-server-stored-procedures/sql-server-stored-procedure-parameters/

• https://www.sqlservertutorial.net/sql-server-stored-procedures/variables/

• Although these topics will come back

• In our discussion today

From Northwind Database

• Write a dynamic SQL

• That will Query the Products table and

• Show all data

• Ref:

• https://www.sqlservertutorial.net/sql-server-stored-procedures/sql-server-dynamic-sql/

Dynamic SQL

• DECLARE    @table NVARCHAR(128), @sql NVARCHAR(MAX);

• SET @table = N'Products'

• SET @sql = N'SELECT * FROM ' + @table;

• EXEC sp_executesql @sql;

Convert the previous to a stored procedure

Using dynamic SQL to query from any table    of Northwind Database

Write a stored Procedure for it
table name as the parameter

Create or ALTER      Procedure    [dbo].[usp_query] ( @table NVARCHAR(128))
AS
BEGIN