Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

Register Log in
  • Congratulations bkrike on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Insert into statement from SP will not work

Status
Not open for further replies.
gdkz

gdkz

Programmer
Nov 21, 2002
44
US
I have a insert into statement that when run from a stored procedure does not return a record set. But if I extract it and manually run the code, I return a record set.

INSERT INTO VCShrpy..EmployeeFinal
SELECT
'T' AS Record_Type,
'A' AS Command,
DBShrpn..employee.emp_id AS Employee_ID,
ltrim(rtrim(DBShrpn..individual.phone_1_area_city_code))+
ltrim(rtrim(DBShrpn..individual.phone_1_nbr)) AS Employee_Phone,
SPACE(1) AS Reserved1,
UPPER(DBShrpn..employee.emp_display_name) AS Employeel_Name,
SPACE(1) AS Reserved2,
VCShrpy..SEMS_Locations.SEMS_Location,
'555' AS Work1_Class1, -- Classification code
'000' AS Work1_Class2,
'000' AS Work1_Class3,
'NYYYYYN' AS Work1_Days,
'0000' AS Work1_Start,
'0000' AS Work1_End,
'001' AS Employee_Calendar,
DBShrpn..individual.addr_1_street_or_pob_1 AS Employee_Address_1,
DBShrpn..individual.addr_1_street_or_pob_2 AS Employee_Address_2,
DBShrpn..individual.addr_1_city_name AS Employee_City,
DBShrpn..individual.addr_1_country_sub_entity_code AS Employee_State,
DBShrpn..individual.addr_1_postal_code AS Employee_Zip,
'000000' AS Expire1,
'000000' AS Expire2,
CASE
WHEN CONVERT(varchar(10),DBShrpn..emp_assignment.end_date,12) = '991231' THEN
'000000'
ELSE
CONVERT(varchar(10),DBShrpn..emp_assignment.end_date,12)
END
AS Expire3,
'000000' AS Work2_Location,
'000' AS Work2_Class1,
'000' AS Work2_Class2,
'000' AS Work2_Class3,
SPACE(7) AS Work2_Days,
SPACE(4) AS Work2_Start,
SPACE(4) AS Work2_End,
'000000' AS Work3_Location,
'000' AS Work3_Class1,
'000' AS Work3_Class2,
'000' AS Work3_Class3,
SPACE(7) AS Work3_Days,
SPACE(4) AS Work3_Start,
SPACE(4) AS Work3_End,
ltrim(rtrim(DBShrpn..individual.phone_1_nbr)) AS Employee_Special_ID,
'0' AS Language,
SPACE(24) AS Budget,
SPACE(1) AS Employee_Ethnicity,
DBShrpn..individual_personal.sex_code AS Employee_Gender,
SPACE(1) AS Reserved3,
SPACE(1) AS Reserved4,
SPACE(2) AS Reserved5,
'000000' AS Start_Date,
SPACE(4) AS Reserved6,
SPACE(2) AS Reserved7,
CONVERT(varchar(10),DBShrpn..employee.original_hire_date,12) AS Employee_Hire_Date,
CONVERT(varchar(10),DBShrpn..individual_personal.birth_date,12) AS Employee_Birth_Date,
SPACE(36) AS Reserved8
FROM
DBShrpn..employee LEFT JOIN DBShrpn..emp_assignment
ON (DBShrpn..employee.emp_id = DBShrpn..emp_assignment.emp_id )
LEFT JOIN DBShrpn..individual
ON ( DBShrpn..employee.individual_id = DBShrpn..individual.individual_id)
LEFT JOIN DBShrpn..individual_personal
ON ( DBShrpn..employee.individual_id = DBShrpn..individual_personal.individual_id)
LEFT JOIN DBShrpn..position

ON ( DBShrpn..emp_assignment.job_or_pos_id = DBShrpn..position.pos_id )
LEFT JOIN VCShrpy..EmployeeSEMS
ON(DBShrpn..employee.emp_id = VCShrpy..EmployeeSEMS.Employee_ID)
LEFT JOIN VCShrpy..SEMS_Locations
ON( DBShrpn..emp_assignment.organization_unit_name = SUBSTRING(SEMS_Location,4,3))
WHERE
DBShrpn..emp_assignment.job_or_pos_id <> 'CONVPOS'
AND
DBShrpn..position.end_date >= '08/07/2003'
AND
DBShrpn..emp_assignment.eff_date <= '08/07/2003'
AND
DBShrpn..emp_assignment.begin_date <= '08/07/2003'
AND
DBShrpn..emp_assignment.end_date > '08/07/2003'
AND
DBShrpn..emp_assignment.next_eff_date > '08/07/2003'
AND
DBShrpn..position.eff_date <= '08/07/2003'
AND
DBShrpn..position.next_eff_date > '08/07/2003'
AND
DBShrpn..position.eff_date <= '08/07/2003'
AND
DBShrpn..position.next_eff_date > '08/07/2003'
AND
SUBSTRING(DBShrpn..position.pos_id,4,1) = '3'
AND
DBShrpn..position.pos_id NOT LIKE '851%'
AND
DBShrpn..position.pos_id NOT LIKE '852%'
AND
DBShrpn..position.pos_id NOT LIKE '853%'
AND
DBShrpn..position.pos_id NOT LIKE '854%'
AND

prime_assignment_ind = 'Y'

AND
Employee_ID = NULL -- Employees not in sems

Why will this not work from a stored procedure?

Thanks in Advance!
Greg
 
Sort by date Sort by votes
You did an insert not a select, why would you expect anything to return?
 
Upvote 0 Downvote
Sorry in my haste, I meant to say it inserts nothing when I execute the sp. But when I extract the code and run in manually, it inserts approx. 210 records, which is what I want it to.

Thanks in Advance!
Greg
 
Upvote 0 Downvote
Hi,

Chane ur SP Like this and see... I have changed the condintion where it checks for Employee_id = NULL it should be Employee_ID IS NULL as u cannot compare a field to a NULL value

INSERT INTO VCShrpy..EmployeeFinal
SELECT
'T' AS Record_Type,
'A' AS Command,
DBShrpn..employee.emp_id AS Employee_ID,
ltrim(rtrim(DBShrpn..individual.phone_1_area_city_code))+
ltrim(rtrim(DBShrpn..individual.phone_1_nbr)) AS Employee_Phone,
SPACE(1) AS Reserved1,
UPPER(DBShrpn..employee.emp_display_name) AS Employeel_Name,
SPACE(1) AS Reserved2,
VCShrpy..SEMS_Locations.SEMS_Location,
'555' AS Work1_Class1, -- Classification code
'000' AS Work1_Class2,
'000' AS Work1_Class3,
'NYYYYYN' AS Work1_Days,
'0000' AS Work1_Start,
'0000' AS Work1_End,
'001' AS Employee_Calendar,
DBShrpn..individual.addr_1_street_or_pob_1 AS Employee_Address_1,
DBShrpn..individual.addr_1_street_or_pob_2 AS Employee_Address_2,
DBShrpn..individual.addr_1_city_name AS Employee_City,
DBShrpn..individual.addr_1_country_sub_entity_code AS Employee_State,
DBShrpn..individual.addr_1_postal_code AS Employee_Zip,
'000000' AS Expire1,
'000000' AS Expire2,
CASE
WHEN CONVERT(varchar(10),DBShrpn..emp_assignment.end_date,12) = '991231' THEN
'000000'
ELSE
CONVERT(varchar(10),DBShrpn..emp_assignment.end_date,12)
END
AS Expire3,
'000000' AS Work2_Location,
'000' AS Work2_Class1,
'000' AS Work2_Class2,
'000' AS Work2_Class3,
SPACE(7) AS Work2_Days,
SPACE(4) AS Work2_Start,
SPACE(4) AS Work2_End,
'000000' AS Work3_Location,
'000' AS Work3_Class1,
'000' AS Work3_Class2,
'000' AS Work3_Class3,
SPACE(7) AS Work3_Days,
SPACE(4) AS Work3_Start,
SPACE(4) AS Work3_End,
ltrim(rtrim(DBShrpn..individual.phone_1_nbr)) AS Employee_Special_ID,
'0' AS Language,
SPACE(24) AS Budget,
SPACE(1) AS Employee_Ethnicity,
DBShrpn..individual_personal.sex_code AS Employee_Gender,
SPACE(1) AS Reserved3,
SPACE(1) AS Reserved4,
SPACE(2) AS Reserved5,
'000000' AS Start_Date,
SPACE(4) AS Reserved6,
SPACE(2) AS Reserved7,
CONVERT(varchar(10),DBShrpn..employee.original_hire_date,12) AS Employee_Hire_Date,
CONVERT(varchar(10),DBShrpn..individual_personal.birth_date,12) AS Employee_Birth_Date,
SPACE(36) AS Reserved8
FROM
DBShrpn..employee LEFT JOIN DBShrpn..emp_assignment
ON (DBShrpn..employee.emp_id = DBShrpn..emp_assignment.emp_id )
LEFT JOIN DBShrpn..individual
ON ( DBShrpn..employee.individual_id = DBShrpn..individual.individual_id)
LEFT JOIN DBShrpn..individual_personal
ON ( DBShrpn..employee.individual_id = DBShrpn..individual_personal.individual_id)
LEFT JOIN DBShrpn..position

ON ( DBShrpn..emp_assignment.job_or_pos_id = DBShrpn..position.pos_id )
LEFT JOIN VCShrpy..EmployeeSEMS
ON(DBShrpn..employee.emp_id = VCShrpy..EmployeeSEMS.Employee_ID)
LEFT JOIN VCShrpy..SEMS_Locations
ON( DBShrpn..emp_assignment.organization_unit_name = SUBSTRING(SEMS_Location,4,3))
WHERE
DBShrpn..emp_assignment.job_or_pos_id <> 'CONVPOS'
AND
DBShrpn..position.end_date >= '08/07/2003'
AND
DBShrpn..emp_assignment.eff_date <= '08/07/2003'
AND
DBShrpn..emp_assignment.begin_date <= '08/07/2003'
AND
DBShrpn..emp_assignment.end_date > '08/07/2003'
AND
DBShrpn..emp_assignment.next_eff_date > '08/07/2003'
AND
DBShrpn..position.eff_date <= '08/07/2003'
AND
DBShrpn..position.next_eff_date > '08/07/2003'
AND
DBShrpn..position.eff_date <= '08/07/2003'
AND
DBShrpn..position.next_eff_date > '08/07/2003'
AND
SUBSTRING(DBShrpn..position.pos_id,4,1) = '3'
AND
DBShrpn..position.pos_id NOT LIKE '851%'
AND
DBShrpn..position.pos_id NOT LIKE '852%'
AND
DBShrpn..position.pos_id NOT LIKE '853%'
AND
DBShrpn..position.pos_id NOT LIKE '854%'
AND

prime_assignment_ind = 'Y'

AND
Employee_ID IS NULL

Hope it helps

Sunil
 
Upvote 0 Downvote
Could be a permissions problem if one or more of the referenced objects are not owned by the same owner as the stored procedure. From Book online:

&quot;A user who creates a view or stored procedure that depends on an object owned by another user must be aware that any permissions he or she grants depend on the permissions allowed by the other owner.&quot;

 
Upvote 0 Downvote
That worked!! Thanks so much, you are a lifesaver. Overlooked that = null. Thanks again for your expertise.

Thanks in Advance!
Greg
 
Upvote 0 Downvote
Status
Not open for further replies.

Similar threads

Cpreston
  • Locked
  • Question
Group results into one row 3
2
Replies
37
Views
2K
Chris Miller
Chris Miller
peac3
  • Locked
  • Question
split one row into multiple rows... help pls 2
Replies
12
Views
2K
Chris Miller
Chris Miller
WomanPro
  • Locked
  • Question
Insert into query adds records to unreadable format, how do I change that? 1
Replies
5
Views
609
Chris Miller
Chris Miller
peac3
  • Locked
  • Question
Consolidate multiple rows into single row 1
Replies
4
Views
1K
Chris Miller
Chris Miller
grnzbra
  • Locked
  • Question
Create Function Within IF statement. 1
Replies
4
Views
1K
mikrom
mikrom

Part and Inventory Search

Sponsor

Back
Top