September 23, 2016 at 10:37 am
I have SQL server SP which moves the data from a temporary grid to the main grid. The main purpose of this proc is to implement the upload process to move data from the temporary grid and when moved to main grid delete the rows from the temp grid. Hope I have explained the requirement clearly. I have done this so far, but I ended up getting few errors. Is my approach correct or am I deviating from the destination. Can someone please suggest a workaround fro this.
Thanks
Code:
CREATE PROCEDURE [hsip].[MDT]
(
@cRegion CHAR(2) = '00',
@cState_Code CHAR(2) = '00',
@nFY NUMERIC(4,0) = 0,
@nREPORT_ID NUMERIC(2,0) = 0,
@nSECTION_ID NUMERIC(2,0) = 0,
@nSUBSECTION_ID NUMERIC(2,0) = 0,
@nDISPLAY_NUMBER NUMERIC(38,0) = 0,
@nQUESTION_NUMBER NUMERIC(38,0) = 0,
@nQUESTION_PART_NUMBER NUMERIC(38,0) = 0,
@sUSER_ID VARCHAR(25) = NULL,
@nIsMerge char ='N'
)
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
declare @starting_row_index as integer=0,
@ncolumn_index as integer=0,
@sresponse_string as varchar(4000),
@srepsonse_stringm as varchar(4000),
@response_stringo as varchar(4000),
@nFY_ST_QUESTION_DTL_TABLE_ID as numeric(38,0),
select qd.FY_ST_QUESTION_DTL_TABLE_ID
from FY_ST_QUESTION_DETAIL qd inner join FY_ST_QUESTION_INFO qi
on qd.FY_ST_QUESTION_INFO_TABLE_ID = qi.FY_ST_QUESTION_INFO_TABLE_ID
where qi.region = @cRegion
and qi.state_code = @cState_Code
and qi.fy = @nFY
and qi.report_id = @nREPORT_ID
and qi.section_id = @nSECTION_ID
and qi.subsection_id = @nSUBSECTION_ID
and qi.display_number = @nDISPLAY_NUMBER
and qi.QUESTION_NUMBER = @nQUESTION_NUMBER
and ( QI.REPORTER_ID = @sUSER_ID or
QI.DELEGATE_ID = @suser_id or
QI.SUB_DELEGATE_ID = @suser_id )
and QD.QUESTION_PART_NUMBER = @nQUESTION_PART_NUMBER;
set @ncolumn_index=
case
when (@nREPORT_ID = 1 and @nQUESTION_NUMBER = 21) then 17
when (@nREPORT_ID = 1 and @nQUESTION_NUMBER = 32) then 16
when (@nREPORT_ID = 3 and @nQUESTION_NUMBER = 11) then 15
when (@nREPORT_ID = 3 and @nQUESTION_NUMBER = 12) then 27
end;
if(@nFY_ST_QUESTION_DTL_TABLE_ID >0)
if(@nIsMerge = 'Y')
select (max(ad.ROW_NUMBER))
from FY_ST_ANSWER_DETAIL as ad
where ad.FY_ST_QUESTION_DTL_TABLE_ID=@nFY_ST_QUESTION_DTL_TABLE_ID;
else
--begin
delete from FY_ST_ANSWER_DETAIL ad
where ad.fy_st_question_dtl_table_id = @nFY_ST_QUESTION_DTL_TABLE_ID;
end;
INSERT INTO FY_ST_ANSWER_DETAIL AD
SELECT UL.FY_ST_QUESTION_DTL_TABLE_ID,UL.REGION,UL.STATE_CODE,UL.FY,
UL.REPORT_ID,UL.SECTION_ID,UL.SUBSECTION_ID,
UL.DISPLAY_NUMBER,UL.QUESTION_NUMBER,UL.QUESTION_PART_NUMBER,
UL.ROW_NUMBER+@starting_row_index,
UL.COLUMN_NUMBER,
UL.LAST_UPDATE_USERID,UL.LAST_UPDATE_TIME,
UL.RESPONSE_STRING
FROM UPLOAD_TEMP_DATA UL
WHERE UL.FY_ST_QUESTION_DTL_TABLE_ID = @nFY_ST_QUESTION_DTL_TABLE_ID
and UL.COLUMN_NUMBER <= @ncolumn_index;
DELETE FROM UPLOAD_TEMP_DATA UL
WHERE UL.FY_ST_QUESTION_DTL_TABLE_ID = @nFY_ST_QUESTION_DTL_TABLE_ID;
--end;
END
GO
September 23, 2016 at 10:49 am
Telling us that you are "getting errors" without telling is what the errors are is most unhelpful ... are we supposed to guess? Please post them.
If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.
September 23, 2016 at 10:59 am
Sorry, @Phil. I forgot to post the errors earlier.
Here they are:
I get errors on the delete statement:
delete from FY_ST_ANSWER_DETAIL ad
where ad.fy_st_question_dtl_table_id = @nFY_ST_QUESTION_DTL_TABLE_ID
and on
SELECT UL.FY_ST_QUESTION_DTL_TABLE_ID,UL.REGION,UL.STATE_CODE,UL.FY,.......
and on
WHERE UL.FY_ST_QUESTION_DTL_TABLE_ID = @nFY_ST_QUESTION_DTL_TABLE_ID; towards the end, last line. It just says incorrect syntax, I think I have used correct syntax.
Thanks
September 23, 2016 at 11:10 am
Make sure you have the correct number of BEGIN and END.
Without details, that's the only thing I could find.
EDIT: Also check your variables declaration.
September 23, 2016 at 11:19 am
OK, this is a start
You have a comma after the declaration of @nFY_ST_QUESTION_DTL_TABLE_ID. Should be a semicolon.
You have a mismatch between begins and ends.
Having an IF and then another IF is awkward. Use this structure instead:
if (Cond1) and (Cond2)
Begin
statements here
End
Else
Begin
statements here
End
You have an INSERT without listing the columns you are inserting to.
You have tried to alias the table you are INSERTing into.
If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.
September 23, 2016 at 11:59 am
dimpythewimpy (9/23/2016)
delete from FY_ST_ANSWER_DETAIL adwhere ad.fy_st_question_dtl_table_id = @nFY_ST_QUESTION_DTL_TABLE_ID
The DELETE statement has two FROM clauses: one mandatory and one optional. The FROM keyword is optional in the mandatory FROM clause and mandatory in the optional FROM clause. You can only assign aliases in the optional FROM clause. You are trying to assign it in the mandatory FROM clause.
The simplest way to correct this is to not use the alias. You can also add the optional FROM clause.
DELETE FROM ad -- This uses the alias defined in the optional FROM clause
FROM FY_ST_ANSWER_DETAIL ad
WHERE ad.fy_st_question_dtl_table_id = @nFY_ST_QUESTION_DTL_TABLE_ID
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
September 23, 2016 at 12:24 pm
@Phil,
Thanks. I have changed my code like this, but still get the errors near both the delete statements and insert statement. For insert, I have 2 tables with same no. of columns with same names.
if(@nFY_ST_QUESTION_DTL_TABLE_ID >0 and @nIsMerge = 'Y')
Begin
select (max(ad.ROW_NUMBER))
from FY_ST_ANSWER_DETAIL as ad
where ad.FY_ST_QUESTION_DTL_TABLE_ID=@nFY_ST_QUESTION_DTL_TABLE_ID;
end
else
begin
delete from FY_ST_ANSWER_DETAIL ad
where ad.fy_st_question_dtl_table_id = @nFY_ST_QUESTION_DTL_TABLE_ID;
end
if(@nFY_ST_QUESTION_DTL_TABLE_ID >0)
begin
insert into FY_ST_ANSWER_DETAIL ad (ad.FY_ST_QUESTION_DTL_TABLE_ID, ad.REGION, ad.STATE_CODE, ad.FY, ad.REPORT_ID, ad.SECTION_ID, ad.SUBSECTION_ID,
ad.DISPLAY_NUMBER, ad.QUESTION_NUMBER, ad.QUESTION_PART_NUMBER, ad.row_number, ad.COLUMN_NUMBER, ad.LAST_UPDATE_USERID, ad.LAST_UPDATE_TIME, ad.RESPONSE_STRING)
select( FY_ST_QUESTION_DTL_TABLE_ID, REGION, STATE_CODE, FY, REPORT_ID, SECTION_ID, SUBSECTION_ID,
DISPLAY_NUMBER, QUESTION_NUMBER, QUESTION_PART_NUMBER, row_number, COLUMN_NUMBER, LAST_UPDATE_USERID, LAST_UPDATE_TIME, RESPONSE_STRING)
from upload_temp_data ud
where ud.FY_ST_QUESTION_DTL_TABLE_ID = @nFY_ST_QUESTION_DTL_TABLE_ID and
ud.COLUMN_NUMBER <= @ncolumn_index;
end
else
begin
DELETE FROM UPLOAD_TEMP_DATA UL
WHERE UL.FY_ST_QUESTION_DTL_TABLE_ID = nFY_ST_QUESTION_DTL_TABLE_ID;
end
END
GO
September 23, 2016 at 12:43 pm
Phil Parkin (9/23/2016)
Telling us that you are "getting errors" without telling is what the errors are is most unhelpful ... are we supposed to guess? Please post them.
Repeating this.
Also, stop aliasing tables that you're deleting from or inserting into.
September 23, 2016 at 1:29 pm
Hello,
Thank you. I removed the aliases from insert. All are errors are gone. Now they are narrowed down to insert statement. It says incorrect syntax near insert.
insert into FY_ST_ANSWER_DETAIL (FY_ST_QUESTION_DTL_TABLE_ID, REGION, STATE_CODE, FY, REPORT_ID, SECTION_ID, SUBSECTION_ID,
DISPLAY_NUMBER, QUESTION_NUMBER, QUESTION_PART_NUMBER, row_number, COLUMN_NUMBER, LAST_UPDATE_USERID, LAST_UPDATE_TIME, RESPONSE_STRING)
select( FY_ST_QUESTION_DTL_TABLE_ID, REGION, STATE_CODE, FY, REPORT_ID, SECTION_ID, SUBSECTION_ID, DISPLAY_NUMBER, QUESTION_NUMBER, QUESTION_PART_NUMBER, row_number, COLUMN_NUMBER, LAST_UPDATE_USERID, LAST_UPDATE_TIME, RESPONSE_STRING)
from upload_temp_data
where FY_ST_QUESTION_DTL_TABLE_ID = @nFY_ST_QUESTION_DTL_TABLE_ID and
COLUMN_NUMBER <= @ncolumn_index;
I think the syntax is correct but, not sure why it complains that there is an error.
Thanks
September 23, 2016 at 1:35 pm
Remove the parenthesis in the column list from the SELECT
September 23, 2016 at 1:42 pm
Thanks Luis. Yaay my code works like a charm now!!!
September 23, 2016 at 1:43 pm
Now the procedure finally works fine. Thanks all for your suggestions
September 24, 2016 at 5:31 am
Good, now maybe ask your boss about sending you on a basic T-SQL course, if you're going to be working with database code a lot.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply