SQL Server Programming and Stored Procedures Fundamentals

Explore the basics of stored procedures in Microsoft SQL Server, including creation, execution, modification, and deletion of a simple stored procedure.

Learn how a procedure groups sql statements under a single heading to boost speed and efficiency, replacing repetitive queries with a reusable stored procedure you can execute.

Learn to write a stored procedure from a select statement using create procedure, the proc shortcut, and begin and end blocks, with the correct database context such as Adventure Works.

Create and verify a new stored procedure in SQL Server, then refresh the object explorer to confirm its appearance under start procedures.

Learn to run a stored procedure in SQL Server using exec from the procedure page or a new query window, with IntelliSense refresh to show results.

Learn how to alter a stored procedure to modify behavior, such as changing order by and adding a new column, and verify updates using both page and object explorer.

Learn to drop a stored procedure in sql server via management studio or a sql statement. Right-click delete and confirm, or execute drop procedure and refresh.

Learn how parameters control results in a stored procedure by passing a numeric parameter to filter products by list price, so different values yield different results.

Add a parameter to a stored procedure by listing parameters in parentheses after the name, with each parameter beginning with @ and declaring @list_price as int.

Learn to use a parameter in the where clause to filter by min list price, create a stored procedure, and verify it in the Adventure Works database using object explorer.

Copy the store procedure name, open a new query window, and run exec with a numeric parameter to retrieve products with a list price greater than that value.

Modify the stored procedure to add new parameters, format the list across lines, and implement min and max price filters with >= and <=, then execute the change.

Learn to name parameters in a SQL Server stored procedure when supplying multiple parameters; the 2017 tooltip reveals parameter names and data types, and naming them does not affect execution.

Learn to use text parameters in stored procedures by adding a four character max prod name parameter, employing like with wildcards and concatenation, to search products containing the supplied text.

Learn how to make stored procedure parameters optional, avoiding the need to pass values for all three parameters on every execution.

Learn to give default values to parameters in stored procedures, making them optional, update the procedure, and test by omitting the parameter to use zero for the minimum list price.

Demonstrates using null as a default for stored procedure parameters in sql server and updating the where clause to handle null min and max list prices.

Learn to use variables in SQL Server: declare, assign, and reference them in queries, display with print or select, and access global variables for system information.

Learn how variables store a single value of a specific type in SQL Server programming, and use a date variable across multiple unioned selects to simplify date changes.

Learn to declare variables in SQL Server, placing the @variable at the top of a procedure, naming it, and assigning the date time data type.

Learn to assign a value to a variable in SQL Server using set, matching the value to the declared data type. Emphasizes syntax, variable naming, and data type compatibility.

Reference a variable in a query and replace dates with the variable name; changing the variable value updates results on re-execution and shows how variables speed up stored procedure writing.

Learn to store query results in variables with the set statement in SQL Server, using a select subquery. Compute totals such as transactions from production.transaction_history filtered by date.

Learn to query the value in a variable and display it in the results panel by selecting variable values and using union to show transactions and sales orders.

Demonstrate printing variable values in SQL Server Management Studio's messages tab using print, converting to text with cast or convert, and concatenating with plus for clear sentences.

Learn to set values of variables in a select list by declaring three variables, assigning fields in the select list, and then selecting the variables to display.

Gather values into prod list by building a comma separated list of product names from production product table for 2008, initialize, append, and use the char function for line breaks.

Discover built-in global variables in SQL Server to enhance IntelliSense, reveal the server name and version, and use the row count variable to track rows affected.

Explore how to use input and output parameters and return values in Microsoft SQL Server, including techniques to retrieve output results and utilize stored procedure return values.

Review how input parameters in a stored procedure work using an integer year parameter in the where clause to fetch product names sold in a specific year, demonstrated with 2013.

Describe and define output parameters to return data from a procedure, adding product list (varchar max) and product count (int) as outputs, including a comma-separated list of product names.

Learn to set output parameters in a stored procedure, computing the row count and building a comma-separated product list with a declared variable, then assign and save.

Learn how to retrieve values from output parameters in a stored procedure, declare variables for results, and display the product list and count using a script.

Explore output parameters and return values in stored procedures, noting you can have multiple output parameters of any data type and a single numeric return value.

Learn to use return values in SQL Server stored procedures by returning the row count of products sold in a provided year, and execute the procedure to retrieve the value.

Learn to capture a stored procedure return value in SQL Server by declaring an integer variable, executing the procedure to assign it, and displaying the result as number of products.

Explore testing conditions in SQL Server with if statements, covering basic syntax, else clauses, and nested structures, and apply if statements inside stored procedures to drive select results.

Describe the if statement and show how to create a logical test that, if true, executes actions like printing a message when the number of accessories exceeds seven.

Learn to use the else clause in SQL Server stored procedures by adding an else branch to an if statement. Handle conditions and customize messages when thresholds are not met.

Learn to use begin and end blocks in SQL Server scripts to group multiple statements for true and false branches, and improve readability with indentation.

Learn how to nest if statements to test multiple conditions, counting accessories and clothing, apply thresholds, and print messages about too many accessories and sufficient clothing to compensate.

Learn to use select statements inside if statements in a SQL Server stored procedure, returning all employees or filtering by gender with independent if statements and a catch-all message.

Explore the fundamentals of while statements in SQL Server, including syntax, looping select statements, breaking loops, handling never-ending loops, and using cursors.

Demonstrates the basic syntax of a while loop in SQL Server, initializing a counter at one, looping while it is less than or equal to 15, printing and incrementing.

Demonstrate using select statements in a while loop to determine the max vacation hours, count employees per hour value, and print text messages with results.

Learn to exit a loop in SQL Server programming by using the break keyword with an if condition that stops when vacation hours reach 20, yielding the final results.

Remove the incrementing marker so the loop condition can become false, preventing a never ending loop. Use the stop button to halt execution and ensure the loop terminates properly now.

Use cursors in loops to traverse the result set, fetch each record, and print subcategories for each product category by querying production.productcategory and production.productsubcategory, while looping until no more records.

Explore user defined scalar functions in SQL Server programming and stored procedures fundamentals, learning how to describe, define inputs and return data, and modify functions for complex queries.

Explore scalar functions in SQL Server, inspect built-in date name function details in object explorer, and learn to create a custom function that encapsulates date formatting logic.

learn to create a scalar valued function by using a template or writing from scratch, and navigate to the scalar valued functions folder in the root database adventure works.

Define a function with create function, name it meaningfully, include input parameters like @date datetime, and return a var char max using a begin end block.

Append code to a function to return a formatted date from input data, rename the input parameter, and execute the script in the correct database to create the function.

Examine and evaluate your scalar valued function in the Adventure Works database using Object Explorer, refresh IntelliSense, and run a test query to confirm proper execution.

Modify a function by changing its definition from create to alter, or use the scalar valued functions folder to alter, then implement a case statement for day-of-month suffixes.

Tackle complicated expressions in a single select by extracting the left side of a product name before the first space, and fix invalid length errors with a user defined function.

Learn to use variables and if statements in SQL Server to parse a product name with charindex, decide output with left, and return the result via a user defined function.

Explore temporary tables in SQL Server, including definitions, two creation methods, appending records, scope and global temporary tables, and how to remove them.

Build temporary tables in SQL Server to hold intermediate results during long operations and retrieve final results from the temp table, focusing on the middle stage of data processing.

learn the first method for temporary table creation in sql server by using an into clause with a select statement to populate a hash-named temp table, then query it.

Create a temporary table with an explicit create table statement, insert the select results into it, and query the temporary table to display product names containing thin.

learn where temporary tables reside in SQL Server: in the system database folder under tempdb, each table with a unique identifier and connection-scoped visibility.

Learn to use variables in SQL Server, starting with a review of temporary tables, then declare and populate a table variable, and compare table variables with advantages, disadvantages, and misconceptions.

Explore how temporary tables work in SQL Server: create with hash mark, insert records, and select results, then compare with table variables introduced in SQL Server 2000.

Declare a table variable in SQL Server, naming it (for example, @products) and setting its data type to table. Define its columns, name varchar(max) and sell start date datetime.

Learn how to push data into a table variable by inserting data similar to a temporary table. Then query the table variable to view its contents and verify results.

Explore why to use table variables versus temporary tables, comparing syntax, behavior, and under-the-hood differences, and learn the technical reasons for choosing one approach.

Understand the life cycle of table variables and their persistence versus temporary tables. See how table variables persist during code execution and why explicit drop statements matter.

Explore the drawbacks of table variables versus temporary tables in SQL Server, noting explicit declare requirements and the inability to use a single insert like select into.