SQL SERVER Procedures&Concepts - Raise Above Beginner Level

Explains dynamic sql by building a string from a declared varchar, holding a top 10 select, and executing the string with a stored procedure to run dynamic queries at runtime.

Create a simple SQL Server procedure for the HR department that returns job category and gender counts. Define two keyword inputs to drive retrieval and set parameters accordingly.

Visualize data by querying the employee table and inspecting its structure, focusing on job title and gender. Count results by gender for single or multiword terms like vice president.

Learn how to design a procedure that counts male and female employees by job title with group by and count, addressing aggregation rules.

Use a case expression inside count to tally males as 1 when gender = 'm' and females as 1 when gender = 'f', group by job title.

Set up the procedure body in SQL Server Management Studio by creating a named procedure with two input varchar(30) parameters, a begin end block, and a dynamic string for execution.

Set up input parameters for a SQL Server procedure to handle two scenarios: a mandatory first word and an optional second word, with defaults NULL to drive the if-else logic.

Develop and test your first if else block in a SQL Server procedure, handling a possible null input and printing null or not null outcomes based on the parameter.

Explain if else logic in sql server procedures by selecting between job title one or two and building a search value that shifts from one word to two words.

Learn how to create dynamic SQL in SQL Server by building a query string, safely escaping quotes, and addressing syntax errors in nested strings.

Place a parameter inside a dynamic query by building a where clause with like to filter job titles, define a search pattern, bind parameters, and then execute the dynamic string.

Learn to trim leading and trailing spaces from input parameters using the built-in trim function, apply it to title parameters, and concatenate trimmed values with a space.

Bind parameters to a search variable and execute a dynamic query with wildcards to retrieve job titles, refining a stored procedure through final execution and parameter binding.

Explore a dynamic sql procedure by stepping through its body, building a query with a dynamic string and wildcards, and testing scenarios that filter job titles like design and engineer.

Explore why XML is essential for cross-application data exchange, teaching you how to pass and decode inputs from external applications to extract parameters and update the database.

Pass data from external applications using a common language into a SQL Server procedure, load into a temporary table, join with product and transaction history, and return type and quantity.

Explore XML structure with a root container and item elements, understand opening and closing tags, and learn how to select item properties like product number and safety stock.

Learn to perform a select from an XML string stored in a variable, starting at the root, and extract item properties such as product number and safety stock into columns.

Define a sql server procedure with an input parameter named sml, perform a select into a temporary table for product number and safety stock, then outline step-by-step joins and cleanup.

Create a temporary table, join production.product on the product number, and connect to production.transaction_history to fetch transaction type, quantity, and transaction date.

Execute final joins between a temporary table and production product and transaction history to align product numbers and safety stock, inspect structure with select top10, and output the required results.

Learn how to test a SQL Server procedure by executing with parameters, using temporary tables, and validating joined outputs from production, product, and history data.

Discover how temporary tables in SQL Server act as regular tables with short lifetimes, created with a hashtag, populated via select into, and reused for holding limited results.

Learn how to declare, open, and fetch a cursor in SQL Server to iterate through each row of a select statement, performing row-by-row transformations.

Create a procedure with an external type to process product searches fed by email, load criteria into a temporary table, and use a cursor to filter input and return results.

Learn to set up a SQL Server procedure by creating a temporary table and a normal table for cursor output, plus a dynamic query with fixed and dynamic parts.

Understand how comparison operators appear inside xml, including greater than, less than, greater or equal, and less or equal, and learn to incorporate them into dynamic sql select statements.

Set up our #tempTables by creating a temporary table named collect_data and inserting the extracted columns such as product number, product key, and list price.

Learn how to build a dynamic sql query by joining an external temp table with product lists and history, selecting product number, name, and list price, filtered by price operators.

Define and declare variables for a cursor, setting three values for two variables—product number, operator, and list price—and pass them through dynamic strings to drive cursor operations.

Declare and open a cursor, fetch next rows in a while loop, and close and deallocate it to manage the cursor body.

Master setting up and iterating a cursor in SQL Server procedures by extracting row values (product number, operator, list price) and building dynamic statements per row.

Build a dynamic SQL string inside a cursor by combining a fixed part with per-row values, then execute or print the dynamic insert into a temporary table.

Learn how to build and execute dynamic SQL in SQL Server, escape quotes, and manage string literals within a dynamic query using variables and placeholders.

Demonstrates step-by-step debugging of a sql server procedure, showing how a dynamic query and cursor use variables and input parameters to populate temp tables and produce the final result.

Explain how dynamic queries built from user input can be vulnerable to SQL injection, and emphasize never trusting external input to prevent attackers accessing sensitive data.

Create a SQL Server procedure to delete old transactions by date, archive them in a temp table, and capture the highest stock quantity for each deleted row using outer apply.

Create a five-step SQL Server procedure to select data older than transaction date, populate temp tables, declare a cursor, delete records, and fetch the highest stock quantity via outer apply.

Select transaction ID and product ID from production transactions, filtering by an input date using convert to date; prepare for a cursor and declare variables.

Declare and open a cursor, fetch each row inside a while loop, and properly close the cursor to set the body of the cursor in SQL Server procedures.

Use the output clause inside a cursor to delete rows by product ID and transaction ID while capturing deleted data for later processing.

Explore the outer apply concept in SQL Server to get the highest stock quantity per product by binding a top 1 subquery per left row, and compare with left join.

Test a created procedure and compare outer apply and cross apply with joins, noting that outer apply yields nulls for unmatched rows while cross apply returns only matched rows.

Set up a procedure that takes a product id, uses temporary tables to fetch latest prices and colors, updates them, and outputs the results.

Create a temporary table and select product id, list price, and color from the production.product table as part of a simulation; then filter the data by the parameter.

Learn to update a table with a join in sql server by aliasing tables, using an inner join, and setting list price and color from the joined table.

Declare and set variables, write a select with alias, avoid select *, use isnull to replace nulls with empty strings, and convert values to the proper type for output.

Finish and test the final SQL Server procedure, using convert, handling nulls to empty strings, and building tagged output with opening and closing tags.