What is SQL Injection attack and how to prevent it?

Cursors in sql server allow you to fetch a set of data, loop through each record, and modify the values as necessary; then, you can easily assign these values to variables and perform processing on these values. While loop also same as cursor to fetch set of data and process each row in sql server.

Here it’s very difficult to say which one is better either cursor or while loop because both will do same thing they fetch set of data and process each row at a time. You need to choose either cursor or while loop based on your requirements.

cursor

SET NOCOUNT ON

DECLARE ex_cursor CURSOR FOR SELECT OrderID,CustomerID FROM Orders

DECLARE @oid INT

DECLARE @cname NVARCHAR(50)

OPEN ex_cursor

FETCH NEXT FROM ex_cursor INTO @oid,@cname

WHILE @@FETCH_STATUS = 0

BEGIN

PRINT  (CAST(@oid AS VARCHAR(5)) + ‘-‘ + @cname)

FETCH NEXT FROM ex_cursor INTO @oid,@cname

END

CLOSE ex_cursor

DEALLOCATE ex_cursor

while

DECLARE @Rows INT, @oid INT

DECLARE @cname NVARCHAR(50)

SET @Rows = 1

SET @oid = 0

WHILE @Rows > 0

BEGIN

SELECT TOP 1 @oid = OrderID, @cname = CustomerID FROM Orders WHERE OrderId >= @oid

SET @Rows = @@ROWCOUNT

PRINT  (CAST(@oid AS VARCHAR(5)) + ‘-‘ + @cname)

SET @oid += 1

END

both are doing same thing and taking same time to execute our script. You need to decide either while loop or cursor based on your requirement.

Advertisements

Leave a Reply

Please log in using one of these methods to post your comment:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s