July 19, 2019 at 7:22 pm
Hi,
I am working on a already written stored procedure and need to understand what exactly it means:
Here is the script:
set @query =' if object_id (lib.dbo.ABC )is not null drop table lib.dbo.ABC
select column1, column,....column n into lib.dbo.ABC from '+ABC+.'
July 19, 2019 at 7:23 pm
As per my understanding if table ABC already exist in a defined library it will delete it however my question is why it is giving me an error if table does not exist at all.
July 19, 2019 at 7:23 pm
Hi, I am working on a already written stored procedure and need to understand what exactly it means: Here is the script: set @query =' if object_id (lib.dbo.ABC )is not null drop table lib.dbo.ABC select column1, column,....column n into lib.dbo.ABC from '+ABC+.'
Do you know what an "object_id" is?
To cut to the chase, the object_id in this query is being checked for NULL. If the object (table, in this case) does exist, then it is dropped. Either way, the SELECT/INTO is reading from a table into a non-existent table, which first creates the table and then populates it with the result of the SELECT.
The key here is that the query code isn't actually being executed here. It's being stored in the @query variable for execution later.
The trouble here is that they're dropping table ABC if it exists and then using it as the source to recreate it.
Are you sure this query is actually the way it is or did you make a mistake when you tried to simplify it for this post???
--Jeff Moden
Change is inevitable... Change for the better is not.
July 19, 2019 at 8:08 pm
There are so many issues with this code, it's hard to know where to start. Since you failed to provide the error message, it's even more difficult.
ABC in your static portion.FROM clause.FROM clause.@.ABC refers to a column or table, it's invalid in a SET clause.SET clause.Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
July 22, 2019 at 7:43 pm
It was a really long code and i couldn't copy the code over here.
But here is the actual script :
ALTER PROCEDURE [dbo].[SP_SBC_CY]
AS
BEGIN
SET NOCOUNT ON;
declare @CYMinus1Table varchar(50), @CYMinus2Table varchar(50), @CYMinus3Table varchar(50)
set @CYMinus1Table = 'Accesstest.dbo.[ACAR Historical Data_' + (select cast(year(getdate()) - 2 as varchar(4))) + ']'
set @CYMinus2Table = 'Accesstest.dbo.[ACAR Historical Data_' + (select cast(year(getdate()) - 3 as varchar(4))) + ']'
set @CYMinus3Table = 'Accesstest.dbo.[ACAR Historical Data_' + (select cast(year(getdate()) - 4 as varchar(4))) + ']'
declare @query varchar(max)
set @query = '
if object_id(''AccessTest.dbo.CYMinus1Table'') is not null drop table AccessTest.dbo.CYMinus1Table
select
[Company Number]
,[Company Name]
,[Total Equity] as TotalEquity_CYMinus1
,[Net Income] as NetIncome_CYMinus1
into AccessTest.dbo.CYMinus1Table
from ' + @CYMinus1Table + '
if object_id(''AccessTest.dbo.CYMinus2Table'') is not null drop table AccessTest.dbo.CYMinus2Table
select
[Company Number]
,[Company Name]
,[Total Equity] as TotalEquity_CYMinus2
,[Net Income] as NetIncome_CYMinus2
into AccessTest.dbo.CYMinus2Table
from ' + @CYMinus2Table + '
if object_id(''AccessTest.dbo.CYMinus3Table'') is not null drop table AccessTest.dbo.CYMinus3Table
select
[Company Number]
,[Company Name]
,[Total Equity] as TotalEquity_CYMinus3
,[Net Income] as NetIncome_CYMinus3
into AccessTest.dbo.CYMinus3Table
from ' + @CYMinus3Table + ''
exec (@query)
July 22, 2019 at 7:44 pm
Also, this is just part of the code and when I am running the complete Stored procedure I am getting this particular error:
Msg 207, Level 16, State 1, Procedure SP_FlagTests_CY, Line 266 Invalid column name 'BaseSolvencyBuffer_CYMinus1'.
Help on this too please.
July 23, 2019 at 3:27 pm
Also, this is just part of the code and when I am running the complete Stored procedure I am getting this particular error: Msg 207, Level 16, State 1, Procedure SP_FlagTests_CY, Line 266 Invalid column name 'BaseSolvencyBuffer_CYMinus1'. Help on this too please.
That column name is not included in the code that you've provided, and there is no way to dynamically produce that column name from the provided code either. The error is occurring elsewhere in your code.
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
July 24, 2019 at 8:11 pm
This was removed by the editor as SPAM
July 25, 2019 at 12:51 pm
Hi I posted this answer before but it appears to be marked as SPAM. Not sure why. This site has an annoying text editor.
What this code is doing.
Accesstest.dbo.[ACAR Historical Data_2017]
Accesstest.dbo.[ACAR Historical Data_2016]
Accesstest.dbo.[ACAR Historical Data_2015]
2. Declares a varchar(max) variable called @query to store dynamic sql
3. Assigns 3 blocks of code to the @query variable. One for each year table.
The 3 blocks inside1 each do similar: Drop the existing 'AccessTest.dbo.CYMinus1Table' table (or ...2, or ...3 table) if it exists and then uses 'select into' to recreate the 'AccessTest.dbo.CYMinus1Table' using the table named variable (defined from the date above date)
Run today this code will:
1) Drop table if exists 'AccessTest.dbo.CYMinus1Table'
2) Insert data from table Accesstest.dbo.[ACAR Historical Data_2017] into AccessTest.dbo.CYMinus1Table
3) Drop table if exists 'AccessTest.dbo.CYMinus2Table'
4) Insert data from table Accesstest.dbo.[ACAR Historical Data_2016] into AccessTest.dbo.CYMinus2Table
5) Drop table if exists 'AccessTest.dbo.CYMinus3Table'
6) Insert data from table Accesstest.dbo.[ACAR Historical Data_2015] into AccessTest.dbo.CYMinus3Table
It appears there is a year missing in this scheme!
Aus dem Paradies, das Cantor uns geschaffen, soll uns niemand vertreiben können
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply