February 17, 2009 at 11:35 am
I am trying to create a stored procedure in MSSQL 2000 that will pull different IDs depending on the selected LocationName. Variables @LocationName and @IDs are defined on an Excel spreadsheet and the Code is run from a Macro. The @LocationName variable, used in the FROM statement, is working correctly. I am having trouble with the @IDs variable which is used in the select statement.
Depending on @LocationName the number of ID returned will be different ie.
@LocationName = 1 @IDs = ID1, ID2, ID3, ID4, ID5
@LocationName = 2 @IDs = ID1, ID3, ID4, ID6
When the procedure is run for @LocationName = 1 my results are correct up to the MO column - the @IDs portion of the select statement will enter the text "ID1, ID2, ID3, ID4, ID5". It does not pull the values from user.vw_PIVOT_ID_DATABASE_ACTIVE for ID1, ID2, ID3, ID4, ID5.
Is it possible to modify the select statement this way? I have 14 @LocationNames with 14 different @IDs and was hoping to use 1 stored Procedure
CREATE PROCEDURE .[ACTIVE_IDs_LOCATION] (@LocationName varchar(30), @IDs varchar(350)) AS
SELECT
LOCATION,
EMPLOYEE_ID,
POSITION,
TRAINING,
OUTLOOK_EMAIL,
TL,
MO,
@IDs
FROM user.vw_PIVOT_ID_DATABASE_ACTIVE
WHERE LOCATION = @LocationName and STATUS_CODE = 'A'
ORDER BY LNAME, FNAME;
GO
This is what the procedure returns
Location EMPLOYEE_IDPOSITION TRAININGLOGINOUTLOOK_EMAILTL MO
1 12345798Cust Serv & Problem Res Rep II12345798ja9201JOHN@att.comTraining TeamTraining ManagerID1, ID2, ID3, ID4, ID5
1 12345799Cust Serv & Problem Res Rep II12345799va5863JANE@att.comTraining TeamTraining ManagerID1, ID2, ID3, ID4, ID5
2 12345800Cust Serv & Problem Res Rep II12345800la947jGEDDY@att.comTerm Team Term Manager ID1, ID3, ID4, ID6
This is what I am looking for, the IDs are made pard of the select statement and their values are returned from user.vw_PIVOT_ID_DATABASE_ACTIVE
Location EMPLOYEE_IDPOSITION TRAINING LOGINOUTLOOK_EMAILTL MO ID1 ID2 ID3 ID4 ID5 ID6
1 12345798Cust Serv & Problem Res Rep II12345798ja9201JOHN@att.comTraining TeamTraining ManagerID1 Value ID2 Value ID3 Value ID4 Value ID5 Value
1 12345799Cust Serv & Problem Res Rep II12345799va5863JANE@att.comTraining TeamTraining ManagerID1 Value ID2 Value ID3 Value ID4 Value ID5 Value
2 12345800Cust Serv & Problem Res Rep II12345800la947jGEDDY@att.comTerm Team Term Manager ID1 Value ID3 Value ID4 ValueID6 Value
February 17, 2009 at 12:48 pm
When you put a variable in the SELECT Clause all the Server knows to do is evaluate the variable and output it's contents...
Select @IDs becomes the exact same thing as Select 'ID1,ID2,ID3,etc.'
SQL Server can only treat this as a string unless you tell it to do something else.
I would imagine you already have what ID's go with what location stored in a table somewhere. You need to join to that table and retrieve that data, once you do you can use pivot or any of the other windowing functions to get the data formatted how you want it.
If you post some sample data and DDL you may find you get faster more accurate answers.
See the following for help on how to do just that. Forum Etiquette: How to post data/code on a forum to get the best help [/url]
-Luke.
February 17, 2009 at 12:59 pm
What you need to do is use a "string parser" to break the IDs into a table instead of a row of comma-separated values.
There are several of those in the scripts section of this site. The best ones use a Numbers table (some people call it a Tally table).
Then use "in (select value from stringparserfunction(@IDs))" in your Where clause, and you'll have what you need.
Does that help enough, or am I pulling you in over your head?
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
February 17, 2009 at 1:37 pm
GSquared - over my head. :crying:
February 18, 2009 at 8:27 am
Here's a high-performance way to accomplish what I'm talking about. Doesn't use a Numbers table, instead uses XML, which gets the same performance. Either one will work, but I don't know if you have a Numbers table, so I'm using this one for now.
Edit: XML doesn't show in the forum, so I'm attaching the two queries as text files.
What the proc does is take the list of IDs, provided as a comma-separated list, and turn them into a simple XML string. Then, it uses the XML nodes function to break that apart into rows, and the query and value functions to pull the numbers out of that.
To see how the pieces of that work, try the attached demo.
That shows you how each piece of it works. Run that in Management Studio, and you'll see each step along the way.
Does that help?
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
February 18, 2009 at 8:35 am
If your proc actually gets a list that looks like "ID1,ID2,ID3" as opposed to "1,2,3", then you'll need to change the "replace" part of the XML string to:
replace(@IDs, ',ID', '" /> <row x="')
If it's just "1,2,3", the it'll need to be:
replace(@IDs, ',', '" /> <row x="')
Okay?
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
February 18, 2009 at 1:05 pm
When I check the code syntax in Enterprise Manager I get the following error: Error 170: Line 48: Incorrect syntax near '.'.
Line 48 is
(select x.y.query('.').value('(/row/@x)[1]', 'int')
is x.y.query to be replaced with something?
I am looking for my select portion of the procedure to end up as
SELECT
LOCATION,
EMPLOYEE_ID,
POSITION,
TRAINING,
OUTLOOK_EMAIL,
TL,
MO,
ID1,
ID2,
ID3,
ID4,
ID5
FROM ...
Where the ID1, ID2, ID3, ID4, ID5 portion may change depending on what @IDs is assigned to?
still confused
Dan - noob
February 18, 2009 at 1:10 pm
i think either your server is SQL 2000, or the database's compatibility level is still sitting at 80 instead of 90. the xquery is a 2005/90 feature.
change your compatibility level and the error should go away.
Lowell
February 18, 2009 at 1:15 pm
Are you using SQL 2000? I assumed 2005, because of the forum the question was posted in.
If so, then you won't be able to use the XML version of the string parser. That's a 2005 feature.
Here's a version that should work in SQL 2000:
create table dbo.Numbers (
Number int identity (0,1) primary key,
PlaceHolder bit);
go
insert into dbo.Numbers (PlaceHolder)
select null
from dbo.syscolumns;
go
CREATE PROCEDURE .[ACTIVE_IDs_LOCATION]
(@LocationName varchar(30),
@IDs varchar(350))
AS
-- Generally a good idea to have this in procs
set nocount on;
-- Final select
SELECT
LOCATION,
EMPLOYEE_ID,
POSITION,
TRAINING,
OUTLOOK_EMAIL,
TL,
MO,
@IDs
FROM
user.vw_PIVOT_ID_DATABASE_ACTIVE
WHERE
LOCATION = @LocationName
and
STATUS_CODE = 'A'
and
ID in
-- string parser
(SELECT SUBSTRING(@IDs+' ', number, CHARINDEX(' ', @IDs+' ', number) - number)
FROM dbo.Numbers
WHERE number <= LEN(@IDs)
AND SUBSTRING(' ' + @IDs, number, 1) = ' ' )
ORDER BY
LNAME,
FNAME;
I got the string parser for this from http://www.simple-talk.com, in an article by Robyn Page and Phil Factor: http://www.simple-talk.com/sql/t-sql-programming/the-helper-table-workbench/
Try that, let me know if it works for you.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
February 18, 2009 at 1:26 pm
My mistake - it is SQL 2000 - I do not have access to create dbo.numbers. is this a requred table? can it be any name?
dan = noob
February 18, 2009 at 1:35 pm
It doesn't matter what you name the table. Some people on this site call it Tally. I call it Numbers. I've seen Integers too, I think. Whatever works for you.
If you can't create a table, can you create a function? There are ways to get a user-defined function to split up a list that don't require either XML or a Numbers/Tally/whatever table. They aren't as fast or efficient, but they do get the job done.
If not, we can build a Numbers table as a temp table in the proc. Won't be as fast as a permanent one, but will work, and doesn't require creating anything other than the proc itself.
Numbers tables (by whatever name) are incredibly useful tools. If you can create one, just with a different name, definitely do so. You'll end up using it for a lot of different things.
Let me know which plan is possible for you, and I'll see if I can help you on it.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
February 19, 2009 at 7:42 am
When I add the code:
And @IDs in
-- string parser
(SELECT SUBSTRING(@IDs+' ', number, CHARINDEX(' ', @IDs+' ', number) - number)
FROM NUMBERS
WHERE number <= LEN(@IDs)
AND SUBSTRING(' ' + @IDs, number, 1) = ' ' )
to the WHERE clause I return no data.
The parsed text need to appear as part of the SELECT portion of the query. Possible?
When I run the string parser by itself it is returning the first ID1 twice.
DECLARE @IDs VARCHAR(500)
SELECT @IDs=
'ID1, ID2, ID3, ID4, ID5, ID6, ID7, ID8, ID9, ID10'
SELECT SUBSTRING(@IDs+' ', number, CHARINDEX(' ', @IDs+' ', number) - number)
FROM NUMBERS
WHERE number <= LEN(@IDs)
AND SUBSTRING(' ' + @IDs, number, 1) = ' '
Result
No Column Name
ID1,
ID1,
ID2,
ID3,
ID4,
ID5,
ID6,
ID7,
ID8,
ID9,
ID10
Dan
February 19, 2009 at 8:14 am
SELECT
LOCATION,
EMPLOYEE_ID,
POSITION,
TRAINING,
OUTLOOK_EMAIL,
TL,
MO,
ID
FROM
user.vw_PIVOT_ID_DATABASE_ACTIVE
WHERE
LOCATION = @LocationName
and
STATUS_CODE = 'A'
and
ID in
-- string parser
(SELECT
replace(replace(
SUBSTRING(@IDs+' ', number,
CHARINDEX(' ', @IDs+' ', number) - number),
'ID', ''), ',') as ID
FROM dbo.Numbers
WHERE number <= LEN(@IDs)
AND SUBSTRING(' ' + @IDs, number, 1) = ' ' )
ORDER BY
LNAME,
FNAME;
I added replace functions to clean up the strings. You can either use them on the @IDs variable before you run the string parser, or inside the string parser like this. Either one should work.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
February 20, 2009 at 7:51 am
I now get: Error 174: The replace function requires 3 arguments.
Note: I have created a user.NUMBERS table
Dan
February 20, 2009 at 11:56 am
You're right. That's what I get for not testing the way I should.
Replace this line:
'ID', ''), ',') as ID
with:
'ID', ''), ',', '') as ID
Just tested that part, and it worked.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
Viewing 15 posts - 1 through 15 (of 22 total)
You must be logged in to reply to this topic. Login to reply
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy