T-SQL Evaluate function

[dangeRuss]

I'm trying to do something like this

DECLARE @p1 VARCHAR(100)
SET @p1='test1'
DECLARE @i=1
SET @result=evaluate('@p' + @i)


Basically I want @result to be 'test1' in this case

Is there such a function in SQL Server?

[Lorne Kates]

@dangeRuss said in T-SQL Evaluate function:

I'm trying to do something like this

DECLARE @p1 VARCHAR(100)
SET @p1='test1'
DECLARE @i=1
SET @result=evaluate('@p' + @i)


Basically I want @result to be 'test1' in this case

Is there such a function in SQL Server?

Hi, I see you're trying to use Eval.

Have you considered not doing that?

Alternately, have you considered eating poison until you die? It'll be better for all of us.

And do you mean @result should be 'test1' or 'test11'.

If you mean test11, why not just do:

SET @result = @p + CAST(@i as varchar)

[PleegWat]

@Lorne-Kates I think he's trying to fake arrays. Does T-SQL have arrays?

[Lorne Kates]

@PleegWat said in T-SQL Evaluate function:

@Lorne-Kates I think he's trying to fake arrays. Does T-SQL have arrays?

Yes. They're called tables.

[dangeRuss]

@Lorne-Kates said in T-SQL Evaluate function:

@PleegWat said in T-SQL Evaluate function:

@Lorne-Kates I think he's trying to fake arrays. Does T-SQL have arrays?

Yes. They're called tables.

Yes I'm trying to fake arrays. Why can't T-SQL just have basic programming constructs?

Anyway I think I solved it using a case statement, but would've been nicer to use an array or something.

[Lorne Kates]

@dangeRuss said in T-SQL Evaluate function:

Why can't T-SQL just have basic programming constructs?

Because tables. :/

What was wrong with DECLARE @table table (idx int primary key auto_incriment, val nvarchar(2571)) ?

Does your product/solution have at least one column that's actually a CSV?

[dangeRuss]

@Lorne-Kates said in T-SQL Evaluate function:

@dangeRuss said in T-SQL Evaluate function:

Why can't T-SQL just have basic programming constructs?

Because tables. :/

What was wrong with DECLARE @table table (idx int primary key auto_incriment, val nvarchar(2571)) ?

Does your product/solution have at least one column that's actually a CSV?

I was just trying to get a formula to work. it's basically something like this

max=3;
p=['a','b','c','d'];
for (i=0;i<max;i++) {
if i=0 {
ans=p[0];
}
else {
ans=ans+ "+" + p[i];
}
}

How do you do the equivalent in tsql.

max would be something that's passed in.

[djls45]

@dangeRuss said in T-SQL Evaluate function:

@Lorne-Kates said in T-SQL Evaluate function:

@dangeRuss said in T-SQL Evaluate function:

Why can't T-SQL just have basic programming constructs?

Because tables. :/

What was wrong with DECLARE @table table (idx int primary key auto_incriment, val nvarchar(2571)) ?

Does your product/solution have at least one column that's actually a CSV?

I was just trying to get a formula to work. it's basically something like this

max=3;
p=['a','b','c','d'];
for (i=0;i<max;i++) {
if i=0 {
ans=p[0];
}
else {
ans=ans+ "+" + p[i];
}
}

How do you do the equivalent in tsql.

max would be something that's passed in.

I believe this will do what you're looking for:

-- create the variables
DECLARE @max int
DECLARE @result VARCHAR(100)
DECLARE @p TABLE (idx INT IDENTITY, val VARCHAR(100))

-- set up the list of values
INSERT INTO @p (val)
VALUES ('a'), ('b'), ('c'), ('d')

SET @max = 3

-- perform the meat of the formula
SELECT @result = STUFF(				-- replace range of positions in string with string
		(SELECT 			-- | | query to get
			top(@max)		-- | | the first <max>
				  '+' + val	-- | | values
		 FROM @p			-- | | from the list of values
		 ORDER BY idx			-- | | in the intended (original?) order
		 FOR XML PATH(''), TYPE		-- | coalesce the values together
		).value('.', 'NVARCHAR(MAX)')	-- | convert the XML type to NVARCHAR type
	, 1, 1, '')				-- rest of parameters for STUFF (from position 1 to position 1, replace with <empty string>

PRINT @result -- if @max = 3, then this outputs "a+b+c"

Edit: Updated formatting to my liking and added explanation.

[ChrisH]

@djls45 said in T-SQL Evaluate function:

FOR XML

I hope you die as the result of an unpleasant tin opener malfunction.

[Jaloopa]

@djls45 said in T-SQL Evaluate function:

STUFF

My favourite TSQL function

[dangeRuss]

@Jaloopa said in T-SQL Evaluate function:

@djls45 said in T-SQL Evaluate function:

STUFF

My favourite TSQL function

MikeRayMSFT

STUFF (Transact-SQL) - SQL Server

The STUFF function inserts a string into another string.

The STUFF function inserts a string into another string.

[Maciejasjmj]

@dangeRuss said in T-SQL Evaluate function:

The STUFF function does stuffinserts a string into another string.

[dangeRuss]

@Maciejasjmj said in T-SQL Evaluate function:

@dangeRuss said in T-SQL Evaluate function:

The STUFF function does stuffinserts a string into another string.

I think you're confusing it with the PHP stuff function, although in PHP it would probably be real_stuff_function_this_time_3

[dangeRuss]

@djls45 said in T-SQL Evaluate function:

@dangeRuss said in T-SQL Evaluate function:

@Lorne-Kates said in T-SQL Evaluate function:

@dangeRuss said in T-SQL Evaluate function:

Why can't T-SQL just have basic programming constructs?

Because tables. :/

What was wrong with DECLARE @table table (idx int primary key auto_incriment, val nvarchar(2571)) ?

Does your product/solution have at least one column that's actually a CSV?

I was just trying to get a formula to work. it's basically something like this

max=3;
p=['a','b','c','d'];
for (i=0;i<max;i++) {
if i=0 {
ans=p[0];
}
else {
ans=ans+ "+" + p[i];
}
}

How do you do the equivalent in tsql.

max would be something that's passed in.

I believe this will do what you're looking for:

-- create up the variables
DECLARE @max int
DECLARE @result VARCHAR(100)
DECLARE @p TABLE (idx INT IDENTITY, val VARCHAR(100))

-- set up the list of values
INSERT INTO @p (val)
VALUES ('a'), ('b'), ('c'), ('d')

SET @max = 3

-- perform the meat of the formula
SELECT @result = STUFF((SELECT top(@max) '+' + val 
		FROM @p 
		ORDER BY idx
	FOR XML PATH(''), TYPE
	).value('.', 'NVARCHAR(MAX)') 
	,1,1,'')
PRINT @result -- if @max = 3, then this outputs "a+b+c"

Thank you. I don't think that's something I fully understand, so I'm not going to change it. Points for creativity though.

[Jaloopa]

@dangeRuss the FOR XML PATH thing is a relatively well known hack for doing this sort of thing in TSQL. It's nasty and unreadable but out there in the wild. Just that would output +a+b+c, then the STUFF(text, 1, 1, '') is starting from the first character, replacing a length of one character with an empty string: basically getting rid of the leading +

[Groaner]

@Jaloopa said in T-SQL Evaluate function:

@dangeRuss the FOR XML PATH thing is a relatively well known hack for doing this sort of thing in TSQL. It's nasty and unreadable but out there in the wild. Just that would output +a+b+c, then the STUFF(text, 1, 1, '') is starting from the first character, replacing a length of one character with an empty string: basically getting rid of the leading +

I personally prefer this construction:

declare @foo varchar(max) = ''
select @foo = @foo + coalesce(name, '') + ', '
from table
order by something

But then people whine about undefined behavior.

[dangeRuss]

@Groaner said in T-SQL Evaluate function:

@Jaloopa said in T-SQL Evaluate function:

@dangeRuss the FOR XML PATH thing is a relatively well known hack for doing this sort of thing in TSQL. It's nasty and unreadable but out there in the wild. Just that would output +a+b+c, then the STUFF(text, 1, 1, '') is starting from the first character, replacing a length of one character with an empty string: basically getting rid of the leading +

I personally prefer this construction:

declare @foo varchar(max) = ''
select @foo = @foo + coalesce(name, '') + ', '
from table
order by something

But then people whine about undefined behavior.

I'm familiar with coalesce. What's this undefined behavior you speak of?

[djls45]

@Jaloopa @dangeRuss
I've edited my post to make it maybe more readable and added an explanation.

[Groaner]

@dangeRuss said in T-SQL Evaluate function:

@Groaner said in T-SQL Evaluate function:

@Jaloopa said in T-SQL Evaluate function:

@dangeRuss the FOR XML PATH thing is a relatively well known hack for doing this sort of thing in TSQL. It's nasty and unreadable but out there in the wild. Just that would output +a+b+c, then the STUFF(text, 1, 1, '') is starting from the first character, replacing a length of one character with an empty string: basically getting rid of the leading +

I personally prefer this construction:

declare @foo varchar(max) = ''
select @foo = @foo + coalesce(name, '') + ', '
from table
order by something

But then people whine about undefined behavior.

I'm familiar with coalesce. What's this undefined behavior you speak of?

It depends on, among other things, the selected query plan iterating through all the rows you intend to concatenate.

Concatenating strings in SQL Server, or undefined behaviour by design

I've written a ton of code using that construction and have had zero problems so far, though.

[djls45]

@ChrisH said in T-SQL Evaluate function:

@djls45 said in T-SQL Evaluate function:

FOR XML

I hope you die as the result of an unpleasant tin opener malfunction.

If I may ask, why the hate? Are there special pitfalls to watch out for when using it, or is it just the typical unreadability of a function that takes multiple parameters (and M$'s maybe-not-so-helpful help pages)?

[Lorne Kates]

@dangeRuss said in T-SQL Evaluate function:

I don't think that's something I fully understand

That is painfully obvious.

[Jaloopa]

@dangeRuss said in T-SQL Evaluate function:

What's this undefined behavior you speak of?

If that's what I think it is, look up quirky update. This is probably widespread enough how that MS will have to treat it as a documented feature in future versions