This question is one of those questions which is very simple and most of the users get it correct, however few users find it confusing for the first time. I have tried to explain the usage of simple WHILE loop in the first example. BREAK keyword will exit the stop the while loop and control is moved to the next statement after the while loop. CONTINUE keyword skips all the statement after its execution and control is sent to the first statement of while loop. Run following examples in Query Editor and see the result. This is very easy to understand example for CONTINUE and BREAK keywords.
SQL SERVER – Stored Procedure to Know Database Access Permission to Current User
Jr. DBA in my company only have access to the database which they need to use. Often they try to access database and if they do not have permission they face error. Jr. DBAs always check which database they have access using following system stored procedure. It is very reliable…
Read MoreSQL SERVER – Difference Between EXEC and EXECUTE vs EXEC() – Use EXEC/EXECUTE for SP always
What is the difference between EXEC and EXECUTE? They are the same. Both of them executes stored procedure when called as EXEC sp_help GO EXECUTE sp_help GO I have seen enough times developer getting confused between EXEC and EXEC(). EXEC command executes stored procedure where as EXEC() function takes dynamic…
Read MoreSQL SERVER – 2005 – Search Stored Procedure Code – Search Stored Procedure Text
I receive following question many times by my team members. How can I find if particular table is being used in the stored procedure? How to search in stored procedures? How can I do dependency check for objects in stored procedure without using sp_depends? I have previously wrote article about…
Read MoreSQL SERVER – 2005 – Create Script to Copy Database Schema and All The Objects – Stored Procedure, Functions, Triggers, Tables, Views, Constraints and All Other Database Objects
Update: This article is re-written with SQL Server 2008 R2 instance over here: SQL SERVER – 2008 – 2008 R2 – Create Script to Copy Database Schema and All The Objects – Data, Schema, Stored Procedure, Functions, Triggers, Tables, Views, Constraints and All Other Database Objects Following quick tutorial demonstrates…
Read MoreSQL SERVER – 2005 Comparison SP_EXECUTESQL vs EXECUTE/EXEC
Common Properties of SP_EXECUTESQL and EXECUTE/EXEC The Transact-SQL statements in the sp_executesql or EXECUTE string are not compiled into an execution plan until sp_executesql or the EXECUTE statement are executed. The strings are not parsed or checked for errors until they are executed. The names referenced in the strings are…
Read More
SQL SERVER – SET ROWCOUNT – Retrieving or Limiting the First N Records from a SQL Query
A SET ROWCOUNT statement simply limits the number of records returned to the client during a single connection. As soon as the number of rows specified is found, SQL Server stops processing the query. The syntax looks like this: