Solved

Setting a table variable from a function in SQL Server

Posted on 2008-10-11
2
548 Views
Last Modified: 2012-05-05
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_Text, ' ')

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

Open in new window

0
Comment
Question by:nkewney
2 Comments
 
LVL 14

Accepted Solution

by:
Binuth earned 400 total points
ID: 22693424
try this
declare @tblMain as table(val varchar(100))

insert into @tblMain

select * from dbo.fnDStringToTable('1,2,3',',')

select * from @tblMain

Open in new window

0
 
LVL 59

Assisted Solution

by:Kevin Cross
Kevin Cross earned 100 total points
ID: 22695319
Binuth's answer is correct.

But just a suggestion, but you are already going through all this trouble in your function, you can just simply use the function as a table as Binuth is also illustrating and skip the variable.

select * from dbo.fnDStringToTable('1,2,3',',')

Gets you same results as above. :)
0

Featured Post

Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In this article we will get to know that how can we recover deleted data if it happens accidently. We really can recover deleted rows if we know the time when data is deleted by using the transaction log.
This article shows gives you an overview on SQL Server 2016 row level security. You will also get to know the usages of row-level-security and how it works
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.

911 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

22 Experts available now in Live!

Get 1:1 Help Now