Home
Search results “Oracle procedure if select”
Stored Procedure - IF ELSE IF ELSE
 
03:13
Stored Procedure - IF ELSE IF ELSE by Professor Saad Yousuf DB2 UDB, Oracle, SQL Server, MySQL
Views: 6650 Professor Saad
IF Statements in Oracle PL SQL with example
 
07:40
Complete Oracle PL/SQL Tutorial for Beginners Playlist here https://www.youtube.com/playlist?list=PLI5t0u6ye3FE_9SZcS0cQZDU2qn0uB1Oi ************************************************** This Video shows how to use IF THEN , ELSIF THEN and ELSE Statements in Oracle with examples for Beginners.
Views: 2211 yrrhelp
Dynamic SQL in Stored Procedure
 
09:32
In this video we will discuss, using dynamic sql in a stored procedure and it's implications from sql injection perspective. We will discuss performance implications of using dynamic sql in a stored procedure in a later video. Text version of the video http://csharp-video-tutorials.blogspot.com/2017/04/dynamic-sql-in-stored-procedure.html Slides http://csharp-video-tutorials.blogspot.com/2017/04/dynamic-sql-in-stored-procedure_11.html All SQL Server Text Articles http://csharp-video-tutorials.blogspot.com/p/free-sql-server-video-tutorials-for.html All SQL Server Slides http://csharp-video-tutorials.blogspot.com/p/sql-server.html All SQL Server Tutorial Videos https://www.youtube.com/playlist?list=PL08903FB7ACA1C2FB All Dot Net and SQL Server Tutorials in English https://www.youtube.com/user/kudvenkat/playlists?view=1&sort=dd All Dot Net and SQL Server Tutorials in Arabic https://www.youtube.com/c/KudvenkatArabic/playlists Consider the following stored procedure "spSearchEmployees". We implemented this procedure in Part 139 of SQL Server tutorial. This stored procedure does not have any dynamic sql in it. It is all static sql and is immune to sql injection. Create Procedure spSearchEmployees @FirstName nvarchar(100) = NULL, @LastName nvarchar(100) = NULL, @Gender nvarchar(50) = NULL, @Salary int = NULL As Begin Select * from Employees where (FirstName = @FirstName OR @FirstName IS NULL) AND (LastName = @LastName OR @LastName IS NULL) AND (Gender = @Gender OR @Gender IS NULL) AND (Salary = @Salary OR @Salary IS NULL) End Go Whether you are creating your dynamic sql queries in a client application like ASP.NET web application or in a stored procedure, you should never ever concatenate user input values. Instead you should be using parameters. Notice in the following example, we are creating dynamic sql queries by concatenating parameter values, instead of using parameterized queries. This stored procedure is prone to SQL injection. Let's prove this by creating a "Search Page" that calls this procedure. Create Procedure spSearchEmployeesBadDynamicSQL @FirstName nvarchar(100) = NULL, @LastName nvarchar(100) = NULL, @Gender nvarchar(50) = NULL, @Salary int = NULL As Begin Declare @sql nvarchar(max) Set @sql = 'Select * from Employees where 1 = 1' if(@FirstName is not null) Set @sql = @sql + ' and FirstName=''' + @FirstName + '''' if(@LastName is not null) Set @sql = @sql + ' and LastName=''' + @LastName + '''' if(@Gender is not null) Set @sql = @sql + ' and Gender=''' + @Gender + '''' if(@Salary is not null) Set @sql = @sql + ' and Salary=''' + @Salary + '''' Execute sp_executesql @sql End Go Add a Web Page to the project that we have been working with in our previous video. Name it "DynamicSQLInStoredProcedure.aspx". Copy and paste the HTML and code available on my blog at the following link http://csharp-video-tutorials.blogspot.com/2017/04/dynamic-sql-in-stored-procedure.html At this point, run the application and type the following text in the "Firsname" text and click "Search" button. Notice "SalesDB" database is dropped. Our application is prone to SQL injection as we have implemented dynamic sql in our stored procedure by concatenating strings instead of using parameters. ' Drop database SalesDB -- In the following stored procedure we have implemented dynamic sql by using parameters, so this is not prone to sql injecttion. This is an example for good dynamic sql implementation. Create Procedure spSearchEmployeesGoodDynamicSQL @FirstName nvarchar(100) = NULL, @LastName nvarchar(100) = NULL, @Gender nvarchar(50) = NULL, @Salary int = NULL As Begin Declare @sql nvarchar(max) Declare @sqlParams nvarchar(max) Set @sql = 'Select * from Employees where 1 = 1' if(@FirstName is not null) Set @sql = @sql + ' and [email protected]' if(@LastName is not null) Set @sql = @sql + ' and [email protected]' if(@Gender is not null) Set @sql = @sql + ' and [email protected]' if(@Salary is not null) Set @sql = @sql + ' and [email protected]' Execute sp_executesql @sql, N'@FN nvarchar(50), @LN nvarchar(50), @Gen nvarchar(50), @sal int', @[email protected], @[email protected], @[email protected], @[email protected] End Go On the code-behind page, use stored procedure spSearchEmployeesGoodDynamicSQL instead of spSearchEmployeesBadDynamicSQL. We do not have to change any other code. At this point run the application one more time and type the following text in the "Firstname" textbox and click the "Search" button. ' Drop database SalesDB -- Notice "SalesDB" database is not dropped, So in this case our application is not succeptible to SQL injection attack. Summary : Whether you are creating dynamic sql in a client application (like a web application) or in a stored procedure always use parameters instead of concatnating strings. Using parameters to create dynamic sql statements prevents sql injection.
Views: 34731 kudvenkat
SQL Stored Procedure - If Condition
 
03:38
SQL Stored Procedure - If Condition Calculate Salary Overtime by: Professor Saad Yousuf DB2 UDB, Oracle, SQL Server, MySQL
Views: 3756 Professor Saad
PL/SQL tutorial 3: SELECT INTO statement in PL/SQL by Manish Sharma RebellionRider
 
05:22
How to initialize the variable by fetching values from the table of the database or by using SELECT INTO statement in PL/SQL by Manish Sharma RebellionRider ------------------------------------------------------------------------ ►►►LINKS◄◄◄ Blog : http://bit.ly/select-into-statement-pl-sql Previous Tutorial ► Variables: https://youtu.be/2MNmodawvnE ------------------------------------------------------------------------- ►►►Let's Get Free Uber Cab◄◄◄ Use Referral Code UberRebellionRider and get $20 free for your first ride. -------------------------------------------------------------------------- ►Make sure you SUBSCRIBE and be the 1st one to see my videos! ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ ►►►Find me on Social Media◄◄◄ Follow What I am up to as it happens on https://twitter.com/rebellionrider https://www.facebook.com/imthebhardwaj http://instagram.com/rebellionrider https://plus.google.com/+Rebellionrider http://in.linkedin.com/in/mannbhardwaj/ http://rebellionrider.tumblr.com/ http://www.pinterest.com/rebellionrider/ You can also Email me at for E-mail address please check About section Please please LIKE and SHARE my videos it makes me happy. Thanks for liking, commenting, sharing and watching more of our videos This is Manish from RebellionRider.com ♥ I LOVE ALL MY VIEWERS AND SUBSCRIBERS
Views: 127259 Manish Sharma
How to Find if Table/View is used by Stored Procedure in SQL Server - SQL Server Interview Questions
 
06:44
In this video, we are going to learn how to find out if the table is used in a Stored Procedure. we have multiple ways to do that. This System Stored Procedure will return you list of table/views used by the Stored procedure/View. You can pass the view name or Stored Procedure as parameter,it will return you tables/views which are used in the object. One disadvantage of using sp_depends is , it will not show you tables/views which are not in current database. If you have used objects from other databases, this information might be misleading. --By using sp_depends, You can provide Stored Procedure Name or View name as parameter EXEC sp_depends '[dbo].[GetCustomer]' The 2nd method is to use the sys.sysdepends view with combination of sys.sysobjects to get the information. This also works the same way. It will not show you cross database objects ( Tables,Views) if have used in Stored Procedure or view definition. --Get Information from sys.sysdepends SELECT DISTINCT OBJECT_NAME(SD.id) AS StoredProcedureNameName, OB.name AS TableOrViewName FROM sys.sysdepends SDINNER JOIN sys.sysobjects O ON SD.id=O.id INNER JOIN sys.sysobjects OB ON SD.depid=OB.id AND O.xtype='P' 3rd method is to use the sys.all_sql_modules system view. The definition column of this view has definition of Stored Procedure, Views, Functions etc.You can write query as given below to check if any of the Stored Procedure or object is using the table/view you are looking for. --Use sys.all_sql_modules system view to get information if table is used --in a Stored Procedure SELECT OBJECT_NAME(OBJECT_ID),definition FROM sys.all_sql_modules WHERE definition LIKE '%vw_Employee%' Link to post: http://sqlage.blogspot.com/2015/02/how-to-find-if-tableview-is-used-in.html
Views: 10337 TechBrothersIT
Stored procedures in sql server   Part 18
 
20:11
In this video we will learn 1. What is a stored procedure 2. Stored Procedure example 3. Creating a stored procedure with parameters 4. Altering SP 5. Viewing the text of the SP 6. Dropping the SP 7. Encrypting stored procedure Text version of the video http://csharp-video-tutorials.blogspot.com/2012/08/stored-procedures-part-18.html Slides http://csharp-video-tutorials.blogspot.com/2013/08/part-18-stored-procedures.html All SQL Server Text Articles http://csharp-video-tutorials.blogspot.com/p/free-sql-server-video-tutorials-for.html All SQL Server Slides http://csharp-video-tutorials.blogspot.com/p/sql-server.html All Dot Net and SQL Server Tutorials in English https://www.youtube.com/user/kudvenkat/playlists?view=1&sort=dd All Dot Net and SQL Server Tutorials in Arabic https://www.youtube.com/c/KudvenkatArabic/playlists
Views: 757315 kudvenkat
SQL Server Programming Part 1 - Stored Procedure Basics
 
10:31
If you'd like to help fund Wise Owl's conversion of tea and biscuits into quality training videos you can click this link https://www.wiseowl.co.uk/donate?t=1 to make a donation. Thanks for watching! By Andrew Gould https://www.wiseowl.co.uk - If you ever find yourself writing the same SQL query over and over again, a Stored Procedure could be just the time-saving tool you're looking for. This video introduces you to the concept of stored procedures and will show you how to create, execute and modify these useful elements of SQL Server. You can see a written version of this tutorial at: https://www.wiseowl.co.uk/blog/s263/stored-procedures.htm You can download the script to create the Movies database used in this video at the following link: https://www.wiseowl.co.uk/files/execise-question-files/qf-898.zip You can see the range of resources and courses we offer in SQL Server here: https://www.wiseowl.co.uk/sql/ Visit www.wiseowl.co.uk for more online training resources in Microsoft Excel, Microsoft Access, Microsoft PowerPoint, Microsoft Word, Microsoft Project, Microsoft Publisher, Microsoft Visio, SQL Server, Reporting Services, Analysis Services, Visual Studio, ASP.NET, VB.NET, C# and more!
Views: 303079 WiseOwlTutorials
PL/SQL tutorial IF THEN ELSE (IF-ELSE) Statement in PL/SQL
 
04:07
This video is about pl sql basics,pl sql basic programs,basic pl sql programs,oracle pl sql basics,pl sql basics with examples,basic pl sql,basic pl sql queries,basics of pl sql,pl sql basics tutorial,pl sql basic concepts,basic pl
Views: 165 Nayabsoft
Cursor in a Stored Procedure Part 1
 
15:01
More information at: http://prof-oracle.blogspot.com/2018/01/cursor-in-stored-procedure.html
Views: 210 Gove Allen
SQL Server Programming Part 2 - Stored Procedure Parameters
 
20:48
If you'd like to help fund Wise Owl's conversion of tea and biscuits into quality training videos you can click this link https://www.wiseowl.co.uk/donate?t=1 to make a donation. Thanks for watching! By Andrew Gould https://www.wiseowl.co.uk - Stored Procedures are extremely useful tools in SQL Server, and they become even more powerful when you use parameters to pass information to them. This video teaches you how to define a list of parameters for a stored procedure and how to call a parameterised procedure and pass information to it. You'll also learn how to create optional parameters with default values, how to handle NULLs passed to a procedure and, finally, how to use your procedure in other applications such as Reporting Services. You can read a written version of this tutorial here: https://www.wiseowl.co.uk/blog/s263/parameters.htm You can download the script to create the Movies database used in this video at the following link: https://www.wiseowl.co.uk/files/execise-question-files/qf-898.zip You can see the range of resources and courses we offer in SQL Server here: https://www.wiseowl.co.uk/sql/ Visit www.wiseowl.co.uk for more online training resources in Microsoft Excel, Microsoft Access, Microsoft PowerPoint, Microsoft Word, Microsoft Project, Microsoft Publisher, Microsoft Visio, SQL Server, Reporting Services, Analysis Services, Visual Studio, ASP.NET, VB.NET, C# and more!
Views: 169306 WiseOwlTutorials
SQL Stored Procedures - What They Are, Best Practices, Security, and More...
 
44:34
Newsletter signup (with exclusive discounts): https://iamtimcorey.com/general-sign-up (your email will be kept safe and you will not be spammed). Facebook Page: https://www.facebook.com/IAmTimCorey/ Want to see a full C# application built from scratch? Check out this course: https://iamtimcorey.com/courses/csharp-from-start-to-finish/ Microsoft SQL can be confusing. There are a TON of features, options, and settings to consider when attempting to use it. One of my favorite features is stored procedures. These pre-compiled queries allow you to safely and securely access and modify data. In this video, you will learn the basics of how to build a stored procedure, as well as what the benefits and drawbacks of stored procedures are. You will also learn some best practices, including how to secure your entire database in just a few simple commands.
Views: 50050 IAmTimCorey
Learning PL/SQL programming
 
29:21
Download the session ppts @ https://drive.google.com/file/d/0B_2D199JLIIpLXp5cm9QMFpVS00/view?usp=sharing 3:05 - Procedures 6:48 - Cursors 15:13 - Functions 16:36 - Triggers 21:35 - Package 23:59 - Exceptions
Views: 150568 BBarters
PL SQL Tutorials for Beginners | IF THEN ELSE Conditions
 
08:44
PL/SQL includes procedural language elements such as conditions and loops. It allows declaration of constants and variables, procedures and functions, types and variables of those types, and triggers. It can handle exceptions (runtime errors). Arrays are supported involving the use of PL/SQL collections. Implementations from version 8 of Oracle Database onwards have included features associated with object-orientation. One can create PL/SQL units such as procedures, functions, packages, types, and triggers, which are stored in the database for reuse by applications that use any of the Oracle Database programmatic interfaces. The IF-THEN-ELSIF statement allows you to choose between several alternatives. An IF-THEN statement can be followed by an optional ELSIF...ELSE statement. The ELSIF clause lets you add additional conditions. When using IF-THEN-ELSIF statements there are few points to keep in mind. It's ELSIF, not ELSEIF An IF-THEN statement can have zero or one ELSE's and it must come after any ELSIF's. An IF-THEN statement can have zero to many ELSIF's and they must come before the ELSE. Once an ELSIF succeeds, none of the remaining ELSIF's or ELSE's will be tested. Subscribe to our Channel for more videos https://www.youtube.com/channel/UC7sbHUgN8FnJEZkEjvKTwJg
Views: 131 Puzzle Guru
Select statement in sql server - Part 10
 
21:54
In this video we will learn 1. Select specific or all columns 2. Distinct rows 3. Filtering with where clause. 4. Wild Cards in SQL Server 5. Joining multiple conditions using AND and OR operators 6. Sorting rows using order by 7. Selecting top n or top n percentage of rows Text version of the video http://csharp-video-tutorials.blogspot.com/2012/08/select-statement-part-10.html Slides http://csharp-video-tutorials.blogspot.com/2013/08/part-10-all-about-select.html All SQL Server Text Articles http://csharp-video-tutorials.blogspot.com/p/free-sql-server-video-tutorials-for.html All SQL Server Slides http://csharp-video-tutorials.blogspot.com/p/sql-server.html All Dot Net and SQL Server Tutorials in English https://www.youtube.com/user/kudvenkat/playlists?view=1&sort=dd All Dot Net and SQL Server Tutorials in Arabic https://www.youtube.com/c/KudvenkatArabic/playlists
Views: 346469 kudvenkat
Calling a stored procedure with output parameters   Part 7
 
20:13
Text version of the video http://csharp-video-tutorials.blogspot.com/2012/10/calling-stored-procedure-with-output.html Slides http://csharp-video-tutorials.blogspot.com/2013/09/part-7-calling-stored-procedure-with.html All ADO .NET Text Articles http://csharp-video-tutorials.blogspot.com/p/free-adonet-video-tutorial.html All ADO .NET Slides http://csharp-video-tutorials.blogspot.com/p/adonet-slides.html All Dot Net and SQL Server Tutorials in English https://www.youtube.com/user/kudvenkat/playlists?view=1&sort=dd All Dot Net and SQL Server Tutorials in Arabic https://www.youtube.com/c/KudvenkatArabic/playlists In this video, we will learn about calling a stored procedure with output parameters from an asp.net web application.
Views: 192446 kudvenkat
Part 5   SQL query to find employees hired in last n months
 
04:53
Link for all dot net and sql server video tutorial playlists http://www.youtube.com/user/kudvenkat/playlists Link for slides, code samples and text version of the video http://csharp-video-tutorials.blogspot.com/2014/05/part-5-sql-query-to-find-employees.html This question is asked is many sql server interviews. If you have used DATEDIFF() sql server function then you already know the answer. -- Replace N with number of months Select * FROM Employees Where DATEDIFF(MONTH, HireDate, GETDATE()) Between 1 and N
Views: 178962 kudvenkat
How to Write Stored Procedure in SQL Server for Beginners
 
07:37
This Video explains how to write stored procedures in SQL Server 2008 and SQL Server 2014 using SQL Server Managment Studio. How to Write Queries in SQL Server and MS Access here: https://www.youtube.com/watch?v=RxJ8TbIh7-8 SQL Databases and Database Management Systems for Beginners(Practicals): https://www.youtube.com/watch?v=x54qJrlQEY8 SQL Databases and Database Management Systems for Beginners(Hands-On Lab): https://www.youtube.com/watch?v=6x9fP8gMTe8 How To Create a Database in MS SQL Management Studio: https://youtu.be/zqo2ZBxxn6M?list=PL9l1zUfnZkZno9dL-VCB5JyTiBRKqs8_J How to Write Stored Procedure in SQL Server is Really very easy More on: http://www.kttpro.com http://kindsonmunonye.blogspot.com http://kttpro.blogspot.com
Views: 52886 Kindson The Tech Pro
SQL Aggregation queries using Group By, Sum, Count and Having
 
10:01
From SQL Queries Joes 2 Pros (Vol2) ch4.1. Learn up to write aggregated queries.
Views: 177466 Joes2Pros SQL Trainings
SQL Server Integration Services - How to use Stored Procedure in OLE DB Source in SSIS Package
 
06:19
In this video we will learn the answer of SQL Server Integration Services(SSIS) Interview Question " Can we use Stored Procedure in OLE DB Source? If yes, how do you map the parameters?" How to use Stored Procedure in OLE DB Source in SSIS Package How to map parameters in OLE DB Source in SSIS Package How to pass value of variable to Stored Procedure parameter in OLE DB Source in Data Flow in SSIS Package To Follow Step by Step this SSIS video tutorial By Tech Brothers http://sqlage.blogspot.com/search/label/SSIS%20Video%20Tutorial
Views: 28319 TechBrothersIT
SQL Server Programming Part 6 - WHILE Loops
 
14:48
If you'd like to help fund Wise Owl's conversion of tea and biscuits into quality training videos you can click this link https://www.wiseowl.co.uk/donate?t=1 to make a donation. Thanks for watching! By Andrew Gould https://www.wiseowl.co.uk - In SQL Server there is only one type of loop: a WHILE loop. This video teaches you how to use them, from the basic syntax of the WHILE statement, through how to use a SELECT statement within a loop. We'll also cover how to use the BREAK command to exit from a loop, what to do when you inevitably find yourself in an endless loop and, finally, a quick introduction to using loops with cursors. You can download the script to create the Movies database used in this video at the following link: https://www.wiseowl.co.uk/files/execise-question-files/qf-898.zip You can see the range of resources and courses we offer in SQL Server here: https://www.wiseowl.co.uk/sql/ Visit www.wiseowl.co.uk for more online training resources in Microsoft Excel, Microsoft Access, Microsoft PowerPoint, Microsoft Word, Microsoft Project, Microsoft Publisher, Microsoft Visio, SQL Server, Reporting Services, Analysis Services, Visual Studio, ASP.NET, VB.NET, C# and more!
Views: 75008 WiseOwlTutorials
Debugging sql server stored procedures
 
15:11
debugging in ssms debugging t-sql code t sql debug stored procedure sql server management studio debug stored procedure how to debug t sql debug in sql server management studio In this video we will discuss how to debug stored procedures in SQL Server. Setting up the Debugger in SSMS : If you have connected to SQL Server using (local) or . (period), and when you start the debugger you will get the following error Unable to start T-SQL Debugging. Could not connect to computer. To fix this error, use the computer name to connect to the SQL Server instead of using (local) or . For the examples in this video we will be using the following stored procedure. Create procedure spPrintEvenNumbers @Target int as Begin Declare @StartNumber int Set @StartNumber = 1 while(@StartNumber [ @Target) Begin If(@StartNumber%2 = 0) Begin Print @StartNumber End Set @StartNumber = @StartNumber + 1 End Print 'Finished printing even numbers till ' + RTRIM(@Target) End Connect to SQL Server using your computer name, and then execute the above code to create the stored procedure. At this point, open a New Query window. Copy and paste the following T-SQL code to execute the stored procedure. DECLARE @TargetNumber INT SET @TargetNumber = 10 EXECUTE spPrintEvenNumbers @TargetNumber Print 'Done' Starting the Debugger in SSMS : There are 2 ways to start the debugger 1. In SSMS, click on the Debug Menu and select Start Debugging 2. Use the keyboard shortcut ALT + F5 At this point you should have the debugger running. The line that is about to be executed is marked with an yellow arrow Step Over, Step into and Step Out in SSMS : You can find the keyboard shortcuts in the Debug menu in SSMS. Let us understand what Step Over, Step into and Step Out does when debugging the following piece of code 1. There is no difference when you STEP INTO (F11) or STEP OVER (F10) the code on LINE 2 2. On LINE 3, we are calling a Stored Procedure. On this statement if we press F10 (STEP OVER), it won't give us the opportunity to debug the stored procedure code. To be able to debug the stored procedure code you will have to STEP INTO it by pressing F11. 3. If the debugger is in the stored procedure, and you don't want to debug line by line with in that stored procedure, you can STEP OUT of it by pressing SHIFT + F11. When you do this, the debugger completes the execution of the stored procedure and waits on the next line in the main query, i.e on LINE 4 in this example. To stop debugging : There are 2 ways to stop debugging 1. In SSMS, click on the Debug Menu and select Stop Debugging 2. Use the keyboard shortcut SHIFT + F5 Show Next Statement shows the next statement that the debugger is about to execute. Run to Cursor command executes all the statements in a batch up to the current cursor position Locals Window in SSMS : Displays the current values of variables and parameters If you cannot see the locals window or if you have closed it and if you want to open it, you can do so using the following menu option. Locals window is only available if you are in DEBUG mode. Watch Window in SSMS : Just like Locals window, Watch window is used to watch the values of variables. You can add and remove variables from the watch window. To add a variable to the Watch Window, right click on the variable and select "Add Watch" option from the context menu. Call Stack Window in SSMS : Allows you to navigate up and down the call stack to see what values your application is storing at different levels. It's an invaluable tool for determining why your code is doing what it's doing. Immediate Window in SSMS : Very helpful during debugging to evaluate expressions, and print variable values. To clear immediate window type ]cls and press enter. Breakpoints in SSMS : There are 2 ways to set a breakpoint in SSMS. 1. By clicking on the grey margin on the left hand side in SSMS (to remove click again) 2. By pressing F9 (to remove press F9 again) Enable, Disable or Delete all breakpoints : There are 2 ways to Enable, Disable or Delete all breakpoints 1. From the Debug menu 2. From the Breakpoints window. To view Breakpoints window select Debug =] Windows =] Breakpoints or use the keyboard shortcut ALT + CTRL + B Conditional Breakpoint : Conditional Breakpoints are hit only when the specified condition is met. These are extremely useful when you have some kind of a loop and you want to break, only when the loop variable has a specific value (For example loop varible = 100). How to set a conditional break point in SSMS : 1. Right click on the Breakpoint and select Condition from the context menu 2. In the Breakpoint window specify the condition Text version of the video http://csharp-video-tutorials.blogspot.com/2015/09/debugging-sql-server-stored-procedures.html Slides http://csharp-video-tutorials.blogspot.com/2015/09/debugging-sql-server-stored-procedures_28.html
Views: 111361 kudvenkat
Stored Procedures in PL/SQL | Oracle PL/SQL Tutorial Videos | Mr.Vijay Kumar
 
18:54
Stored Procedures:- A Stored Procedure is also a Named PL/SQL block the accepts some input in the form of parameters and performs some task and many or many not returns a value Procedures are created to perform one or more dml operations over database.
Views: 54356 Naresh i Technologies
PL/SQL tutorial 4: Anchored Datatype (%TYPE) in PL/SQL By Manish Sharma RebellionRider
 
06:10
Anchored datatype or %TYPE in PL/SQL are those data type which you assign to a variable based on a database object. Watch this PL/SQL Tutorial 4 and leanr about %TYPE with examples By Manish Sharma from wwww.RebellionRider.com ------------------------------------------------------------------------ ►►►LINKS◄◄◄ Blog : http://bit.ly/anchored-datatype Previous Tutorial ► SELECT...INTO statement in PL/SQL https://youtu.be/F5eMJhwmCQs ------------------------------------------------------------------------- ►►►Let's Get Free Uber Cab◄◄◄ Use Referral Code UberRebellionRider and get $20 free for your first ride. ------------------------------------------------------------------------- ►Make sure you SUBSCRIBE and be the 1st one to see my videos! ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ ►►►Find me on Social Media◄◄◄ Follow What I am up to as it happens on https://twitter.com/rebellionrider https://www.facebook.com/imthebhardwaj http://instagram.com/rebellionrider https://plus.google.com/+Rebellionrider http://in.linkedin.com/in/mannbhardwaj/ http://rebellionrider.tumblr.com/ http://www.pinterest.com/rebellionrider/ You can also Email me at for E-mail address please check About section Please please LIKE and SHARE my videos it makes me happy. Thanks for liking, commenting, sharing and watching more of our videos This is Manish from RebellionRider.com ♥ I LOVE ALL MY VIEWERS AND SUBSCRIBERS
Views: 107017 Manish Sharma
Stored procedure output parameters or return values   Part 20
 
16:45
In this video we will 1. Understand what are stored procedure return values 2. Difference between stored procedure return values and output parameters 3. When to use output parameters over return values Text version of the video http://csharp-video-tutorials.blogspot.com/2012/08/stored-procedure-output-parameters-or.html Slides http://csharp-video-tutorials.blogspot.com/2013/08/part-20-stored-procedure-output.html All SQL Server Text Articles http://csharp-video-tutorials.blogspot.com/p/free-sql-server-video-tutorials-for.html All SQL Server Slides http://csharp-video-tutorials.blogspot.com/p/sql-server.html All Dot Net and SQL Server Tutorials in English https://www.youtube.com/user/kudvenkat/playlists?view=1&sort=dd All Dot Net and SQL Server Tutorials in Arabic https://www.youtube.com/c/KudvenkatArabic/playlists
Views: 285265 kudvenkat
Oracle Part 15 Stored Proc Conditions and Calls
 
26:13
If conditions, insert statement in SP, call another SP
Views: 4776 Free OpenMentor
How To Troubleshoot a Slow Running Query in SQL Server (Extended Events & Wait Stats) by Amit Bansal
 
17:48
In this video, you will understand how to track waits stats for a given query/session using Extended Events. Video resources including presentation, demo files, code snippets and more learning material is available on http://www.dataplatformgeeks.com/ (Join for free and access all the resources) Suggest us topics that you wish to learn through our videos: http://www.dataplatformgeeks.com/dpg-suggest-topics/ Connect With The Speaker (Amit Bansal) -Follow on Twitter: https://twitter.com/A_Bansal -Follow on FaceBook at http://www.facebook.com/amit.r.bansal -Follow on LinkedIN: http://www.linkedin.com/in/amitbansal2010 -Facebook Page: https://www.facebook.com/AmitRSBansal/ Connect with SQLMaestros: http://sqlmaestros.com/ -http://www.twitter.com/SQLMaestros -https://www.facebook.com/SQLMaestros -Email us: [email protected] Have technical questions? Join the largest SQL/Data group on FaceBook – https://www.facebook.com/groups/thesqlgeeks/ DataPlatformGeeks (DPG) Community Join the fastest growing community of data & analytics professionals Why Join DPG? http://www.dataplatformgeeks.com/ -Attend all events hosted by DPG, including SQLMaestros Special Events -Get access to free videos, labs, magazines and host of learning resources -Download all events & conference material -Learn new skills. Sharpen existing skills -Be part of Asia’s Largest Data/Analytics Community -Opportunity to be a regional mentor & speaker at our events -Immense technical & professional development -http://www.dataplatformgeeks.com/
Views: 51105 SQLMaestros
Difference between where and having in sql server
 
06:36
sql server where vs having sql server group by where having group by having sql server In this vide we will discuss the difference between where and having caluses in sql server. Let us understand the difference with an example. To calculate total sales by product, we would write a GROUP BY query as shown below SELECT Product, SUM(SaleAmount) AS TotalSales FROM Sales GROUP BY Product Now if we want to find only those products where the total sales amount is greater than $1000, we will use HAVING clause to filter products SELECT Product, SUM(SaleAmount) AS TotalSales FROM Sales GROUP BY Product HAVING SUM(SaleAmount) ] 1000 If we use WHERE clause instead of HAVING clause, we will get a syntax error. This is because the WHERE clause doesn’t work with aggregate functions like sum, min, max, avg, etc. SELECT Product, SUM(SaleAmount) AS TotalSales FROM Sales GROUP BY Product WHERE SUM(SaleAmount) ] 1000 So in short, the difference is WHERE clause cannot be used with aggregates where as HAVING can. However, there are other differences as well that we need to keep in mind when using WHERE and HAVING clauses. WHERE clause filters rows before aggregate calculations are performed where as HAVING clause filters rows after aggregate calculations are performed. Let us understand this with an example. Total sales of iPhone and Speakers can be calculated by using either WHERE or HAVING clause Calculate Total sales of iPhone and Speakers using WHERE clause : In this example the WHERE clause retrieves only iPhone and Speaker products and then performs the sum. SELECT Product, SUM(SaleAmount) AS TotalSales FROM Sales WHERE Product in ('iPhone', 'Speakers') GROUP BY Product Calculate Total sales of iPhone and Speakers using HAVING clause : This example retrieves all rows from Sales table, performs the sum and then removes all products except iPhone and Speakers. SELECT Product, SUM(SaleAmount) AS TotalSales FROM Sales GROUP BY Product HAVING Product in ('iPhone', 'Speakers') So from a performance standpoint, HAVING is slower than WHERE and should be avoided when possible. Another difference is WHERE comes before GROUP BY and HAVING comes after GROUP BY. Difference between WHERE and Having 1. WHERE clause cannot be used with aggregates where as HAVING can. This means WHERE clause is used for filtering individual rows where as HAVING clause is used to filter groups. 2. WHERE comes before GROUP BY. This means WHERE clause filters rows before aggregate calculations are performed. HAVING comes after GROUP BY. This means HAVING clause filters rows after aggregate calculations are performed. So from a performance standpoint, HAVING is slower than WHERE and should be avoided when possible. 3. WHERE and HAVING can be used together in a SELECT query. In this case WHERE clause is applied first to filter individual rows. The rows are then grouped and aggregate calculations are performed, and then the HAVING clause filters the groups. Text version of the video http://csharp-video-tutorials.blogspot.com/2015/09/difference-between-where-and-having-in.html Slides http://csharp-video-tutorials.blogspot.com/2015/09/difference-between-where-and-having-in_15.html All SQL Server Text Articles http://csharp-video-tutorials.blogspot.com/p/free-sql-server-video-tutorials-for.html All SQL Server Slides http://csharp-video-tutorials.blogspot.com/p/sql-server.html Full SQL Server Course https://www.youtube.com/playlist?list=PL4cyC4G0M1RQ_Rm52cQ4CcOJ_T_HXeMB4 All Dot Net and SQL Server Tutorials in English https://www.youtube.com/user/kudvenkat/playlists?view=1&sort=dd All Dot Net and SQL Server Tutorials in Arabic https://www.youtube.com/c/KudvenkatArabic/playlists
Views: 71322 kudvenkat
PL/SQL: Stored Procedure Part-2
 
04:38
In this tutorial, you'll learn what is stored procedure. PL/SQL (Procedural Language/Structured Query Language) is Oracle Corporation's procedural extension for SQL and the Oracle relational database. PL/SQL is available in Oracle Database (since version 7), TimesTen in-memory database (since version 11.2.1), and IBM DB2 (since version 9.7).[1] Oracle Corporation usually extends PL/SQL functionality with each successive release of the Oracle Database. PL/SQL includes procedural language elements such as conditions and loops. It allows declaration of constants and variables, procedures and functions, types and variables of those types, and triggers. It can handle exceptions (runtime errors). Arrays are supported involving the use of PL/SQL collections. Implementations from version 8 of Oracle Database onwards have included features associated with object-orientation. One can create PL/SQL units such as procedures, functions, packages, types, and triggers, which are stored in the database for reuse by applications that use any of the Oracle Database programmatic interfaces. PL/SQL works analogously to the embedded procedural languages associated with other relational databases. For example, Sybase ASE and Microsoft SQL Server have Transact-SQL, PostgreSQL has PL/pgSQL (which emulates PL/SQL to an extent), and IBM DB2 includes SQL Procedural Language,[2] which conforms to the ISO SQL’s SQL/PSM standard. The designers of PL/SQL modeled its syntax on that of Ada. Both Ada and PL/SQL have Pascal as a common ancestor, and so PL/SQL also resembles Pascal in several aspects. However, the structure of a PL/SQL package does not resemble the basic Object Pascal program structure as implemented by a Borland Delphi or Free Pascal unit. Programmers can define public and private global data-types, constants and static variables in a PL/SQL package.[3] PL/SQL also allows for the definition of classes and instantiating these as objects in PL/SQL code. This resembles usage in object-oriented programming languages like Object Pascal, C++ and Java. PL/SQL refers to a class as an "Abstract Data Type" (ADT) or "User Defined Type" (UDT), and defines it as an Oracle SQL data-type as opposed to a PL/SQL user-defined type, allowing its use in both the Oracle SQL Engine and the Oracle PL/SQL engine. The constructor and methods of an Abstract Data Type are written in PL/SQL. The resulting Abstract Data Type can operate as an object class in PL/SQL. Such objects can also persist as column values in Oracle database tables. PL/SQL is fundamentally distinct from Transact-SQL, despite superficial similarities. Porting code from one to the other usually involves non-trivial work, not only due to the differences in the feature sets of the two languages,[4] but also due to the very significant differences in the way Oracle and SQL Server deal with concurrency and locking. There are software tools available that claim to facilitate porting including Oracle Translation Scratch Editor,[5] CEITON MSSQL/Oracle Compiler [6] and SwisSQL.[7] The StepSqlite product is a PL/SQL compiler for the popular small database SQLite. PL/SQL Program Unit A PL/SQL program unit is one of the following: PL/SQL anonymous block, procedure, function, package specification, package body, trigger, type specification, type body, library. Program units are the PL/SQL source code that is compiled, developed and ultimately executed on the database. The basic unit of a PL/SQL source program is the block, which groups together related declarations and statements. A PL/SQL block is defined by the keywords DECLARE, BEGIN, EXCEPTION, and END. These keywords divide the block into a declarative part, an executable part, and an exception-handling part. The declaration section is optional and may be used to define and initialize constants and variables. If a variable is not initialized then it defaults to NULL value. The optional exception-handling part is used to handle run time errors. Only the executable part is required. A block can have a label. Package Packages are groups of conceptually linked functions, procedures, variables, PL/SQL table and record TYPE statements, constants, cursors etc. The use of packages promotes re-use of code. Packages are composed of the package specification and an optional package body. The specification is the interface to the application; it declares the types, variables, constants, exceptions, cursors, and subprograms available. The body fully defines cursors and subprograms, and so implements the specification. Two advantages of packages are: Modular approach, encapsulation/hiding of business logic, security, performance improvement, re-usability. They support object-oriented programming features like function overloading and encapsulation. Using package variables one can declare session level (scoped) variables, since variables declared in the package specification have a session scope.
Views: 5820 radhikaravikumar
SQL Server join :- Inner join,Left join,Right join and full outer join
 
08:11
For more such videos visit http://www.questpond.com For more such videos subscribe https://www.youtube.com/questpondvideos?sub_confirmation=1 Also watch Learn Sql Queries in 1 hour :- https://www.youtube.com/watch?v=uGlfP9o7kmY See our other Step by Step video series below :- Learn Angular tutorial for beginners https://tinyurl.com/ycd9j895 Learn MVC Core step by step :- http://tinyurl.com/y9jt3wkv Learn MSBI Step by Step in 32 hours:- https://goo.gl/TTpFZN Learn Xamarin Mobile Programming Step by Step :- https://goo.gl/WDVFuy Learn Design Pattern Step by Step in 8 hours:- https://goo.gl/eJdn0m Learn C# Step by Step in 100 hours :- https://goo.gl/FNlqn3 Learn Data structures & algorithm in 8 hours :-https://tinyurl.com/ybx29c5s Learn SQL Server Step by Step in 16 hours:- http://tinyurl.com/ja4zmwu Learn Javascript in 2 hours :- http://tinyurl.com/zkljbdl Learn SharePoint Step by Step in 8 hours:- https://goo.gl/XQKHeP Learn TypeScript in 45 Minutes :- https://goo.gl/oRkawI Learn webpack in 50 minutes:- https://goo.gl/ab7VJi Learn Visual Studio code in 10 steps for beginners:- https://tinyurl.com/lwgv8r8 Learn Tableau step by step :- https://tinyurl.com/kh6ojyo Preparing for C# / .NET interviews start here http://www.youtube.com/watch?v=gaDn-sVLj8Q In this video we will try to understand four important concepts Inner joins,Left join,Right join and full outer joins. We are also distributing a 100 page Ebook ".Sql Server Interview Question and Answers". If you want this ebook please share this video in your facebook/twitter/linkedin account and email us on [email protected] with the shared link and we will email you the PDF.
Views: 878802 Questpond
MySQL Stored Procedure 4 - Creating Variables within SELECT
 
04:47
Let's learn how to create variables within a SELECT statement! This is great for when we want to have a result table showing the result of our variable. Keep in mind that you must put a colon before the equals in the assignment operator. If you forget to do this, MySQL is going to think you are doing a comparison! ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ SUPPORT ME!: http://www.patreon.com/calebcurry Subscribe to my newsletter: http://bit.ly/JoinCCNewsletter Donate!: http://bit.ly/DonateCTVM2. ~~~~~~~~~~~~~~~Additional Links~~~~~~~~~~~~~~~ More content: http://CalebCurry.com Facebook: http://www.facebook.com/CalebTheVideoMaker Google+: https://plus.google.com/+CalebTheVideoMaker2 Twitter: http://twitter.com/calebCurry Amazing Web Hosting - http://bit.ly/ccbluehost (The best web hosting for a cheap price!)
Views: 18308 Caleb Curry
Get Table Names with Column Names and Data Types in SQL Server - SQL Server Tutorial
 
05:47
In this video ,we will generate the script that will return us all the table names with column names and data type of those columns. We often need this information. Let's say we are going to prepare mapping document for load the data from Source Database to Destination database. We can get the list of all tables with column names and data types from Source Database and Destination Database and then paste in Excel and map the required input columns to output columns for ETL Process. Blog link with scripts used in video http://sqlage.blogspot.com/2015/02/get-all-tables-with-column-names-and.html
Views: 49490 TechBrothersIT
Improve SQL Server performance using profiler and tuning advisor
 
13:12
For more such videos visit http://www.questpond.com See our other Step by Step video series below :- Learn SQL Server Step by Step in 16 hours:- http://tinyurl.com/ja4zmwu Learn MSBI Step by Step in 32 hours:- https://goo.gl/TTpFZN Learn Angular tutorial for beginners https://tinyurl.com/ycd9j895 Learn MVC Core step by step :- http://tinyurl.com/y9jt3wkv Learn MVC 5 Step by Step in 16 hours:- https://goo.gl/dmdakg Learn Xamarin Mobile Programming Step by Step :- https://goo.gl/WDVFuy Learn Design Pattern Step by Step in 8 hours:- https://goo.gl/eJdn0m Learn C# Step by Step in 100 hours :- https://goo.gl/FNlqn3 Learn Data structures & algorithm in 8 hours :-https://tinyurl.com/ybx29c5s Learn Javascript in 2 hours :- http://tinyurl.com/zkljbdl Learn SharePoint Step by Step in 8 hours:- https://goo.gl/XQKHeP Learn TypeScript in 45 Minutes :- https://goo.gl/oRkawI Learn webpack in 50 minutes:- https://goo.gl/ab7VJi Learn Visual Studio code in 10 steps for beginners:- https://tinyurl.com/lwgv8r8 Learn Tableau step by step :- https://tinyurl.com/kh6ojyo This video first starts with a simple explanation of what is a SQL server profiler and performance tuning wizard. After completing the basic concepts it then demonstrates a simple example using two tables by capturing the work load and understanding the recommendations given by the tuning wizard. For more such videos visit http://www.questpond.com We are also distributing a 100 page Ebook "Sql Server Interview Questions". If you want this ebook please share this video in your facebook/twitter/linkedin account and email us on [email protected] with the shared link and we will email you the PDF.
Tutorial 42 - Creating Functions in PostgreSQL
 
12:41
Learn about Creating Functions in PostgreSQL
Views: 25143 Programming Guru
aula 2420 PL SQL Select Into Procedure IN
 
07:21
Aqui mais de 2.700 vídeo aulas no site www.informaticon.com.br.Desenvolvido pelo professor Neri Aldoir Neitzke, pós-graduado em Desenvolvimento para Web, professor e coordenador do curso de Sistemas de Informação da Universidade Ulbra e proprietário da Informaticon, email [email protected] 0xx543329-5400 Carazinho-RS Brasil Obs.: No youtube.com, as video aulas não estão nítidas e apenas a metade das vídeo aulas estão aqui. Caso queira, posso enviar um dvd com centenas de vídeo aulas originais com alta qualidade, e você ainda vai receber todo o código fonte.
Views: 3477 Neri Neitzke
SQL Script SAP HANA Stored Procedure
 
09:18
made with ezvid, free download at http://ezvid.com
Views: 35783 Somnath Ray
Dynamic Power BI reports using Parameters
 
12:21
In this video, Patrick shows you how you can use a parameter, within a Power BI report, to dynamically change the data in a report. This uses M Functions within Power Query and a second source that has the key values to pull. Then using a gateway to enable refresh. LET'S CONNECT! Guy in a Cube -- https://guyinacube.com -- http://twitter.com/guyinacube -- http://www.facebook.com/guyinacube -- Snapchat - guyinacube -- https://www.instagram.com/guyinacube/ ***Gear*** Check out my Tools page - https://guyinacube.com/tools/
Views: 93412 Guy in a Cube
Part 1   How to find nth highest salary in sql
 
11:45
Link for all dot net and sql server video tutorial playlists http://www.youtube.com/user/kudvenkat/playlists Link for slides, code samples and text version of the video http://csharp-video-tutorials.blogspot.com/2014/05/part-1-how-to-find-nth-highest-salary_17.html This is a very common SQL Server Interview Question. There are several ways of finding the nth highest salary. By the end of this video, we will be able to answer all the following questions as well. How to find nth highest salary in SQL Server using a Sub-Query How to find nth highest salary in SQL Server using a CTE How to find the 2nd, 3rd or 15th highest salary Let's use the following Employees table for this demo Use the following script to create Employees table Create table Employees ( ID int primary key identity, FirstName nvarchar(50), LastName nvarchar(50), Gender nvarchar(50), Salary int ) GO Insert into Employees values ('Ben', 'Hoskins', 'Male', 70000) Insert into Employees values ('Mark', 'Hastings', 'Male', 60000) Insert into Employees values ('Steve', 'Pound', 'Male', 45000) Insert into Employees values ('Ben', 'Hoskins', 'Male', 70000) Insert into Employees values ('Philip', 'Hastings', 'Male', 45000) Insert into Employees values ('Mary', 'Lambeth', 'Female', 30000) Insert into Employees values ('Valarie', 'Vikings', 'Female', 35000) Insert into Employees values ('John', 'Stanmore', 'Male', 80000) GO To find the highest salary it is straight forward. We can simply use the Max() function as shown below. Select Max(Salary) from Employees To get the second highest salary use a sub query along with Max() function as shown below. Select Max(Salary) from Employees where Salary [ (Select Max(Salary) from Employees) To find nth highest salary using Sub-Query SELECT TOP 1 SALARY FROM ( SELECT DISTINCT TOP N SALARY FROM EMPLOYEES ORDER BY SALARY DESC ) RESULT ORDER BY SALARY To find nth highest salary using CTE WITH RESULT AS ( SELECT SALARY, DENSE_RANK() OVER (ORDER BY SALARY DESC) AS DENSERANK FROM EMPLOYEES ) SELECT TOP 1 SALARY FROM RESULT WHERE DENSERANK = N To find 2nd highest salary we can use any of the above queries. Simple replace N with 2. Similarly, to find 3rd highest salary, simple replace N with 3. Please Note: On many of the websites, you may have seen that, the following query can be used to get the nth highest salary. The below query will only work if there are no duplicates. WITH RESULT AS ( SELECT SALARY, ROW_NUMBER() OVER (ORDER BY SALARY DESC) AS ROWNUMBER FROM EMPLOYEES ) SELECT SALARY FROM RESULT WHERE ROWNUMBER = 3
Views: 943362 kudvenkat
Temp tables in dynamic sql
 
04:36
Text version of the video http://csharp-video-tutorials.blogspot.com/2017/05/temp-tables-in-dynamic-sql.html Slides http://csharp-video-tutorials.blogspot.com/2017/05/temp-tables-in-dynamic-sql_9.html All SQL Server Text Articles http://csharp-video-tutorials.blogspot.com/p/free-sql-server-video-tutorials-for.html All SQL Server Slides http://csharp-video-tutorials.blogspot.com/p/sql-server.html All SQL Server Tutorial Videos https://www.youtube.com/playlist?list=PL08903FB7ACA1C2FB All Dot Net and SQL Server Tutorials in English https://www.youtube.com/user/kudvenkat/playlists?view=1&sort=dd All Dot Net and SQL Server Tutorials in Arabic https://www.youtube.com/c/KudvenkatArabic/playlists In this video we will discuss the implications of creating temp tables in dynamic sql Temp tables created by dynamic SQL are not accessible from the calling procedure. They are dropped when the dynamic SQL block in the stored procedure completes execution. Let us understand this with an example. Notice in the example below, all the following 3 operations are in the block of dynamic sql code. 1. Creating the Temp Table 2. Populating the Temp Table 3. Select query on the Temp Table Create procedure spTempTableInDynamicSQL as Begin Declare @sql nvarchar(max) Set @sql = 'Create Table #Test(Id int) insert into #Test values (101) Select * from #Test' Execute sp_executesql @sql End So when we execute the above procedure we are able to access data from the Temp Table. Execute spTempTableInDynamicSQL Now, let's move the SELECT statement outside of the dynamic sql code block as shown below and ALTER the stored procedure. Alter procedure spTempTableInDynamicSQL as Begin Declare @sql nvarchar(max) Set @sql = 'Create Table #Test(Id int) insert into #Test values (101)' Execute sp_executesql @sql Select * from #Test End At this point, execute the stored procedure. Notice, we get the error - Invalid object name '#Test'. This is because temp tables created by dynamic SQL are not accessible from the calling procedure. They are dropped when the dynamic SQL block in the stored procedure completes execution. Execute spTempTableInDynamicSQL On the other hand, dynamic SQL block can access temp tables created by the calling stored procedure. Let's prove this by modifying the stored procedure as shown below. Alter procedure spTempTableInDynamicSQL as Begin Create Table #Test(Id int) insert into #Test values (101) Declare @sql nvarchar(max) Set @sql = 'Select * from #Test' Execute sp_executesql @sql End At this point, execute the stored procedure. Notice that we are able to access the temp table, which proves that dynamic SQL block can access temp tables created by the calling stored procedure. Execute spTempTableInDynamicSQL Summary 1. Temp tables created by dynamic SQL are not accessible from the calling procedure. 2. They are dropped when the dynamic SQL block in the stored procedure completes execution. 3. On the other hand, dynamic SQL block can access temp tables created by the calling stored procedure
Views: 25913 kudvenkat
Nested If Then Else statement in pl/sql
 
04:08
Nested If Then Else statement in pl/sql Share, Support, Subscribe!!! Subscribe: https://www.youtube.com/channel/UC1P3... Youtube: https://www.youtube.com/channel/UC1P3... Twitter: https://twitter.com/csetuts4you Facebook: https://www.facebook.com/csetuts4you/ Instagram: https://www.instagram.com/csetuts4you/ Google Plus: https://plus.google.com/u/0/+csetuts4you About : This is my youtube channel its name is Technical Notes that means computer science and engineering tutorials for you. the aim of my channel is to provide you to the best programming language concept and other subjective concept and different technology concept. so friends please visit to my Technical Notes youtube channel and subscribe it. this is fully free channel. and please share this link to all yours friends to get these benefits.., New Video is Posted Everyday :)
Views: 36 Technical Notes
Sybase Stored Procedure , If Else , Cursor and Hashed table ( TUTORIALS )
 
07:02
SYBASE TUTORIALS -------------------------------------------------- Follow MEGABONE --------------------- Youtube(Subscribe) -- http://goo.gl/Dr4O9W Vimeo -- https://vimeo.com/megabone Instagram -- http://instagram.com/bestof_souvik Twitter -- https://twitter.com/souvikdutta21 Facebook -- https://www.facebook.com/Youtube.MEGABONE Soundcloud -- https://soundcloud.com/megabone-youtube Google + -- https://plus.google.com/u/0/+MEGABONE/posts Twitch -- http://www.twitch.tv/megabone_play Blogspot -- http://articlesmustread.blogspot.in/?view=timeslide Snapchat (Username) -- bestof_souvik Tumblr -- realmegabone.tumblr.com Vine -- souvikdu
Views: 4139 MEGA BONE
how to store procedure select in mysql - student at combodia
 
03:18
how to store procedure select in MySQL how to call store procedure select in mysql
Views: 210 khjava
How to Join 3 tables in 1 SQL query
 
04:59
Get your first month on the Joes 2 Pros Academy for just $1 with code YOUTUBE1. Visit http://www.joes2pros.com Offer expires July 1, 2015 From the newly released 2 Disc DVD set (SQL Queries Joes 2 Pros Vol2) this video shows how to join 3 tables in 1 query.
Views: 259383 Joes2Pros SQL Trainings
List all tables in a sql server database using a query  Part 65
 
05:38
Text version of the video http://csharp-video-tutorials.blogspot.com/2013/06/part-65-list-all-tables-in-sql-server.html Slides http://csharp-video-tutorials.blogspot.com/2013/09/part-65-list-all-tables-in-sql-server.html All SQL Server Text Articles http://csharp-video-tutorials.blogspot.com/p/free-sql-server-video-tutorials-for.html All SQL Server Slides http://csharp-video-tutorials.blogspot.com/p/sql-server.html All Dot Net and SQL Server Tutorials in English https://www.youtube.com/user/kudvenkat/playlists?view=1&sort=dd All Dot Net and SQL Server Tutorials in Arabic https://www.youtube.com/c/KudvenkatArabic/playlists In this video we will discuss, writing a transact sql query to list all the tables in a sql server database. This is a very common sql server interview question. Object explorer with in sql server management studio can be used to get the list of tables in a specific database. However, if we have to write a query to achieve the same, there are 3 system views that we can use. 1. SYSOBJECTS - Supported in SQL Server version 2000, 2005 & 2008 2. SYS.TABLES - Supported in SQL Server version 2005 & 2008 3. INFORMATION_SCHEMA.TABLES - Supported in SQL Server version 2005 & 2008 -- Gets the list of tables only Select * from SYSOBJECTS where XTYPE='U' -- Gets the list of tables only Select * from SYS.TABLES -- Gets the list of tables and views select * from INFORMATION_SCHEMA.TABLES To get the list of different object types (XTYPE) in a database Select Distinct XTYPE from SYSOBJECTS Executing the above query on my SAMPLE database returned the following values for XTYPE column from SYSOBJECTS IT - Internal table P - Stored procedure PK - PRIMARY KEY constraint S - System table SQ - Service queue U - User table V - View Please check the following MSDN link for all possible XTYPE column values and what they represent. http://msdn.microsoft.com/en-us/library/ms177596.aspx
Views: 92355 kudvenkat
PL/SQL: Collections Part-1
 
06:13
In this tutorial, you'll learn the introduction to collections. PL/SQL (Procedural Language/Structured Query Language) is Oracle Corporation's procedural extension for SQL and the Oracle relational database. PL/SQL is available in Oracle Database (since version 7), TimesTen in-memory database (since version 11.2.1), and IBM DB2 (since version 9.7).[1] Oracle Corporation usually extends PL/SQL functionality with each successive release of the Oracle Database. PL/SQL includes procedural language elements such as conditions and loops. It allows declaration of constants and variables, procedures and functions, types and variables of those types, and triggers. It can handle exceptions (runtime errors). Arrays are supported involving the use of PL/SQL collections. Implementations from version 8 of Oracle Database onwards have included features associated with object-orientation. One can create PL/SQL units such as procedures, functions, packages, types, and triggers, which are stored in the database for reuse by applications that use any of the Oracle Database programmatic interfaces. PL/SQL works analogously to the embedded procedural languages associated with other relational databases. For example, Sybase ASE and Microsoft SQL Server have Transact-SQL, PostgreSQL has PL/pgSQL (which emulates PL/SQL to an extent), and IBM DB2 includes SQL Procedural Language,[2] which conforms to the ISO SQL’s SQL/PSM standard. The designers of PL/SQL modeled its syntax on that of Ada. Both Ada and PL/SQL have Pascal as a common ancestor, and so PL/SQL also resembles Pascal in several aspects. However, the structure of a PL/SQL package does not resemble the basic Object Pascal program structure as implemented by a Borland Delphi or Free Pascal unit. Programmers can define public and private global data-types, constants and static variables in a PL/SQL package.[3] PL/SQL also allows for the definition of classes and instantiating these as objects in PL/SQL code. This resembles usage in object-oriented programming languages like Object Pascal, C++ and Java. PL/SQL refers to a class as an "Abstract Data Type" (ADT) or "User Defined Type" (UDT), and defines it as an Oracle SQL data-type as opposed to a PL/SQL user-defined type, allowing its use in both the Oracle SQL Engine and the Oracle PL/SQL engine. The constructor and methods of an Abstract Data Type are written in PL/SQL. The resulting Abstract Data Type can operate as an object class in PL/SQL. Such objects can also persist as column values in Oracle database tables. PL/SQL is fundamentally distinct from Transact-SQL, despite superficial similarities. Porting code from one to the other usually involves non-trivial work, not only due to the differences in the feature sets of the two languages,[4] but also due to the very significant differences in the way Oracle and SQL Server deal with concurrency and locking. There are software tools available that claim to facilitate porting including Oracle Translation Scratch Editor,[5] CEITON MSSQL/Oracle Compiler [6] and SwisSQL.[7] The StepSqlite product is a PL/SQL compiler for the popular small database SQLite. PL/SQL Program Unit A PL/SQL program unit is one of the following: PL/SQL anonymous block, procedure, function, package specification, package body, trigger, type specification, type body, library. Program units are the PL/SQL source code that is compiled, developed and ultimately executed on the database. The basic unit of a PL/SQL source program is the block, which groups together related declarations and statements. A PL/SQL block is defined by the keywords DECLARE, BEGIN, EXCEPTION, and END. These keywords divide the block into a declarative part, an executable part, and an exception-handling part. The declaration section is optional and may be used to define and initialize constants and variables. If a variable is not initialized then it defaults to NULL value. The optional exception-handling part is used to handle run time errors. Only the executable part is required. A block can have a label. Package Packages are groups of conceptually linked functions, procedures, variables, PL/SQL table and record TYPE statements, constants, cursors etc. The use of packages promotes re-use of code. Packages are composed of the package specification and an optional package body. The specification is the interface to the application; it declares the types, variables, constants, exceptions, cursors, and subprograms available. The body fully defines cursors and subprograms, and so implements the specification. Two advantages of packages are: Modular approach, encapsulation/hiding of business logic, security, performance improvement, re-usability. They support object-oriented programming features like function overloading and encapsulation. Using package variables one can declare session level (scoped) variables, since variables declared in the package specification have a session scope.
Views: 15125 radhikaravikumar
DDL Triggers in sql server
 
12:31
ddl trigger example ddl trigger example in sql server ddl triggers can be used to In this video we will discuss DDL Triggers in sql server. In SQL Server there are 4 types of triggers 1. DML Triggers - Data Manipulation Language. Discussed in Parts 43 to 47 of SQL Server Tutorial. 2. DDL Triggers - Data Definition Language 3. CLR triggers - Common Language Runtime 4. Logon triggers What are DDL triggers DDL triggers fire in response to DDL events - CREATE, ALTER, and DROP (Table, Function, Index, Stored Procedure etc...). For the list of all DDL events please visit https://msdn.microsoft.com/en-us/library/bb522542.aspx Certain system stored procedures that perform DDL-like operations can also fire DDL triggers. Example - sp_rename system stored procedure What is the use of DDL triggers If you want to execute some code in response to a specific DDL event To prevent certain changes to your database schema Audit the changes that the users are making to the database structure Syntax for creating DDL trigger CREATE TRIGGER [Trigger_Name] ON [Scope (Server|Database)] FOR [EventType1, EventType2, EventType3, ...], AS BEGIN -- Trigger Body END DDL triggers scope : DDL triggers can be created in a specific database or at the server level. The following trigger will fire in response to CREATE_TABLE DDL event. Use SampleDB Go CREATE TRIGGER trMyFirstTrigger ON Database FOR CREATE_TABLE AS BEGIN Print 'New table created' END To check if the trigger has been created 1. In the Object Explorer window, expand the SampleDB database by clicking on the plus symbol. 2. Expand Programmability folder 3. Expand Database Triggers folder Please note : If you can't find the trigger that you just created, make sure to refresh the Database Triggers folder. When you execute the following code to create the table, the trigger will automatically fire and will print the message - New table created Create Table Test (Id int) The above trigger will be fired only for one DDL event CREATE_TABLE. If you want this trigger to be fired for multiple events, for example when you alter or drop a table, then separate the events using a comma as shown below. ALTER TRIGGER trMyFirstTrigger ON Database FOR CREATE_TABLE, ALTER_TABLE, DROP_TABLE AS BEGIN Print 'A table has just been created, modified or deleted' END Now if you create, alter or drop a table, the trigger will fire automatically and you will get the message - A table has just been created, modified or deleted. The 2 DDL triggers above execute some code in response to DDL events (CREATE_TABLE, ALTER_TABLE, DROP_TABLE). Now let us look at an example of how to prevent users from creating, altering or dropping tables. To do this modify the trigger as shown below. ALTER TRIGGER trMyFirstTrigger ON Database FOR CREATE_TABLE, ALTER_TABLE, DROP_TABLE AS BEGIN Rollback Print 'You cannot create, alter or drop a table' END To be able to create, alter or drop a table, you either have to disable or delete the trigger. To disable trigger 1. Right click on the trigger in object explorer and select "Disable" from the context menu 2. You can also disable the trigger using the following T-SQL command DISABLE TRIGGER trMyFirstTrigger ON DATABASE To enable trigger 1. Right click on the trigger in object explorer and select "Enable" from the context menu 2. You can also enable the trigger using the following T-SQL command ENABLE TRIGGER trMyFirstTrigger ON DATABASE To drop trigger 1. Right click on the trigger in object explorer and select "Delete" from the context menu 2. You can also drop the trigger using the following T-SQL command DROP TRIGGER trMyFirstTrigger ON DATABASE Certain system stored procedures that perform DDL-like operations can also fire DDL triggers. The following trigger will be fired when ever you rename a database object using sp_rename system stored procedure. CREATE TRIGGER trRenameTable ON DATABASE FOR RENAME AS BEGIN Print 'You just renamed something' END The following code changes the name of the TestTable to NewTestTable. When this code is executed, it will fire the trigger trRenameTable sp_rename 'TestTable', 'NewTestTable' The following code changes the name of the Id column in NewTestTable to NewId. When this code is executed, it will fire the trigger trRenameTable sp_rename 'NewTestTable.Id' , 'NewId', 'column' Text version of the video http://csharp-video-tutorials.blogspot.com/2015/09/ddl-triggers-in-sql-server.html Slides http://csharp-video-tutorials.blogspot.com/2015/09/ddl-triggers-in-sql-server_9.html All SQL Server Text Articles http://csharp-video-tutorials.blogspot.com/p/free-sql-server-video-tutorials-for.html All SQL Server Slides http://csharp-video-tutorials.blogspot.com/p/sql-server.html All Dot Net and SQL Server Tutorials in English https://www.youtube.com/user/kudvenkat/playlists?view=1&sort=dd All Dot Net and SQL Server Tutorials in Arabic https://www.youtube.com/c/KudvenkatArabic/playlists
Views: 62435 kudvenkat
Stored Procedure: Looping, Counters, and Inserts
 
03:53
I continue my lesson on Looping. Now, let's use a counter and an insert statement to do something truly amazing with this Stored Procedure. For more education opportunities, visit CoffingDW.com The Query Tool being used in this film is the Nexus Query Chameleon. Read more about it here: https://www.coffingdw.com/software/nexus
Prevent sql injection with dynamic sql
 
10:28
Text version of the video http://csharp-video-tutorials.blogspot.com/2017/04/prevent-sql-injection-with-dynamic-sql.html Slides http://csharp-video-tutorials.blogspot.com/2017/04/prevent-sql-injection-with-dynamic-sql_5.html All SQL Server Text Articles http://csharp-video-tutorials.blogspot.com/p/free-sql-server-video-tutorials-for.html All SQL Server Slides http://csharp-video-tutorials.blogspot.com/p/sql-server.html All SQL Server Tutorial Videos https://www.youtube.com/playlist?list=PL08903FB7ACA1C2FB All Dot Net and SQL Server Tutorials in English https://www.youtube.com/user/kudvenkat/playlists?view=1&sort=dd All Dot Net and SQL Server Tutorials in Arabic https://www.youtube.com/c/KudvenkatArabic/playlists In this video we will discuss, how to prevent SQL injection when using dynamic SQL. In Part 140, we have implemented "Search Page" using dynamic SQL. Since we have used parameters to build our dynamic SQL statements, it is not prone to SQL Injection attack. This is an example of good dynamic SQL implementation. I have seen lot of software developers, not just the beginners but even experienced developers, buidling their dynamic sql queries by concatenating strings instead of using parameters without realizing that they are opening the doors for SQL Injection. Please check my blog at the following link for code used in the demo. http://csharp-video-tutorials.blogspot.com/2017/04/prevent-sql-injection-with-dynamic-sql.html Since we are concatenating the user input values to build the dynamic sql statement, the end user can very easily inject sql. Imagine, what happens for example, if the user enters the following in the "Firstname" textbox. ' Drop database SalesDB -- With the above SQL injected into the "Firstname" textbox, if you click the "Search" button, the following is the query which is sent to SQL server. This will drop the SalesDB. Select * from Employees where 1 = 1 AND FirstName = '' Drop database SalesDB --' On the other hand, if you use parameters to build your dynamic SQL statements, SQL Injection is not an issue. The following input in the "Firstname" textbox, would not drop the SalesDB database. ' Drop database SalesDB -- The text the user has provided in the "Firstname" textbox is treated as the value for @Firstname parameter. The following is the query that is generated and executed. exec sp_executesql N'Select * from Employees where 1 = 1 AND [email protected]',N'@FirstName nvarchar(26)',@FirstName=N''' Drop database SalesDB --' We don't have this problem of sql injection if we are using stored procedures. "SearchPageWithoutDynamicSQL.aspx" is using the stored procedure "spSearchEmployees" instead of dynamic SQL. The same input in the "Firstname" texbox on this page, would generate the following. Notice, whatever text we typed in the "Firstname" textbox is treated as the value for @FirstName parameter. exec spSearchEmployees @FirstName=N''' Drop database SalesDB --' An important point to keep in mind here is that if you have dynamic SQL in your stored procedure, and you are concatenating strings in that stored procedure to build your dynamic sql statements instead of using parameters, it is still prone to SQL injection. If this is not clear at the moment don't worry, we will discuss an example of this in out next video. So in summary, while dynamic sql provides great flexibility when implementing complicated logic with lot of permutations and combinations, if not properly implemented it may open doors for sql injection. Always use parameters to build dynamic sql statements, instead of concatenating user input values. Another benefit of using parameters to build dynamic sql statements is that it allows cached query plans to be reused, which greatly increases the performance. We will discuss an example of this in our upcoming videos.
Views: 20949 kudvenkat