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?
-
@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)
-
@Lorne-Kates I think he's trying to fake arrays. Does T-SQL have arrays?
-
@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.
-
@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.
-
@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?
-
@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.
-
@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.
-
@djls45 said in T-SQL Evaluate function:
FOR XML
I hope you die as the result of an unpleasant tin opener malfunction.
-
-
@Jaloopa said in T-SQL Evaluate function:
@djls45 said in T-SQL Evaluate function:
STUFF
My favourite TSQL function
The STUFF function inserts a string into another string.
-
@dangeRuss said in T-SQL Evaluate function:
The STUFF function does stuff
inserts a string into another string.
-
@Maciejasjmj said in T-SQL Evaluate function:
@dangeRuss said in T-SQL Evaluate function:
The STUFF function does stuff
inserts 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
-
@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.
-
@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 theSTUFF(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+
-
@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 theSTUFF(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.
-
@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 theSTUFF(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?
-
@Jaloopa @dangeRuss
I've edited my post to make it maybe more readable and added an explanation.
-
@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 theSTUFF(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.
I've written a ton of code using that construction and have had zero problems so far, though.
-
@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)?
-
@dangeRuss said in T-SQL Evaluate function:
I don't think that's something I fully understand
That is painfully obvious.
-
@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