Search This Blog

Wednesday, November 9, 2011

SQL Server - Simple Cursor Example

A very simple cursor to add the integers from a table variable. The following query declares a table
variable with single column, inserts 3 integers into the table. Next, the cursor is declared and it
adds up the integers and prints the total.
 
--Query Begins--
--Declare table to store integers with single column
DECLARE @NumTable TABLE (Number int NULL)

--Inserts 3 integers into the table
INSERT INTO @NumTable (Number) VALUES (10)
INSERT INTO @NumTable (Number) VALUES (20)
INSERT INTO @NumTable (Number) VALUES (30)

--Declare the variables
DECLARE @Num INT
DECLARE @Total INT = 0

--Declare the cursor
DECLARE Cur CURSOR FOR
SELECT Number FROM @NumTable

--Open the cursor
OPEN Cur
FETCH NEXT FROM Cur INTO @Num

WHILE @@FETCH_STATUS = 0

BEGIN

--Add the integers
SELECT @Total = @Total + @Num

FETCH NEXT FROM Cur INTO @Num

--Close the cursor
END
CLOSE Cur
DEALLOCATE Cur

--Select the total
SELECT @Total
--Query Ends--

Tuesday, November 8, 2011

SQL Error : String or Binary data would be truncated




String or Binary data would be truncated

This is well known error. This could occur when your are trying to insert some values into
a table through stored procedure or query etc.

Solution: This error occurs when one of the column SIZE in the table is very small compared
to the value being inserted. So closely check all the column sizes, change it accordingly
and then try to insert the values.

Monday, November 7, 2011

SQL Server- Syntax to create simple table

Note the syntax to create a very simple table with two colums for sql server! We can do much more while creating tables like applying constraints, allow NULLs or NOT NULLS etc.

USE <DataBaseName>


GO

CREATE TABLE <TableName>

(

ProductID  VARCHAR(25),

ProductName VARCHAR(25)

)

GO