February 23, 2009 at 7:44 am
That takes care of the Replace error. I have renamed dbo.numbers to user.numbers, as I have not been able to crated dbo.numbers. Now I am getting Error 207: Invalid column name 'ID'. Invalid column name 'ID'
will this work with the numbers table as user.Numbers? does it have to be dbo.Numbers?
If I rename user.numbers back to dbo.numbers, the procedure will save correctly but will not run - no dbo.numbers exists.
My user.Numbers table has the following column headers - Number, PlaceHolder
I used the code you provided:
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
and changed the dbo.numbers to user.numbers
February 23, 2009 at 7:51 am
Should work just fine with user.Numbers.
I'm not sure why it's giving you an error about ID not being a valid column. Can you copy-and-paste the query into the forum?
- 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 23, 2009 at 8:48 am
Here is the stored Procedure
CREATE PROCEDURE .[ACTIVE_IDs_LOCATION] (@LocationName varchar(30), @IDs varchar(500)) AS
-- Generally a good idea to have this in procs
set nocount on;
-- Final select
SELECT
LOCATION,
LOB,
TT_INFO,
Right('0' + Convert(VarChar(2), Month(START_DATE)), 2) ++ '/' ++ Right('0' + Convert(VarChar(2), DAY(START_DATE)), 2) ++ '/' ++ Right('0' + Convert(VarChar(4), year(START_DATE)), 4) AS [START_DATE],
CLASS_NAME,
CASE
WHEN LOA = 'L' THEN 'Y'
WHEN LOA = 'P' THEN 'Y'
ELSE NULL
END AS LOA,
LNAME,
FNAME,
MI,
EMPLOYEE_ID,
POSITION,
CUID,
ATTUID,
CMS_ID,
IEX_ID,
UNIVERSITY,
LOGIN,
OUTLOOK_EMAIL,
EMAIL_SEND,
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 user.Numbers
WHERE number <= LEN(@IDs)
AND SUBSTRING(' ' + @IDs, number, 1) = ' ' )
ORDER BY
LNAME,
FNAME;
GO
February 23, 2009 at 8:55 am
Is there an ID column in your view? If not, what is that column called?
- 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 23, 2009 at 9:18 am
No, that's my whole problem. the columns that appear in the view are what @IDs is set to. what I am attempting to do is have the values for @IDs appear in the SELECT in place of ID.
@IDs id different depending on the location = @LocationName (there are 14 different locations). I am trying to have one stored procedure for all locations.
My goal is to have the the values for @IDs parsed and pleaced into the select statement
ie when @IDs = ID1, ID2, ID3 the ID would be replaced by them in the select statement.
ID does not appear as a column in the view.
SELECT
LOCATION,
EMPLOYEE_ID,
POSITION,
TRAINING,
OUTLOOK_EMAIL,
TL,
MO,
ID
should be parsed as:
SELECT
LOCATION,
EMPLOYEE_ID,
POSITION,
TRAINING,
OUTLOOK_EMAIL,
TL,
MO,
ID1,
ID2,
ID3
Does that make sense?
Dan
February 23, 2009 at 9:24 am
You can't really do that as part of a view. You could do it in a proc with dynamic SQL, but a view doesn't have variable column names.
- 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 23, 2009 at 12:10 pm
I am trying to create a procedure to pull the ID information based on LocationName from a view.
It is just the procedure I want to have the vaiables - the view is not being changed. If it can't be done i'll have to live with what i already have.
Thanks for all you help!
Dan
Viewing 7 posts - 16 through 22 (of 22 total)
You must be logged in to reply to this topic. Login to reply