Stored Procedure to cross reference table

[M8KWR]

I think i need a stored procedure, but unsure where to begin.

I want to send 3 fields to it, lets called the F1, F2 and F3.

These to be queries against another table in the same db, which will bring back multiple rows.

With these rows i want to loop through each one and grab a text field, and combine them all together to make 1, but in between each one put a carriage or 2.

Am i thinking a stored procedure is what i require, if not can anyone advise me what i should be looking to do.

Many thanks in advance.

 

[gmmastros]

Can you post some sample data and expected results? This will help us to understand what you are trying to accomplish and allow us to offer better advice.


-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[markros]

If you want to concatenate strings, take a look at the very top of

http://blogs.lessthandot.com/index.php/DataMgmt/DataDesign/cte-and-hierarchical-queries

for example of concatenating Phone and Phone type with carriage return in between.

 

[M8KWR]

Example data

F1 F2 F3 Notes
1 2 4 Test Notes
1 2 4 No Notes here
1 2 4 May be some here
2 3 2 NOtes
2 3 2 NOtes NOtes
3 3 3 NOtes



So if i was to send 1, 2 and 4 to the stored procedure, it should return

Test Notes
No Notes here
May be some here

HTH

 

[gmmastros]

You could write a stored procedure like this:

Create Procedure ConcatenateRows
    @F1 Int,
    @F2 Int,
    @F3 Int
As 
SET NOCOUNT ON

Declare @Output VarChar(8000)

Set @Output = ''

Select @Output = @Output + Notes + Char(13) + Char(10)
From   YourTableName
Where  F1 = @F1
       And F2 = @F2
       And F3 = @F3
       And Notes Is Not NULL

If Right(@Output, 2) = Char(13) + Char(10)
    Set @Output = Left(@Output, Len(@Output)-2)

Select @Output As ConcatenatedRows

To call the procedure...

Exec ConcatenateRows 1,2,4

If you run this in a query window, you may not see the 'rows' because in "grid" view, carriage returns and line feeds are not shown. What you can do is show the results in text mode. To do this:

Right click in the query window
Click "Results To" -> "Results To Text"

When you run the query now, you will see the data separated in to rows.

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[markros]

Alternatively, and I think this may be better suited for your needs, you can write this as a scalar-valued function. The benefit of using a function is that you can use it in bigger select statements.

In fact, using a function was the most commonly known solution for this problem in SQL Server 2000. In SQL Server 2005 and up more commonly accepted solution is to use FOR XML PATH('') I believe it was first introduced by some Russian guy - but I forgot the exact history of this solution right now.