TSQL

TSQL: Converting/Casting String between Unicode and Non-Unicode

Categories: Database; Tagged with: SQL • TSQL; @ January 16th, 2013 12:04

I got an Error in a SSIS package:

cannot convert between unicode and non-unicode string data types.

We can use one component called ‘DataConversion’, but I think directly convert the type in SQL will be better.

SQL:

DECLARE @str_unicode NVARCHAR(32);
SET @str_unicode = NCHAR(9734);
SELECT @str_unicode

— Using CAST
SELECT CAST(@str_unicode AS VARCHAR(32))

— Using Convert
SELECT CONVERT(VARCHAR(32), @str_unicode)

Result:

TSQL: Get the length of String including/excluding trailing spaces

Categories: Database; Tagged with: SQL • SQL Server • TSQL; @ January 16th, 2013 11:53

LEN (Transact-SQL)
Returns the number of characters of the specified string expression, excluding trailing blanks.
Note: To return the number of bytes used to represent an expression, use the DATALENGTH function.

http://msdn.microsoft.com/en-us/library/ms190329(SQL.90).aspx

DATALENGTH (Transact-SQL)

Returns the number of bytes used to represent any expression.

SQLs:

DECLARE @STR VARCHAR(32);
SET @STR = ’12 ‘
SELECT LEN(@STR) — 2
SELECT DATALENGTH(@STR); –6

Using function in T-SQL

Categories: Database; Tagged with: Database • SQL • TSQL; @ November 2nd, 2012 16:49

Basic function:

ALTER FUNCTION fnGetAge(@birthDate datetime) RETURNS int AS BEGIN -- RETURN MONTH(@birthDate) RETURN DateDiff(day, @birthDate, getdate())/365.25 END


SELECT dbo.fnGetAge('09/10/1985');

Table-Valued Functions:


--INLINE Table-Valued Functions, returns a result set, like a view
CREATE FUNCTION fnGetStusByScore(@score int) RETURNS TABLE
AS    
    RETURN SELECT * FROM UserDavid WHERE score = @score
SELECT * FROM fnGetStusByScore(61)

Multi-Statement Table-Valued Functions:


-- Multi-Statement Table-Valued Functions, returns a table-type result set, the table is explicitly constructed in the script. 
ALTER FUNCTION fnGetUsers(@score int)
    RETURNS @userList TABLE
        (UserID int, UserName varchar(32))
    BEGIN
        IF @score IS NULL
            BEGIN
                INSERT INTO @userList(UserID, UserName)
                    SELECT ID, userName FROM userDavid
            END
        ELSE
            BEGIN
                INSERT INTO @userList(UserID, UserName)
                    SELECT ID, userName FROM userDavid WHERE score > @score
            END
    RETURN 
    END
SELECT * FROM fnGetUsers()

TSQL: IF ELSE, For Loop

Categories: Database; Tagged with: SQL • TSQL; @ November 2nd, 2012 16:39

IF:


CREATE PROCEDURE spTestDavid
@userID int
AS
    IF EXISTS(SELECT * FROM UserDavid WHERE ID = @userID)
        PRINT 'User EXISTS: ' + CAST(@userID AS VARCHAR(32))
    ELSE 
        PRINT 'User CANNOT found' + CAST(@userID AS VARCHAR(32))

WHILE:


CREATE PROCEDURE spTestDavid2
@COUNTER int
AS
    WHILE @COUNTER > 0 
    BEGIN 
        EXEC spTestDavid @userID = @COUNTER
        SET @COUNTER = @COUNTER - 1;
    END
EXEC spTestDavid2 @COUNTER = 30