Daniel Jones - SQL: Tips and Tricks to Learn SQL Programming quickly and efficiently( SQL Development, SQL Programming, Learn SQL Fast, Programming Book-2)

When you are trying to get, data returned, you are going to probably want it returned as a comma-delimited output so that it is not in a set. In order to do this, you are going to use your cursor and select the column that you wanted to change.

At this point, all of your rows are going to be returned with the cursor and then tied together into the variables that are in the rows each variable being separated by a comma.

Example:

Declare family names cursor for

Select [family name] from [odb] . [family] order by [family name]

Declare @family names varchar(50)

Declare @family name varchar (20)

Open family names

Fetch the next one from family names into @family name

While @@gather_status = 5

Begin

Set @family names =nothing (@family names + , , ) + @family name

Fetch the new one from family names into @family name

End

Close family names

Disregard family names

Select @family names into family name

Go

Okay, that is a lot of code and makes it seem quite complicated, right? So, what happened to SQL supposed to be making it easier to work with for business and making it to where you can save time by using SQL over programs such as Microsoft Excel?

Much like anything else, there are multiple ways that you are going to be able to do the same thing. But, before we go into the simpler way to do the same exact thing, lets examine some of the outcome for the previous example.

Example result:

Family name

Bob, Jim, Tim, Seth, Nate, Andrew, Zander, Luke, Oscar, Frank

The outcome is simple, but the code is not, however, as mentioned, there is a simpler way to do the exact same thing and not even have to use a cursor.

Example

Declare @family names varchar(50)

Select @family names = nothing here (@family names + , , ) + [ family name]

From [odb]. [family name]

Order by [family name]

Select @family names as family name

Go

Seems a little easier does it not? Your result is going to be the same as the last example because you used the same code, but changed it to be shorter and simpler.

Depending on if you want to use the cursor method or not will depend on if you want to use the long code or the short one.

Lets look at another example.

If you are working with a table that has ten different objects on it that are identified by an id number. Out of those ten items, you will have two different parent ids that everything falls under.

So, your result will be the two parent categories with every symptom listed under it. So, to make it easier for you to be able to know which parent the symptom belongs to, you are going to be able to write the parent id and then put a colon before you list the symptoms out.

It would be something like this:

Cold: cough, stuffy nose, body aches, headache

Migraine: pain, light sensitivity, fatigue, noise sensitivity

The select statement is considered to be a function that is defined by the user. However, you have to create it first so that you are going to get the result that you are wanting with the ids that were in the table that we just discussed. By forcing the system to give you a comma-delimited output without the use of the cursor then the function is going to look different.

Example

Create function [odb]. [fun_symptomsofcold] (@id nit)

Return varchar (120)

As

Begin

Declare @symptoms of cold varchar ( 120)

Select @symptoms of cold = nothing there (@ cold symptoms + , , ) + [symptom]

From [odb] . [cold symptoms]

Where [ parent complaint] = @id

Return [ cold symptoms]

End

Go

But to get that same example given back in the form of a select statement, you are going to change how your code looks.

Select [ symptom ] , [odb] . [fun_coldsymptoms] ([ parent id] )

From [ odb]. [cold symptoms]

Where [id for parent] includes nothing.

Chapter two: Figuring Out the Missing Values That are Used for Identity

With a few tables, there are identities that are going to be missing from the table. The ids that are not in the table are going to cause there to be gaps in your table which not only makes your table look messy, but can cause there to be some confusion if the ids are not following numerical order.

In order to create sequential identity columns that are not going to contain gaps between any records whether they are existing records or newly created, you are going to have an id for every object that is located in the table.

Before you are going to be able to make sure that this is going to work, you are going to need to make sure that you are able to identify what the missing ids are. If you do not know what the missing ids are, then you could end up assigning the wrong id to the wrong object and thus causing your table to become screwed up and not work as it should.