September 24, 2010 at 8:09 am
Hello,
I have created a stored procedure in SQL 2005. WHen I run it as a script, it completes. Changing it to a PROC is driving me nuts. It does not seem to be working when I execute it. Can anyone see where I am going wrong? Your help would be greatly appreciated.
USE Datasets
GO
IF EXISTS (SELECT name FROM sysobjects
WHERE name = 'proc_AutoGEAcctMon' AND type = 'P')
DROP PROCEDURE proc_AutoGEAcctMon
GO
-- Create a procedure on a nonexistent table.
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author: <Author,,Name>
-- Create date: <Create Date,,>
-- Description: <Description,,>
-- =============================================
CREATE PROCEDURE proc_AutoGEAcctMon
AS
BEGIN
--**********************************************
-- run in reporting
--**********************************************
-- The input interface should create the ds_GEAcct_Inp_Table
drop table ds_GEAcct_Inp_Table
Select * into ds_GEAcct_Inp_Table
from dbo.ds_GE_Orig_Input_File
-- Change the column names for the GE file
EXEC sp_rename 'ds_GEAcct_Inp_Table.[Column 0]', 'GE_Account_ID', 'COLUMN'
EXEC sp_rename 'ds_GEAcct_Inp_Table.[Column 1]', 'Customer_Business_name', 'COLUMN'
EXEC sp_rename 'ds_GEAcct_Inp_Table.[Column 2]', 'Customer_DBA_Name', 'COLUMN'
EXEC sp_rename 'ds_GEAcct_Inp_Table.[Column 3]', 'Customer_Address_1', 'COLUMN'
EXEC sp_rename 'ds_GEAcct_Inp_Table.[Column 4]', 'Customer_Address_2', 'COLUMN'
EXEC sp_rename 'ds_GEAcct_Inp_Table.[Column 5]', 'Customer_City_Name', 'COLUMN'
EXEC sp_rename 'ds_GEAcct_Inp_Table.[Column 6]', 'Customer_State_Abbreviation', 'COLUMN'
EXEC sp_rename 'ds_GEAcct_Inp_Table.[Column 7]', 'Customer_Zip_code', 'COLUMN'
EXEC sp_rename 'ds_GEAcct_Inp_Table.[Column 8]', 'Customer_Phone', 'COLUMN'
EXEC sp_rename 'ds_GEAcct_Inp_Table.[Column 9]', 'GE_DataField_1', 'COLUMN'
EXEC sp_rename 'ds_GEAcct_Inp_Table.[Column 10]', 'GE_DataField_2', 'COLUMN'
--########################################################################
--CREATE AND FILL CLEAN FIELDS
--########################################################################
alter table ds_GEAcct_Inp_Table
add [record_id] [T_lrgid] IDENTITY (1, 1) NOT NULL
,clean_name1 varchar(500) null
,clean_name2 varchar(500) null
,clean_extra_name varchar(500) null
,clean_address1 varchar(500) null
,clean_address2 varchar(500) null
,clean_city1 varchar(500) null
,clean_state1 varchar(500) null
,clean_phone1 varchar(500) null
update dbo.ds_GEAcct_Inp_Table
set
clean_name1 = Customer_Business_Name
,clean_name2= Customer_DBA_Name
,clean_address1 = Customer_Address_1
,clean_address2 = Customer_Address_2
,clean_city1= Customer_City_name
,clean_state1 = Customer_State_abbreviation
,clean_phone1 = dbo.udf_clean_phone(Customer_Phone)
from dbo.ds_GEAcct_Inp_Table
--****Create the Clean_Table_File ***********
drop table ds_clean_Table
select record_id, clean_name1, clean_name2, clean_extra_name,
clean_address1, clean_address2,
clean_city1,
clean_state1,
clean_phone1
--clean_ssn
into ds_clean_Table
from ds_GEAcct_Inp_Table
--*********************************************************************
-- create table of counts for number of input columns of each type (i.e. 2 input names, cntName = 2)
--
drop table ds_Acct_counts
create table ds_Acct_counts
(
cntName int NULL,
cntAddr int NULL,
cntCity int NULL,
cntState int NULL,
cntPhone int NULL
)
Insert into ds_Acct_counts values (0,0,0,0,0)
Update dbo.ds_Acct_counts
SET cntName =
CASE When ((select count(*) from ds_clean_Table where clean_Name2 is not NULL) > 0)
Then 2 else 1 end,
cntAddr =
case when (select count(*) from ds_clean_Table where clean_Address2 is not NULL
and clean_Address2 <> '') > 0 then 2
else 1 end,
cntCity =
case when (select count(*) from ds_clean_Table where clean_City1 is not NULL
and clean_City1 <> '') > 0 then 1
else 0 end,
cntState =
case when (select count(*) from ds_clean_Table where clean_State1 is not NULL
and clean_State1 <> '') > 0 then 1
else 0 end,
cntPhone =
case when (select count(*) from ds_clean_Table where clean_Phone1 is not NULL
and clean_Phone1 <> '') > 0 then 1
else 0 end
alter table ds_clean_Table
add clean_parseName1 char (255) NULL,
parseName1 char(255) NULL
If (select cntName from ds_Acct_counts) = 2
alter table ds_clean_Table
add clean_parseName2 char(255) NULL,
parseName2 char (255) NULL
alter table ds_clean_Table
add clean_parseExtraName char(255) NULL,
parseExtraName char(255) NULL
-- select * from ds_clean_Table
END
GO
--Clean name1 and name2
Execute dbo.proc_cleanNames
GO
September 24, 2010 at 9:22 am
Are you getting an error or is there missing/incorrect data?
----------------------------------------------------The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood... Theodore RooseveltThe Scary DBAAuthor of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd EditionProduct Evangelist for Red Gate Software
September 24, 2010 at 9:26 am
I get 'Command(s) completed successfully.' It completes right away and does nothing.
September 24, 2010 at 9:29 am
i see the proc proc_AutoGEAcctMon
get created, but never called; down towards the end you call a procedure with Execute dbo.proc_cleanNames, but not proc_AutoGEAcctMon
; could that be the issue? forgetting to call the newly created procedure?
Lowell
September 24, 2010 at 9:43 am
Duh!!!
Thanks. I am kind of new to this. I didn't realize that the create only 'Creates'.
September 24, 2010 at 9:53 am
Good catch Lowell. I didn't see that at all.
----------------------------------------------------The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood... Theodore RooseveltThe Scary DBAAuthor of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd EditionProduct Evangelist for Red Gate Software
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply