I have a select query
select *
from dbo.GetTableFromString ('2,www.google.com')
which returns this result:
2
www.google.com
Edit from comments: Function accepts comma separated values and returns a single column called name.
Now I want to store 2 in variable called @ID and www.google.com in @Link.
What is the best way to achieve this?
DECLARE @SplitResults table(
RowIndex int IDENTITY(1,1)
,Item varchar(200));
INSERT INTO @SplitResults
select * from dbo.GetTableFromString ('2,www.google.com');
SET @ID = (SELECT Item FROM @SplitResults WHERE RowIndex=1);
SET @Link = (SELECT Item FROM @SplitResults WHERE RowIndex=2);
2 will end up in @ID. It can get RowIndex=2.
GetTableFromString actually does, so only to make a hypothesis for the result from GetTableFromString.GetTableFromString returns a table. This table is not ordered (as any table), so when you INSERT this table into @SplitResults, the order in which RowIndex identity values are generated is not defined. As a result, you may get 2 in the @ID variable or you may get 2 in the @Link variable. www.google.com may end up in the @ID variable and www.google.com may end up in the @Link variable.GetTableFromString is user-defined function, It will keep the order for the result, and there are more methods to keep the output order (order for custom), this question is just only for rows in table into variables only.
declare @ID int, @link nvarchar(max);
select @ID = ID, @link = Link
from dbo.GetTableFromString ('2,www.google.com')
I suppose columns are returning the function dbo.GetTableFromString id and link. If they are not replaced by the correct column names..
try this.
declare @str nvarchar(200)
set @str= '2,www.google.com'
declare @len int
SELECT @len=CHARINDEX(',', @str);
SELECT SUBSTRING( @str,0, @len ) 'id'
SELECT SUBSTRING( @str,@len+1, LEN(@str) )'link'
Just use Charindex
DECLARE @TextValue varchar(100) = '2,www.google.com'
DECLARE @ID int
DECLARE @link varchar(100)
SELECT @ID = substring(@TextValue, 0, charindex(',',@TextValue)),
@link = RIGHT(@TextValue,len(@TextValue) - charindex(',',@TextValue))
SELECT @ID, @link
return
2 www.google.com
declare @id int,@link varchar(200) ;with cte as ( select * from dbo.GetTableFromString ('2,www.google.com') ) select @id=id,@link=link from cte