nkewney
asked on
Setting a table variable from a function in SQL Server
Dear Experts,
I have a function in my database (code snippet below) which returns type table)
I'm trying to set this table as a variable in my stored procedure by calling something like the following:
DECLARE @tblMain table
SET @tblMain = [dbo].[fnDStringToTable](@ Original_T ext, ' ')
This doesn't seem to work and the following is returned...
Msg 156, Level 15, State 1, Procedure procTranslate, Line 19
Incorrect syntax near the keyword 'SET'.
Msg 137, Level 15, State 1, Procedure procTranslate, Line 19
Must declare the scalar variable "@tblMain".
Could anybody tell me where I'm going wrong?
Thanks in advance
Nick
I have a function in my database (code snippet below) which returns type table)
I'm trying to set this table as a variable in my stored procedure by calling something like the following:
DECLARE @tblMain table
SET @tblMain = [dbo].[fnDStringToTable](@
This doesn't seem to work and the following is returned...
Msg 156, Level 15, State 1, Procedure procTranslate, Line 19
Incorrect syntax near the keyword 'SET'.
Msg 137, Level 15, State 1, Procedure procTranslate, Line 19
Must declare the scalar variable "@tblMain".
Could anybody tell me where I'm going wrong?
Thanks in advance
Nick
ALTER FUNCTION [dbo].[fnDStringToTable]
(
@list VARCHAR(4000)
, @delimiter NCHAR(1) = ',' --Defaults to CSV
)
RETURNS
@tableList TABLE(
value VARCHAR(100)
)
AS
BEGIN
DECLARE @value NVARCHAR(100)
DECLARE @position INT
SET @list = LTRIM(RTRIM(@list))+ ','
SET @position = CHARINDEX(@delimiter, @list, 1)
IF REPLACE(@list, @delimiter, '') <> ''
BEGIN
WHILE @position > 0
BEGIN
SET @value = LTRIM(RTRIM(LEFT(@list, @position - 1)))
IF @value <> ''
BEGIN
INSERT INTO @tableList (value)
VALUES (@value)
END
SET @list = RIGHT(@list, LEN(@list) - @position)
SET @position = CHARINDEX(@delimiter, @list, 1)
END
END
RETURN
END
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.