DECLARE @myTable TABLE - Usage

Prev: Log file get full, but only sometimes...need help
Next: Normalization Techniques

From: Bob on 20 Jul 2008 11:21

---

INSERT INTO @ProductTotals
SELECT item
FROM dbo.fnSplit( ... )

"Gene Berger" wrote:

> The following line works:
> SELECT * from fnSplit('test1~test2','~')
>
>
> WHY DOESN'T THE FOLLOWING WORK?
>
> DECLARE @ProductTotals TABLE
> (
> item VARCHAR(8000)
> )
> --SELECT @ProductTotals = dbo.fnSplit('test1~test2','~') << doesn't work
> --SET @ProductTotals = dbo.fnSplit('test1~test2','~') << doesn't
> work
>
> THANKS
> Gene
>
> ===============================================
> ===============================================
> ALTER FUNCTION [dbo].[fnSplit]
> (
> @sInputList VARCHAR(8000), -- List of delimited items
> @sDelimiter VARCHAR(8000) = ',' -- delimiter that separates items
> )
> RETURNS @List TABLE (item VARCHAR(8000))
> AS
> BEGIN
> /*
> SELECT * from fnSplit('test1~test2','~')
> */
> DECLARE @sItem VARCHAR(8000)
> WHILE CHARINDEX(@sDelimiter,@sInputList,0) <> 0
> BEGIN
> SELECT
> @sItem=RTRIM(LTRIM(SUBSTRING(@sInputList,1,CHARINDEX(@sDelimiter,@sInputList,0)-1))),
> @sInputList=RTRIM(LTRIM(SUBSTRING(@sInputList,CHARINDEX(@sDelimiter,@sInputList,0)+LEN(@sDelimiter),LEN(@sInputList))))
> IF LEN(@sItem) > 0
> INSERT INTO @List SELECT @sItem
> END
> IF LEN(@sInputList) > 0
> INSERT INTO @List SELECT @sInputList -- Put the last item in
> RETURN
>

From: Gene Berger on 22 Jul 2008 10:12

---

Thanks Bob !!!

"Bob" <Bob(a)discussions.microsoft.com> wrote in message
news:586C5E25-AECC-4547-BEE4-219994AB3C43(a)microsoft.com...
> INSERT INTO @ProductTotals
> SELECT item
> FROM dbo.fnSplit( ... )
>
> "Gene Berger" wrote:
>
>> The following line works:
>> SELECT * from fnSplit('test1~test2','~')
>>
>>
>> WHY DOESN'T THE FOLLOWING WORK?
>>
>> DECLARE @ProductTotals TABLE
>> (
>> item VARCHAR(8000)
>> )
>> --SELECT @ProductTotals = dbo.fnSplit('test1~test2','~') << doesn't
>> work
>> --SET @ProductTotals = dbo.fnSplit('test1~test2','~') <<
>> doesn't
>> work
>>
>> THANKS
>> Gene
>>
>> ===============================================
>> ===============================================
>> ALTER FUNCTION [dbo].[fnSplit]
>> (
>> @sInputList VARCHAR(8000), -- List of delimited items
>> @sDelimiter VARCHAR(8000) = ',' -- delimiter that separates items
>> )
>> RETURNS @List TABLE (item VARCHAR(8000))
>> AS
>> BEGIN
>> /*
>> SELECT * from fnSplit('test1~test2','~')
>> */
>> DECLARE @sItem VARCHAR(8000)
>> WHILE CHARINDEX(@sDelimiter,@sInputList,0) <> 0
>> BEGIN
>> SELECT
>> @sItem=RTRIM(LTRIM(SUBSTRING(@sInputList,1,CHARINDEX(@sDelimiter,@sInputList,0)-1))),
>> @sInputList=RTRIM(LTRIM(SUBSTRING(@sInputList,CHARINDEX(@sDelimiter,@sInputList,0)+LEN(@sDelimiter),LEN(@sInputList))))
>> IF LEN(@sItem) > 0
>> INSERT INTO @List SELECT @sItem
>> END
>> IF LEN(@sInputList) > 0
>> INSERT INTO @List SELECT @sInputList -- Put the last item in
>> RETURN
>>

 | 
Pages: 1
Prev: Log file get full, but only sometimes...need help
Next: Normalization Techniques