About Me

My photo
Northglenn, Colorado, United States
I'm primarily a BI Developer on the Microsoft stack. I do sometimes touch upon other Microsoft stacks ( web development, application development, and sql server development).

Friday, September 21, 2007

Quick Reminder: Parsing in T-Sql

When a set of values are sent in as a varchar and need to be parsed. Ex: '1,2,3,4'
In this example the values are sent into the stored procedure's variable @atInvNum.

DECLARE @aInvNum varchar(4000)

SET @aInvNum = LTRIM(RTRIM(@atInvNum))
DECLARE @pos INT
DECLARE @piece varchar(500)

DECLARE @tInvNum TABLE (ID int)

IF right(rtrim(@aInvNum),1) <> ','
SET @aInvNum = @aInvNum + ','

SET @pos = patindex('%,%' , @aInvNum)
WHILE @pos <> 0
BEGIN
SET @piece = left(@aInvNum, @pos - 1)

INSERT INTO @tInvNum SELECT cast(@piece as int)

SET @aInvNum = stuff(@aInvNum, 1, @pos, '')
SET @pos = patindex('%,%' , @aInvNum)
END


---------------------A better way---------------------

IF (LEN(RTRIM(LTRIM(@atTaskIDs))) > 0)
SET @atTaskIDs = ',' + @atTaskIDs + ','

CHARINDEX(',' + Convert(varchar, SRT.TASK_ID) + ',', @atTaskIDs) > 0 )

No comments: