Stored Procedure help please - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

Stored Procedure help please

Author

Topic

FSURob19
Starting Member

5 Posts

Posted - 2004-07-15 : 10:09:11

I have not created to many stored procedures so this might be a pretty easy question for some of you.

Ok I have created a report that basically queries only 2 tables. The first table has th fields I am looking for + 1 id column. Now the DB design for this id is wrong but I have to work with it. The id is structred with a multiple combination of ids.

Example:
ID: -0203-
OR
ID: -0203-0450-0980-0409-

This id needs to match with the second table to retrieve a desc column. Now the id in the second table only has the first 2 digits in each id pair from table 1. So with the second example you would need to check for the first 2 digits in each pair and match them up to the second table.

Example:
ID : -0203-0450-0980-0409- would equate to needing to loop though the string to grap 02,04,09,04.

So one row might have one desc or more depending on how many pairs are in the first table's row.

I thought about moving this into 2 stored procedures but the user has the ability to asc and desc the columns with teh desc field being one of those fields.

Hope that made sense and any help would be appreciated.

Kristen
Test

22859 Posts

Posted - 2004-07-15 : 10:24:03

There are functions discussed on SQLTeam which could split your "-0203-0450-0980-0409-" into a recordset which you could then join to your second table


SELECT *
FROM   dbo.MyTable2 AS T2
       JOIN dbo.MySplitterFunction('-0203-0450-0980-0409-')
          ON LEFT(SpliterValue, 2) = T2.ID

Searching for CSV / Splitter should find the relevant topics

Kristen

X002548
Not Just a Number

15586 Posts

Posted - 2004-07-15 : 10:24:18

EDIT: Damn, sniped again

OK, I'm confused about the multiple ID's per line thing...

But you'll need to use CHARINDEX and SUBSTRING...maybe a FUNCTION


DECLARE @x varchar(50)
SELECT @x = '-0203-0450-0980-0409-'
SELECT SUBSTRING(@x,2,2)
	,SUBSTRING(@x,CHARINDEX('-',@x,2)+1,2)
	,SUBSTRING(@x,CHARINDEX('-',@x,CHARINDEX('-',@x,2)+1)+1,2)

Brett

8-)

FSURob19
Starting Member

5 Posts

Posted - 2004-07-15 : 13:09:53

Thanks for the info I will check it out.

What I meant by the multiple id's is when you do the sql to grab the rows you need, one row can contain either one or multiple sets. One set being -0908-, multiple -0908-0304-0202-. So since the id in the second table is only the first two digits to match it with a desc you have to step though the string and grab desc for each 2 digits.

Not sure if that cleared anything up or not =).

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources