Solved

SQL function , loop and lookup example .

Posted on 2016-08-10
23
65 Views
Last Modified: 2016-08-30
Requirement  have an Input array populated e.g 10000, 10001, 10002
Want each of the number values in the array to be individually looked up and there respective text name values returned,
This is also for me to improve my knowledge of SQL loops. I have started the below and not sure if I have closed the while loop sufficiently .

CREATE FUNCTION Closure.PLookup3
(
@Pnuminput nvarchar(255)
)
RETURNS nvarchar(255)
AS
BEGIN
      -- Declare the return variable here
      DECLARE @pnumitem nvarchar(10)
      DECLARE @Pvaloutput nvarchar(255)
      DECLARE @Delimiter nvarchar(1)
      Set @Delimiter = '-'
      Set @Pvaloutput = ""
      -- Add the T-SQL statements to compute the return value here
      WHILE @pnumitem <>''
            BEGIN
      @pnumitem = SUBSTRING(@delimiter,@Pnuminput,0)
      @pvaloutput = @pvaloutput + SELECT E1.EnumvalueName From Dbo.CL_ENUM_VALUE AS E1 WHERE E1.Id = @pnumitem
     
            END
      -- Return the result of the function
      RETURN @Pvaloutput
-- PRINT @Pval
END  

---
0
Comment
Question by:yasanthax
  • 9
  • 6
  • 2
  • +3
23 Comments
 
LVL 69

Expert Comment

by:ScottPletcher
ID: 41751266
Don't loop!  

Instead, split the string into a table using an in-line table-valued function; most recommended is dbo.DelimitedSplit8K.

Then join to Dbo.CL_ENUM_VALUE for every item returned from the split.
0
 
LVL 40

Expert Comment

by:Sharath
ID: 41751271
This returns PnumID and its EnumvalueName in tabular format. Do you want to convert the EnumvalueNames into comma separated list?
declare @Pnuminput nvarchar(100) = '10000,10001,10002'

select t1.Pnum, E1.EnumvalueName
  from (
SELECT ltrim(SUBSTRING(Pnuminput, n, CHARINDEX(',', Pnuminput + ',',n) - n)) AS Pnum
 FROM (select @Pnuminput Pnuminput) t1
CROSS JOIN (SELECT number FROM master..spt_values WHERE type = 'P')  AS Numbers(n)
WHERE SUBSTRING(',' + Pnuminput, n, 1) = ','
  AND n < LEN(Pnuminput) + 1 ) t1
  join Dbo.CL_ENUM_VALUE AS E1 where E1.Id = t1.Pnum

Open in new window

0
 
LVL 29

Accepted Solution

by:
Olaf Doschke earned 250 total points
ID: 41751323
Besides the evangelisation to use a splitter function, you've got several problems and SSMS will tell you error, you just have to follow the error messages, learn parameterization of SUBSTRING and some more things...

CREATE FUNCTION dbo.HelloWorld
(
@Pnuminput nvarchar(255)
)
RETURNS nvarchar(255) 
AS 
BEGIN
      -- Declare the return variable here
      DECLARE @pnumitem nvarchar(10)
      DECLARE @Pvaloutput nvarchar(255)
      DECLARE @Delimiter nvarchar(1)
      Set @Delimiter = '-'
      Set @Pvaloutput = '' -- minor error, empty string is only '', not ""

	  -- simple test data here
	  Declare @data as Table (Id int, EnumvalueName varchar(20));
	  insert into @data values (1,'Hello'), (2, 'World'), (3, '!');

      -- Add the T-SQL statements to compute the return value here

      While 1=1
            BEGIN
      -- minor error: Forgot SET
      SET @pnumitem = SUBSTRING(@Pnuminput, 1, CHARINDEX(@delimiter, @Pnuminput+@delimiter, 0)-1) -- major error, wrong parameter usage of SUBSTRING(@delimiter,@Pnuminput,0)
	  IF @pnumitem = '' BREAK;
	     
	  -- Major error: Can't Set var = var + query, instead do the assignment within the query:
      SELECT @pvaloutput = @pvaloutput + E1.EnumvalueName+' ' From @data AS E1 WHERE E1.Id = convert(int,@pnumitem);
	  
      -- MAJOR error: How should this loop end without a change of @Pnuminput?
	  SET @Pnuminput = SUBSTRING(@Pnuminput, CHARINDEX(@delimiter, @Pnuminput+@delimiter, 0)+1,8000)
            END 
      -- Return the result of the function
      RETURN @Pvaloutput
END  

Open in new window


After creating that function, test it with
PRINT dbo.HelloWorld('1-2-3');

Open in new window


Bye, Olaf.
0
 
LVL 29

Assisted Solution

by:Olaf Doschke
Olaf Doschke earned 250 total points
ID: 41751330
Also, notice one change I made for the program flow to work:

WHILE @pnumitem <>''
            BEGIN
      ...
            END 

Open in new window


This loop won't even start, because @pnumitem is '' to begin with, you only set it within the loop. In such a case, where you always want to enter the loop, rather do a WHILE 1=1 loop and break it, when the exit condition is fulfilled, as I did.

Bye, Olaf.
0
 
LVL 10

Assisted Solution

by:HuaMinChen
HuaMinChen earned 250 total points
ID: 41751406
Use a cursor like
DECLARE @name VARCHAR(50) -- database name  
DECLARE @path VARCHAR(256) -- path for backup files  
DECLARE @fileName VARCHAR(256) -- filename for backup  
DECLARE @fileDate VARCHAR(20) -- used for file name 

SET @path = 'C:\Backup\'  

SELECT @fileDate = CONVERT(VARCHAR(20),GETDATE(),112) 

DECLARE db_cursor CURSOR FOR  
SELECT name 
FROM MASTER.dbo.sysdatabases 
WHERE name NOT IN ('master','model','msdb','tempdb')  

OPEN db_cursor   
FETCH NEXT FROM db_cursor INTO @name   

WHILE @@FETCH_STATUS = 0   
BEGIN   
       SET @fileName = @path + @name + '_' + @fileDate + '.BAK'  
       BACKUP DATABASE @name TO DISK = @fileName  

       FETCH NEXT FROM db_cursor INTO @name   
END   

CLOSE db_cursor   
DEALLOCATE db_cursor

Open in new window

0
 

Author Comment

by:yasanthax
ID: 41751868
Hi Olaf

I checked your solution and it works , however one little issue when the first character of @pnuminput is '-' . I was trying to put an expression using my vba knowledge and make some changes in SQL .

If left(@pnuminput, 1)
Begin
@pnuminput = right(@pnuminput , len(@pnuminput)-1)
End

I also assume @data is a temporary table and I had to insert values of this table into this . Assume this is correct as it worked . I could not simply use @data = select ....

Thanks
0
 
LVL 12

Expert Comment

by:funwithdotnet
ID: 41752090
FYI: SQL Server 2016 includes a built-in STRING_SPLIT function for v13 databases ... finally!
1
 
LVL 29

Expert Comment

by:Olaf Doschke
ID: 41752542
Your assumption about the reason for @data is wrong. It is simply my sample codes replacement for your real table Dbo.CL_ENUM_VALUE, you can simply remove its definition and the insert into it, as you have your data.

The solution to your problem of a prefixed '-' almost works, you just forgot ...= @Delimiter, it has to be If left(@pnuminput, 1) = @Delimiter, without the comparison there is no boolean result.

And once again, you forgot the SET. Assignments in T-SQL need the SET command, expressions as @var = value are not seen as assignments, but as comparison. The alternative is within a SELECT, as also used in the code, but also not in any SELECT. Refer to the reference to pick up what I try to teach you here, please:
1. SET @var=...: https://msdn.microsoft.com/en-us/library/ms189484.aspx
2. SELECT @var=...:https://msdn.microsoft.com/en-us/library/ms187330.aspx

Bye, Olaf.
0
 
LVL 69

Expert Comment

by:ScottPletcher
ID: 41752601
Sorry, didn't have time to do actual code when I first posted.

Again, forget about looping!  It's a performance killer in SQL.  For relational languages, you need to think set-based rather than loop-based.

If you want to use a function, use an inline table-valued function so that it performs well if you need to decode an entire table's worth of codes.

Here's the main function; it uses the high-performing dbo.DelimitedSplit8K function I mentioned earlier: you can Google the source for that function:

CREATE FUNCTION Closure.PLookup3
(
    @Pnuminput varchar(255)
)
RETURNS TABLE
AS
RETURN (
    SELECT STUFF((
        SELECT ',' + E1.EnumvalueName
        FROM dbo.DelimitedSplit8K(@Pnuminput, ',') ds
        LEFT OUTER JOIN dbo.CL_ENUM_VALUE E1 ON E1.Id = LTRIM(RTRIM(ds.Item))
        ORDER BY ds.ItemNumber
        FOR XML PATH('')
        ), 1, 1, '') AS return_values
)
GO


Here's some sample code-table values:

CREATE TABLE dbo.CL_ENUM_VALUE (
    Id int PRIMARY KEY,
    EnumvalueName varchar(255) NOT NULL
    )
INSERT INTO dbo.CL_ENUM_VALUE VALUES(10000,'String10000'), (10001,'String10001'), (10002,'String10002')
*/


And here's the code to do the actual decoding.  Note how quickly this code runs!

DECLARE @input_values varchar(8000)
DECLARE @return_values nvarchar(4000)
SET @input_values = '10000, 10001, 10002'

SELECT *
FROM Closure.PLookup3(@input_values)

SELECT @return_values = return_values
FROM Closure.PLookup3(@input_values)
PRINT @return_values
1
 
LVL 40

Expert Comment

by:Sharath
ID: 41752727
Did you try other posts?
0
 
LVL 29

Expert Comment

by:Olaf Doschke
ID: 41753146
While I agree with Scott in general, it looks really odd to use a splitter function and then look up single values to then concatenate them using XML PATH.

I instead just gave you, what you asked for, but I'd go for yet another solution.

Using a splitter stored proc and XML PTAH are the usual ways to do this kind of thing in T-SQL and fortunately SQL 2016 introduces a Split_String() function to put one of them at rest, but more natural usage of a series of IDs, of course, is to put them into a table, perhaps a temp table, and inner join this set of IDs. And a natural result of such a query rather is a set of values than a concatenation of them.

This very obviously looks like something done outside of the core intended service of a database. I hope one day MS will also introduce something like Concat_String and let XML PATH be used to process something else, perhaps XML data. Judging from the name, it looks like it might be useful there.

The most natural way to go about this, in my opinion, would be creating a temp table, inserting the single IDs - perhaps for that to work get rid of data with dash separated IDs but instead, have a detail table and then do a classical join with the result also being a set of values.

That set of values arriving anywhere client side can afterwards be iterated and concatenated, because that indeed is not a thing to do in T-SQL, really, but is dead easy in any client side multi-purpose programming language. PHP has explode/implode, C#, Python, and even Javascript have String.Join() or Array.Join() to convert lists or arrays to a single string.

Bye, Olaf.
0
How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

 

Author Comment

by:yasanthax
ID: 41754045
Perfect . Great stuff guys and thanks for the reference information . Much appreciated again . Have a good weekend .
0
 
LVL 69

Expert Comment

by:ScottPletcher
ID: 41754067
While I agree with Scott in general, it looks really odd to use a splitter function and then look up single values to then concatenate them using XML PATH.
...
Using a splitter stored proc and XML PTAH are the usual ways to do this kind of thing in T-SQL and

Isn't that self-contradictory?  If it's the "usual way", how can it be "odd"?  

And why is it "odd" anyway?  You have to look up the values individually, as they appear in the lookup table as single entries, not combined.  How else, then, could one look them up except to split them first and then look each up separately??

What's "odd" to me is to encourage any type of looping within a set-based language such as SQL, unless such a loop is absolutely unavoidable.  The poor performance of such code is just another reason to avoid it.

As to whether this should/should not be done within the db language, that's a different q to me.  The q here was: once I've chosen to do this within the dbms engine, what's the best way to do it?

Finally, as much as we DBAs/developers like result sets, sometimes real-world applications need a singe string of results rather than an array.  For example, when I write emails to several people, I like being able to list "name1; name2; ..." rather than me having to create a "name_table".
0
 
LVL 29

Expert Comment

by:Olaf Doschke
ID: 41754303
How can a usual way be odd? Well, can't you see this yourself? Why should we need such a complex way to first create a tally table large enough to be able to create all the possible substrings and take out those splitting exactly at delimiters. This really is the pride in managing to do something with the limited possibilities of SQL, while it's just straight forward 𝒪(n) complexity - linear to the length of the string to split. It's a simple iteration to go through a string and split it at delimiters found while iterating from position 1 to length.

This always wasn't awesome to me, but crap. It might awesome in the aspect to manage this, but it's really making a bad impression to me about Microsoft or any RDBMS vendor not to have come up with a String_Split() function earlier if you see how often a splitter stored proc is used. And now they had the kindness to do this not to come up with the inverse aggregation function. MySQL has GROUP_CONCAT() for example, Oracle has LISTAGG.

The word for doing such simple things in the way they are done may best be described as awkward. And I see a tendency of people bragging with this knowledge of this splitter functions and the performance they managed to get from them, while this is something you should rather point to in shame of not having something more straight forward.

Bye, Olaf.
0
 
LVL 69

Expert Comment

by:ScottPletcher
ID: 41754343
I didn't design or write SQL Server's code and must work within what it has, not refuse to write efficient code because some other language might have a capability that SQL current doesn't.  If you're ashamed of having to work within the restrictions of any given language, perhaps you shouldn't be a developer, since no one language can offer all things to all devlopers.

I don't consider lack of a string split function to be a horrible handicap for a relational dbms, particularly given that SQL provides the capability to create our own functions.
0
 
LVL 29

Expert Comment

by:Olaf Doschke
ID: 41754347
And what I agree with is that the capability to do while loops in T-SQL can be seen as implemented just for the sake of completeness of a formal language including simple program flow paradigms. Queries are indeed loops in themselves, they iterate on sets, but in a less ordinary way of the "full table scan" simple iterations any simple for loop would do. So given the situation as is, implementing a loop within a stored proc only can be argued for to have a better case than just an academic example to learn a bit about how loops could work and the lesson learned from yasanthax or what he should have learned here seems rather to be about SET and the paramterization of SUBSTRING and CHARINDEX than about WHILE.

So in short I seconde given the situation of T-SQL this specific problem it's still not a case for using WHILE but rather what you can use today. Configuring SQL SErver to be able to program in C# could improve this, but has it's own pros and cons.

You know and recommend the article of Jeff Moden and you know it ends in showing how a Split based on CLR (c#) is fastest anyway. Which proves to me, this is the job of the RDBMS vendor to add it as a native functionality done in C# or perhaps C++, but it was a missing portion of T-SQL so far.

Bye, Olaf.
0
 
LVL 29

Expert Comment

by:Olaf Doschke
ID: 41754349
And finally you miss a simple point of what yasanthax said:

This is also for me to improve my knowledge of SQL loops

So I gave that lesson to be able to see a working loop.

Bye, Olaf.
0
 
LVL 69

Expert Comment

by:ScottPletcher
ID: 41754357
I didn't miss that point.  I made the counter-point that you should not focus on writing loops in SQL Server, but instead focus more on writing set-based solutions.  SQL isn't C++ and shouldn't be written the same way.  You need to learn to think in terms of sets, not individuals rows.

Starting to learn SQL by studying loops is terribly counter-productive.  You should be studying types of joins, etc., not how to code loops.

Similarly, if you want to update every row that contains a NULL value in a given column, you should do this:
UPDATE table_name
SET col1 = ''
WHERE col1 IS NULL

And not as below, even though, yes, the code below would allow you to practice writing loops(!):

DECLARE @id int
DECLARE csr_col1 CURSOR FOR
SELECT id /*assume id is a unique key for the row*/
FROM table_name
WHERE col1 IS NULL

OPEN CURSOR csr_col1

WHILE 1 = 1
BEGIN
    FETCH NEXT FROM csr_col1 INTO @id
    IF @@FETCH_STATUS <> 0
        BREAK
    UPDATE table_name
    SET col1 = ''
    WHERE id = @id
END /*WHILE*/

DEALLOCATE csr_col1
0
 
LVL 29

Expert Comment

by:Olaf Doschke
ID: 41754644
Scott, you really are very inattentive. The quote I gave was not quoting myself but the Author of this question. You just have to scroll back to the question itself to find it there, or press CTRL+F and search. Anyway, that misunderstanding from your side is easily solved, all you say is addressed to him and I don't feel bad about this, I rather feel sorry for you.

I do agree with you, that SQL Servers should be used with set based code, eg SQL. In other occasions, I also said some functions should rather not be available in T-SQL at all, but I don't think so in regard to IF and WHILE loops, they have their raison d'être.

And while this isn't an ideal case to implement a loop, its not so bad as your UPDATE on NULL condition. So in your opinion yasanthax should perhaps never ever learn loops in T-SQL, I rather helped him understand his errors, and knowledge about how and where to set variables surely is helpful also outside of loops, so this is really an improvement of skills. If you like you can put all other commands but INSERT, SELECT, UPDATE and DELETE aside, create tables with the visual frontend the SSMS offers and can do a CRUD API interface to a database and everything else you can't do with this subset you can do clientside. Fine. I'm overexaggerating your point of view, but that's how I see your stance on this.

And on the piont that "no one language can offer all things to all devlopers": I didn't made the point that any language should be universal, I made the point any language should be pure for it's intention. And for SQL that would include such split CSV (more precise one row of CSV rather is one record, so we talk of UNPIVOTING that, but simply take "comma separated values" in that literal sense and that also applies to '1,2,3') to rows and concat rows to CSV. It's not the core of SQL, but it obviously is helpful and even part of SQL2011 ANSI standard.

Anyway, like so many times already, we can agree to disagree and lay this down at this point.

Bye, Olaf.
0
 
LVL 69

Expert Comment

by:ScottPletcher
ID: 41754856
Hardly inattentive.  I quite noticed that you excused your recommendation for poor coding on the initial q; impossible to miss really.  But, as experts, we're supposed to provide guidance when the original poster is not aware of some capabilities of SQL and/or the best approach to take.  Loops have their place in SQL, but that task is not properly one of them.

I'm the one trying to provide professional-level methods for doing specific tasks in SQL, using the currently best techniques available, rather than using lowest-common-denominator loops.  Just because you aren't familiar with a techniques does not make it "odd" or improper.  The approach I recommended is well-tested, proven and generally accepted as best practice by true SQL experts.

Finally, it's absolutely bizarre to insist that the "pure intention" of SQL would have a string split function.  The underlying theory of SQL is based on atomic (single-value) columns.  Reference Codd's rules and normal data forms.  Thus, a splitter function is a great extension to the language, but hardly a core requirement.
0
 
LVL 29

Expert Comment

by:Olaf Doschke
ID: 41754983
I'm almost fine with that, except two things, unfortunately the core things and not just side notes.

1. Our task as experts
I think even yasanthax himself knows it's not the first class solution to do a loop, therefore he explicitly asked how this task could be done so. In the light of the lack of not knowing several basic things one could also see this different, but if you never do anything but query language, you can't get well-versed on T-SQL's part of structural programming. He damnded to be teached what I answered and not doing and even with the best intention of it I can think of, I consider that arrogant towards yasanthax and not helpful and corrective.

Edit: To be more precise, you can give the code asked for and the better alternative on top of that, instead of withholding any help just because of your expertise on it and even when you consider having all experts on your side.

I have your stance from time to time, but only if I see something dangerous in what is asked for, not if it's just a less good solution.

2. Splitter function
A splitter function is a great extension to the language, but hardly a core requirement.
Well, SQL has evolved from that core requirements and when even the American National Standards Institute considers that to be part of the SQL language and several competing databases implement this, it is a shame Microsoft is last - again.

Bye, Olaf.
0
 
LVL 12

Expert Comment

by:funwithdotnet
ID: 41755027
If I made the first contribution to this question, it would read:

I don't recommend looping in SQL. However, to answer your question, perhaps an expert will comment as to the best way to loop, as I am not  an expert at looping in SQL.

In situations where I perceive a pure SQL solution is messy or overly-complicated, I use .NET processing to resolve all other issues. Changes even in the most complex transformations are a breeze that way.  A pure SQL solution may require that SQL do things it's not very good at. Same thing goes for trying to make .NET do things SQL does better.  I let each tool do what it does best in concert,

Good luck!
1
 
LVL 10

Expert Comment

by:HuaMinChen
ID: 41774249
Hi Yasanthax,
Please confirm you have no more questions to this thread. Thanks
0

Featured Post

Threat Intelligence Starter Resources

Integrating threat intelligence can be challenging, and not all companies are ready. These resources can help you build awareness and prepare for defense.

Join & Write a Comment

Suggested Solutions

Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties

708 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

13 Experts available now in Live!

Get 1:1 Help Now