SQL SERVER – Simple Example of Cursor – Sample Cursor Part 2

I have recently received email that I should update SQL SERVER – Simple Example of Cursor with example of AdventureWorks database.

Simple Example of Cursor using AdventureWorks Database is listed here.

USE AdventureWorks
GO
DECLARE @ProductID INT
DECLARE
@getProductID CURSOR
SET
@getProductID = CURSOR FOR
SELECT
ProductID
FROM Production.Product
OPEN @getProductID
FETCH NEXT
FROM @getProductID INTO @ProductID
WHILE @@FETCH_STATUS = 0
BEGIN
PRINT
@ProductID
FETCH NEXT
FROM @getProductID INTO @ProductID
END
CLOSE
@getProductID
DEALLOCATE @getProductID
GO


Reference : Pinal Dave (http://blog.SQLAuthority.com)

About these ads

SQLAuthority News – Best Articles on SQLAuthority.com

SQL SERVER – Cursor to Kill All Process in Database

SQL SERVER – Find Stored Procedure Related to Table in Database – Search in All Stored procedure

SQL SERVER – Shrinking Truncate Log File – Log Full

SQL SERVER – Simple Example of Cursor

SQL SERVER – UDF – Function to Convert Text String to Title Case – Proper Case

SQL SERVER – Restore Database Backup using SQL Script (T-SQL)

SQL SERVER – T-SQL Script to find the CD key from Registry

SQL SERVER – Delete Duplicate Records – Rows

SQL SERVER – QUOTED_IDENTIFIER ON/OFF and ANSI_NULL ON/OFF Explanation

SQL SERVER – Union vs. Union All – Which is better for performance?

SQL SERVER – DBCC RESEED Table Identity Value – Reset Table Identity

SQL SERVER – @@IDENTITY vs SCOPE_IDENTITY() vs IDENT_CURRENT – Retrieve Last Inserted Identity of Record

SQL SERVER – Difference between DISTINCT and GROUP BY – Distinct vs Group By

SQL SERVER – Index Seek Vs. Index Scan (Table Scan)

SQL SERVER – TempDB is Full. Move TempDB from one drive to another drive

SQL SERVER – T-SQL Paging Query Technique Comparison – SQL 2000 vs SQL 2005

SQL SERVER – Performance Optimization of SQL Query and FileGroups

SQL SERVER – Search Text Field – CHARINDEX vs PATINDEX

SQL SERVER – 2005 Explanation of TRY…CATCH and ERROR Handling

SQL SERVER – Script to find SQL Server on Network

SQL SERVER – Stored Procedures Advantages and Best Advantage

SQL SERVER – CASE Statement/Expression Examples and Explanation

SQL SERVER – Raid Configuration – RAID 10

SQL SERVER – Understanding new Index Type of SQL Server 2005 Included Column Index along with Clustered Index and Non-clustered Index

SQL SERVER – Query to Find Seed Values, Increment Values and Current Identity Column value of the table

SQL SERVER – Six Properties of Relational Tables

SQL SERVER – TRIM() Function – UDF TRIM()

SQL SERVER – Difference between Unique Index vs Unique Constraint

SQL SERVER – 2005 Locking Hints and Examples

SQL SERVER – Good, Better and Best Programming Techniques

SQL SERVER – Random Number Generator Script – SQL Query

SQL SERVER – 2005 TOP Improvements/Enhancements

SQL SERVER – 2005/2000 Examples and Explanation for GOTO

SQL SERVER – Explanation SQL Commando GO

SQL SERVER – 2005 – List all the database

SQL SERVER – UDF – Function to Parse AlphaNumeric Characters from String

SQL SERVER – Disable Index – Enable Index – ALTER Index

SQL SERVER – 2005 – SSMS Change T-SQL Batch Separator

SQL SERVER – SQL Code Formatter Tools

SQL SERVER – 2005 Comparison EXCEPT operator vs. NOT IN

SQL SERVER – 2005 NorthWind Database or AdventureWorks Database – Samples Databases

SQL SERVER – 2005 Find Table without Clustered Index – Find Table with no Primary Key

SQL SERVER – 2005 Limiting Result Sets by Using TABLESAMPLE – Examples

SQL SERVER – 2005 Change Database Compatible Level – Backward Compatibility

SQL SERVER – 2005 Constraint on VARCHAR(MAX) Field To Limit It Certain Length

SQL SERVER Database Coding Standards and Guidelines Complete List Download

SQL SERVER – Insert Multiple Records Using One Insert Statement – Use of UNION ALL

SQL SERVER – Retrieve – Select Only Date Part From DateTime – Best Practice

SQL SERVER – 2005 T-SQL Paging Query Technique Comparison (OVER and ROW_NUMBER()) – CTE vs. Derived Table

SQL SERVER – Easy Sequence of SELECT FROM JOIN WHERE GROUP BY HAVING ORDER BY

SQL SERVER – 2005 – UDF – User Defined Function to Strip HTML – Parse HTML – No Regular Expression

SQL SERVER – Retrieve Current Date Time in SQL Server CURRENT_TIMESTAMP, GETDATE(), {fn NOW()}

SQL SERVER – Explanation and Comparison of NULLIF and ISNULL

SQL SERVER – 2005 Row Overflow Data Explanation

SQL SERVER – Comparison Index Fragmentation, Index De-Fragmentation, Index Rebuild – SQL SERVER 2000 and SQL SERVER 2005

SQL SERVER – Comparison : Similarity and Difference #TempTable vs @TempVariable

SQL SERVER – Definition, Comparison and Difference between HAVING and WHERE Clause

SQL SERVER – 2005 Best Practices Analyzer Tutorial – Sample Example

SQL SERVER – 2005 – List All Stored Procedure Modified in Last N Days

SQL SERVER – Count Duplicate Records – Rows

SQL SERVER – CASE Statement in ORDER BY Clause – ORDER BY using Variable

SQL SERVER – Restore Database Without or With Backup – Everything About Restore and Backup

SQL SERVER – UDF – Function to Get Previous And Next Work Day – Exclude Saturday and Sunday

SQL SERVER – One Thing All DBA Must Know

SQL SERVER – 2005 – List Tables in Database Without Primary Key

SQL SERVER – 2005 – Find Stored Procedure Create Date and Modified Date

SQL SERVER – UDF – Validate Integer Function

SQL SERVER – What is SQL? How to pronounce SQL?

SQL SERVER – 2005 – Difference and Similarity Between NEWSEQUENTIALID() and NEWID()

SQL SERVER – 2005 – Explanation and Script for Online Index Operations – Create, Rebuild, Drop

SQL SERVER – Find Last Day of Any Month – Current Previous Next

SQL SERVER – T-SQL Script to Insert Carriage Return and New Line Feed in Code

SQL SERVER – 2005 – T-SQL Script to Attach and Detach Database

SQL SERVER – Actual Execution Plan vs. Estimated Execution Plan

SQL SERVER – 2005 – Search Stored Procedure Code – Search Stored Procedure Text

SQL SERVER – 2005 – Find Tables With Foreign Key Constraint in Database

SQL SERVER – 2005 – Find Tables With Primary Key Constraint in Database

SQL SERVER – 2005 – Introduction and Explanation to sqlcmd

SQL SERVER – UDF – User Defined Function – Get Number of Days in Month

SQL SERVER – 2005 – Start Stop Restart SQL Server From Command Prompt

SQL SERVER – 2005 – List All The Constraint of Database – Find Primary Key and Foreign Key Constraint in Database

SQL SERVER – UDF – Validate Positive Integer Function – Validate Natural Integer Function

SQL SERVER – Effect of TRANSACTION on Local Variable – After ROLLBACK and After COMMIT

SQL SERVER – 2005 – OUTPUT Clause Example and Explanation with INSERT, UPDATE, DELETE

SQL SERVER – 2005 – Sample Example of RANKING Functions – ROW_NUMBER, RANK, DENSE_RANK, NTILE

SQL SERVER – Three T-SQL Script to Create Primary Keys on Table

Reference : Pinal Dave (http://blog.SQLAuthority.com)

SQL SERVER – Simple Example of WHILE Loop With CONTINUE and BREAK Keywords

This is question is one of those question which is very simple and most of the users get it correct, however few users find it confusing for first time. I have tried to explain the usage of simple WHILE loop in first example. BREAK keyword will exit the stop the while loop and control is moved to next statement after the while loop. CONTINUE keyword skips all the statement after its execution and control is sent to first statement of while loop. Run following examples in Query Editor and see the result. This is very easy to understand example.

1) Example of WHILE Loop
DECLARE @intFlag INT
SET
@intFlag = 1
WHILE (@intFlag <=5)
BEGIN
PRINT
@intFlag
SET @intFlag = @intFlag + 1
END
GO

ResultSet:
1
2
3
4
5

2) Example of WHILE Loop with BREAK keyword
DECLARE @intFlag INT
SET
@intFlag = 1
WHILE (@intFlag <=5)
BEGIN
PRINT
@intFlag
SET @intFlag = @intFlag + 1
IF @intFlag = 4
BREAK;
END
GO

ResultSet:
1
2
3

3) Example of WHILE Loop with CONTINUE and BREAK keywords
DECLARE @intFlag INT
SET
@intFlag = 1
WHILE (@intFlag <=5)
BEGIN
PRINT
@intFlag
SET @intFlag = @intFlag + 1
CONTINUE;
IF @intFlag = 4 -- This will never executed
BREAK;
END
GO

ResultSet:
1
2
3
4
5

Reference : Pinal Dave (http://blog.SQLAuthority.com)

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 and provides accurate information.

Sytanx:
EXEC sp_MShasdbaccess
GO

ResultSet: ( I have listed only one column)
AdventureWorks
AdventureWorksDW
master
model
msdb
MyDB
ReportServer
ReportServerTempDB
tempdb

Reference : Pinal Dave (http://blog.SQLAuthority.com)

SQL 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 string as input and executes them.
EXEC('EXEC sp_help')
GO

Another common mistakes I have seen is not using EXEC before stored procedure. It is always good practice to use EXEC before stored procedure name even though SQL Server assumes any command as stored procedure when it does not recognize the first statement. Developer learns while working with Query Editor in SSMS that EXEC is not necessary before running any stored procedure. However, consider following two test and you will see why EXEC or EXECUTE is necessary in many cases and good practice to use it.

TEST 1 : No Errors
USE AdventureWorks;
GO
----Try this first independently this will throw an error
sp_helptext 'dbo.uspPrintError'
GO
----Try this second independently this will work fine
EXEC sp_helptext 'dbo.uspPrintError'
GO

TEST 2 : EXEC prevents error
USE AdventureWorks;
GO
----Try this first independently this will throw an error
SELECT *
FROM Sales.Individual
sp_helptext 'dbo.uspPrintError'
GO
----Try this second independently this will work fine
SELECT *
FROM Sales.Individual
EXEC sp_helptext 'dbo.uspPrintError'
GO

Test 2 indicates that using EXEC or EXECUTE is good practice as it always executes the stored procedure, when not using EXEC can confuse SQL SERVER to misinterpret commands and may create errors.

Reference : Pinal Dave (http://blog.SQLAuthority.com)

SQL 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 this SQL SERVER – Find Stored Procedure Related to Table in Database – Search in All Stored procedure.

The same feature can be implemented using following script in SQL Server 2005.
USE AdventureWorks
GO
--Searching for Empoloyee table
SELECT Name
FROM sys.procedures
WHERE OBJECT_DEFINITION(OBJECT_ID) LIKE '%Employee%'
GO
--Searching for Empoloyee table and RateChangeDate column together
SELECT Name
FROM sys.procedures
WHERE OBJECT_DEFINITION(OBJECT_ID) LIKE '%Employee%'
AND OBJECT_DEFINITION(OBJECT_ID) LIKE '%RateChangeDate%'
GO

ResultSet:
Name
———————————–
uspGetEmployeeManagers
uspGetManagerEmployees
uspUpdateEmployeeHireInfo
uspUpdateEmployeeLogin
uspUpdateEmployeePersonalInfo

Name
———————————–
uspUpdateEmployeeHireInfo

Reference : Pinal Dave (http://blog.SQLAuthority.com)

SQL 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 how to create T-SQL script to copy complete database schema and all of its objects such as Stored Procedure, Functions, Triggers, Tables, Views, Constraints etc. You can review your schema, backup for reference or use it to compare with previous backup.

Step 1 : Start

Step 2 : Welcome Screen

Step 3 : Select One or Multiple Database
If Script all objects in the selected database checkbox is not selected it will give options to selected individual objects on respective screen. (e.g. Stored Procedure, Triggers and all other object will have their own screen where they can be selected)

Step 4 : Select database options

Step 5 : Select output option

Step 6 : Review Summary

Step 7 : Observe script generation process

Step 8 : Database object script generation completed in new query window

Reference : Pinal Dave (http://blog.SQLAuthority.com), All images are protected by copyright owner SQLAuthority.com