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.


  • area_deu

    @djls45 said in T-SQL Evaluate function:

    FOR XML

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


  • kills Dumbledore

    @djls45 said in T-SQL Evaluate function:

    STUFF

    My favourite TSQL function



  • @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 stuffinserts a string into another string.



  • @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



  • @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.


  • kills Dumbledore

    @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 +



  • @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.



  • @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?



  • @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 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.

    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)?


Log in to reply
 

Looks like your connection to What the Daily WTF? was lost, please wait while we try to reconnect.