July 27, 2012 at 2:22 pm
//**UPDATE**//
Here is the query WITHOUT CURSOR. I used the LOOP but looks like I have too many temp tables and I need re-create this in a better format. The purpose of this query is ·
*Do all staged tables exist in the source and in the stage database?
*Do all staged columns exist in the source and in the stage database?
*Is the data type of all staged columns the same in the source and stage database?
*Does the tbPlanStartEnd table exist in the source database, if not, create it.
*Is the tbPlanStartEnd table populated in the source database, if not, populate it.
For each of the potential problems, execute a SQL that lists tables that do not meet the condition specified. With the list of tables, with corresponding message to the log table and print the messages to the SQL console.
· For each of the potential problems that can be automatically corrected, make the correction but also insert a message to the log table and print the messages to the SQL console.
If unhandled errors are found, raise an error and tell the user to check the log table or printed messages for details.
At a high level I have accomplished all of that but I need a better format instead of creating 5 seperate temp _tables. Here is my query;
CREATE PROCEDURE usp_PreStageValidation
@SQLog_str1 NVARCHAR(max),
@SQLog_str2 NVARCHAR(max),
@SQLog_str3 NVARCHAR(max),
@SQLog_str4 NVARCHAR(max),
@SQLog_str5 NVARCHAR(max)
AS
BEGIN
DECLARE
@Catalog nvarchar(128)
, @Table nvarchar(128)
, @Column nvarchar(128)
, @Message nvarchar(128)
, @RecCount int
, @index int;
CREATE TABLE #temp_table(
RowId int IDENTITY(1, 1),
Cat nvarchar(128),
Tbl nvarchar(128),
col nvarchar(128)
);
INSERT INTO #temp_table(Cat, Tbl)
EXEC sp_executeSQL @SQLog_str1, N'@AppliCationName VARCHAR(100)', @AppliCationName=@AppliCationName ;
SET @RecCount = @@ROWCOUNT;
SET @index = 1;
WHILE @index <= @RecCount
BEGIN
SELECT @Catalog = Cat, @Table = Tbl
FROM #temp_table
WHERE RowId = @index;
@Message = 'This Stage table does not exist in this SOURCE database';
INSERT INTO syd_LogTable VALUES(@Catalog,@Table,NULL,@Message);
END;
DROP TABLE #temp_table;
--------------------------------------------------------------------------end of #1
IF NOT EXISTS (SELECT * FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_CatALOG = @Catalog AND
TABLE_SCHEMA = 'dbo' AND
TABLE_NAME = 'tbPlanStartEnd' )
BEGIN
INSERT INTO syd_LogTable
VALUES(@Catalog, 'tbPlanStartEnd', NULL, 'This Table is Created Newly');
END;
--------------
IF NOT EXISTS (SELECT * FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_CatALOG = @Catalog AND
TABLE_SCHEMA = 'dbo' AND
TABLE_NAME = 'tbPlanGroup' )
BEGIN
INSERT INTO syd_LogTable
VALUES(@Catalog, 'tbPlanGroup', NULL, 'This Table is Created Newly');
ELSE
IF NOT EXISTS (SELECT * FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_CatALOG = @Catalog AND
TABLE_SCHEMA = 'dbo' AND
TABLE_NAME = 'tbPlanGroup' AND
COLUMN_NAME = 'grp_name' )
BEGIN
INSERT INTO syd_LogTable
VALUES(@Catalog, 'tbPlanGroup', NULL, 'This Table is added with column grp_name');
END;
END;
INSERT INTO #temp_table(Cat, Tbl)
EXEC sp_executeSQL @SQLog_str2, N'@AppliCationName VARCHAR(100)', @AppliCationName=@AppliCationName ;
SET @RecCount = @@ROWCOUNT;
SET @index = 1;
WHILE @index <= @RecCount
BEGIN
SELECT @Catalog = Cat, @Table = Tbl
FROM #temp_table
WHERE RowId = @index;
@Message = 'This Stage table does not exist in this STAGE database';
INSERT INTO syd_LogTable VALUES(@Catalog,@Table,NULL,@Message);
END;
DROP TABLE #temp_table;
--------------------------------------------------------------------------end of #2
INSERT INTO #temp_table(Cat, Tbl, col)
EXEC sp_executeSQL @SQLog_str3, N'@AppliCationName VARCHAR(100)', @AppliCationName=@AppliCationName ;
SET @RecCount = @@ROWCOUNT;
SET @index = 1;
WHILE @index <= @RecCount
BEGIN
SELECT @Catalog = Cat, @Table = Tbl, @Column = col
FROM #temp_table
WHERE RowId = @index;
@Message = 'This Stage Column does not exist in this Table of this SOURCE database';
INSERT INTO syd_LogTable VALUES(@Catalog,@Table,@Column,@Message);
END;
DROP TABLE #temp_table;
--------------------------------------------------------------------------end of #3
INSERT INTO #temp_table(Cat, Tbl, col)
EXEC sp_executeSQL @SQLog_str4, N'@AppliCationName VARCHAR(100)', @AppliCationName=@AppliCationName ;
SET @RecCount = @@ROWCOUNT;
SET @index = 1;
WHILE @index <= @RecCount
BEGIN
SELECT @Catalog = Cat, @Table = Tbl, @Column = col
FROM #temp_table
WHERE RowId = @index;
@Message = 'This Stage Column does not in this Table of this STAGE database';
INSERT INTO syd_LogTable VALUES(@Catalog,@Table,@Column,@Message);
END;
DROP TABLE #temp_table;
--------------------------------------------------------------------------end of #4
INSERT INTO #temp_table(Cat, Tbl, col)
EXEC sp_executeSQL @SQLog_str5, N'@AppliCationName VARCHAR(100)', @AppliCationName=@AppliCationName ;
SET @RecCount = @@ROWCOUNT;
SET @index = 1;
WHILE @index <= @RecCount
BEGIN
SELECT @Catalog = Cat, @Table = Tbl, @Column = col
FROM #temp_table
WHERE RowId = @index;
@Message = 'The Data Type of this Stage Column of this Table is NOT the same as in this SOURCE database';
INSERT INTO syd_LogTable VALUES(@Catalog,@Table,@Column,@Message);
END;
DROP TABLE #temp_table;
--------------------------------------------------------------------------end of #5
-- *******************************************************************
-- list stage Tables not in the source database
-- *******************************************************************//
CREATE PROCEDURE syd_StagedTablesNotInSource
@AppliCationName VARCHAR(100),
@SourceDatabaseName VARCHAR(100),
@SQLog_str NVARCHAR(max) OUT
AS
SET @SQLog_str = '
SELECT
@SourceDatabaseName,Table_Name
FROM
(
select
ct.Table_Name
from
dbo.[AppliCation] a
INNER JOIN dbo.CustomTable ct
ON a.AppliCationId=ct.AppliCationId
AND a.AppliCationName=@AppliCationName
UNION
select
st.Table_Name
from
dbo.[AppliCation] a
INNER JOIN dbo.StandardTable st
ON a.AppliCationId=st.AppliCationId
AND a.AppliCationName=@AppliCationName
) StageTables
LEFT JOIN
(
SELECT
Table_Name
FROM '+@SourceDatabaseName+'.INFORMATION_SCHEMA.tables
) CD
ON StageTables.Table_Name=CD.Table_name
WHERE CD.Table_Name IS NULL'
-- *******************************************************************
-- list stage Tables not in the Stage database
-- *******************************************************************
CREATE PROCEDURE syd_StagedTablesNotInStage
@AppliCationName VARCHAR(100),
@StageDatabaseName VARCHAR(100),
@SQLog_str NVARCHAR(max) OUT
AS
SET @SQLog_str = '
SELECT
@StageDatabaseName,Table_Name
FROM
(
select
ct.Table_Name
from
dbo.[AppliCation] a
INNER JOIN dbo.CustomTable ct
ON a.AppliCationId=ct.AppliCationId
AND a.AppliCationName=@AppliCationName
UNION
select
st.Table_Name
from
dbo.[AppliCation] a
INNER JOIN dbo.StandardTable st
ON a.AppliCationId=st.AppliCationId
AND a.AppliCationName=@AppliCationName
) StageTables
LEFT JOIN
(
SELECT
Table_Name
FROM '+@StageDatabaseName+'.INFORMATION_SCHEMA.tables
) CD
ON StageTables.Table_Name=CD.Table_name
WHERE CD.Table_Name IS NULL'
=========================================================================================================================
-- *******************************************************************
-- list stage Columns not in the source database
-- *******************************************************************
CREATE PROCEDURE syd_StagedColsNotInSource
@AppliCationName VARCHAR(100),
@SourceDatabaseName VARCHAR(100),
@SQLog_str NVARCHAR(max) OUT
AS
SET @SQLog_str = '
SELECT
@SourceDatabaseName,Table_Name,ColumnName
FROM
(
select
ct.Table_Name,ct.ColumnName
from
dbo.[AppliCation] a
INNER JOIN dbo.CustomColumn ct
ON a.AppliCationId=ct.AppliCationId
AND a.AppliCationName=@AppliCationName
UNION
select
st.Table_Name,st.ColumnName
from
dbo.[AppliCation] a
INNER JOIN dbo.StandardColumn st
ON a.AppliCationId=st.AppliCationId
AND a.AppliCationName=@AppliCationName
) StageColumns
LEFT JOIN
(
SELECT
Table_Name,Column_Name
FROM '+@SourceDatabaseName+'.INFORMATION_SCHEMA.Columns
) CD
ON StageColumns.ColumnName=CD.Column_name
WHERE CD.Column_Name IS NULL'
-- *******************************************************************
-- list stage Columns not in the Stage database
-- *******************************************************************
CREATE PROCEDURE syd_StagedColsNotInStage
@AppliCationName VARCHAR(100),
@StageDatabaseName VARCHAR(100),
@SQLog_str NVARCHAR(max) OUT
AS
SET @SQLog_str = '
SELECT
@StageDatabaseName,Table_Name, ColumnName
FROM
(
select
ct.Table_Name,ct.ColumnName
from
dbo.[AppliCation] a
INNER JOIN dbo.CustomColumn ct
ON a.AppliCationId=ct.AppliCationId
AND a.AppliCationName=@AppliCationName
UNION
select
st.Table_Name,st.ColumnName
from
dbo.[AppliCation] a
INNER JOIN dbo.StandardColumn st
ON a.AppliCationId=st.AppliCationId
AND a.AppliCationName=@AppliCationName
) StageColumns
LEFT JOIN
(
SELECT
Table_Name,Column_Name
FROM '+@StageDatabaseName+'.INFORMATION_SCHEMA.Columns
) CD
ON StageColumns.ColumnName=CD.Column_name
WHERE CD.Column_Name IS NULL'
-- *******************************************************************
-- list stage Columns whose data type is not same in the Source and Stage Database
-- *******************************************************************
CREATE PROCEDURE syd_StagedCols_DT_Differ_SourceCols
@AppliCationName VARCHAR(100),
@SourceDatabaseName VARCHAR(100),
@SQLog_str NVARCHAR(max) OUT
AS
SET @SQLog_str = '
SELECT
@SourceDatabaseName,Table_Name,ColumnName
FROM
(
select
ct.Table_Name, ct.ColumnName, ct.DataType
from
dbo.[AppliCation] a
INNER JOIN dbo.CustomColumn ct
ON a.AppliCationId=ct.AppliCationId
AND a.AppliCationName=@AppliCationName
UNION
select
st.Table_Name,st.ColumnName, st.DataType
from
dbo.[AppliCation] a
INNER JOIN dbo.StandardColumn st
ON a.AppliCationId=st.AppliCationId
AND a.AppliCationName=@AppliCationName
) StageColumns
LEFT JOIN
(
SELECT
Table_Name, Column_Name, Data_Type
FROM '+@SourceDatabaseName+'.INFORMATION_SCHEMA.Columns
) CD
ON StageColumns.ColumnName = CD.Column_name AND
WHERE CD.Column_Name IS NOT NULL AND
StageColumns.DataType <> CD.Data_Type'
use biCentral
GO
SET NOCOUNT ON
GO
-----------------------
-- LOG table
-----------------------
CREATE TABLE syd_LogTable (
Log_Database nvarchar(128),
Log_Table nvarchar(128),
Log_Column nvarchar(128),
Log_Message nvarchar(128)
);
GO
DECLARE
@AppliCationName VARCHAR(100)='CD'
, @ClientName VARCHAR(100)='ACHP'
, @ClientID INT
, @nMColum1 NVARCHAR(max)
, @nMColum2 NVARCHAR(max)
, @nMColum3 NVARCHAR(max)
, @nMColum4 NVARCHAR(max)
, @nMColum5 NVARCHAR(max)
, @SourceDatabaseName VARCHAR(100)
, @StageDatabaseName VARCHAR(100)
SELECT
@SourceDatabaseName=(SELECT DatabaseName FROM [BICentral].[dbo].DatabaseLoCation WHERE DatabaseID=ca.SourceDatabaseID)
, @StageDatabaseName=(SELECT DatabaseName FROM [BICentral].[dbo].DatabaseLoCation WHERE DatabaseID=ca.TargetDatabaseID)
FROM
[BICentral].[dbo].[Client] c
INNER JOIN [BICentral].[dbo].[ClientAppliCation] ca
ON c.ClientId=ca.ClientId
AND c.ClientName=@ClientName
INNER JOIN [BICentral].[dbo].[AppliCation] a
ON ca.AppliCationId=a.AppliCationId
AND a.[AppliCationName] =@AppliCationName
//**Making SQL strings Ready**//
-----------------------------------
EXEC syd_StagedTablesNotInSource
@AppliCationName,
@SourceDatabaseName,
@SQLog_str = @nMColumn1 OUT;
EXEC syd_StagedTablesNotInStage
@AppliCationName,
@StageDatabaseName,
@SQLog_str = @nMColumn2 OUT;
EXEC syd_StagedColsNotInSource
@AppliCationName,
@SourceDatabaseName,
@SQLog_str = @nMColumn3 OUT;
EXEC syd_StagedColsNotInStage
@AppliCationName,
@StageDatabaseName,
@SQLog_str = @nMColumn4 OUT;
EXEC syd_StagedCols_DT_Differ_SourceCols
@AppliCationName,
@SourceDatabaseName,
@SQLog_str = @nMColumn5 OUT;
//**Messages to Log Table**//
-------------------------------
EXEC usp_PreStageValidation
@nMColumn1,
@nMColumn2,
@nMColumn3,
@nMColumn4,
@nMColumn5;
GO
//**Messages to SQL Console**//
---------------------------------
SELECT * FROM syd_LogTable;
July 27, 2012 at 2:53 pm
morepainot (7/27/2012)
but I need a better format instead of creating 5 seperate temp _tables
Tell them what you told me in the email you sent me.
--Jeff Moden
Change is inevitable... Change for the better is not.
July 27, 2012 at 2:55 pm
Holy cowabunga. Over there on the left when posting is IFCode shortcuts. They do a great job of helping format large code segments.
The real problem is that the code you posted is full of errors. I am a bit concerned that the scope of this is beyond an online forum. This is 500 lines of code and we still don't have ddl (create table) or sample data (inserts). All we have is 500 lines of code that needs to completely rewritten top to bottom.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
July 27, 2012 at 2:56 pm
Jeff Moden (7/27/2012)
morepainot (7/27/2012)
but I need a better format instead of creating 5 seperate temp _tablesTell them what you told me in the email you sent me.
And what you told me in mine too.
See why I said it is far better to use the forum instead of individuals? 😀
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
July 27, 2012 at 3:04 pm
Sean Lange (7/27/2012)
Jeff Moden (7/27/2012)
morepainot (7/27/2012)
but I need a better format instead of creating 5 seperate temp _tablesTell them what you told me in the email you sent me.
And what you told me in mine too.
See why I said it is far better to use the forum instead of individuals? 😀
Is there a reason neither of you can post what he said in his emails to you?
July 27, 2012 at 3:10 pm
Lynn Pettis (7/27/2012)
Sean Lange (7/27/2012)
Jeff Moden (7/27/2012)
morepainot (7/27/2012)
but I need a better format instead of creating 5 seperate temp _tablesTell them what you told me in the email you sent me.
And what you told me in mine too.
See why I said it is far better to use the forum instead of individuals? 😀
Is there a reason neither of you can post what he said in his emails to you?
Well mine didn't have any details other than that looping is not allowed. 😉
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
July 27, 2012 at 3:15 pm
I cant tell if you guys are being sarcastic or not...
July 27, 2012 at 3:16 pm
Sean Lange (7/27/2012)
Lynn Pettis (7/27/2012)
Sean Lange (7/27/2012)
Jeff Moden (7/27/2012)
morepainot (7/27/2012)
but I need a better format instead of creating 5 seperate temp _tablesTell them what you told me in the email you sent me.
And what you told me in mine too.
See why I said it is far better to use the forum instead of individuals? 😀
Is there a reason neither of you can post what he said in his emails to you?
Well mine didn't have any details other than that looping is not allowed. 😉
Ya, thats exactly what i said to both of you. You guys seemed very knowledgeable so i made an attempt to contact for help.
July 27, 2012 at 3:21 pm
morepainot (7/27/2012)
I cant tell if you guys are being sarcastic or not...
I can't speak for anybody else but I was not. Knowing Lynn and Jeff as well as I do I doubt they are either. You have at least 3 people that are willing and able to help. We just need all the details in order to help.
I am guessing the reason Jeff asked is because at your last posting you seemed perfectly content with looping. For Jeff and I we understand why the new posting. For anybody else (i.e. Lynn) they are left wondering why all of a sudden this thread is getting activity again.
So for anybody else just wandering in...
It seems that after further review the company was in fact not satisfied with the looping construct OR the original cursor. As a result the OP is requesting help with turning this loop based process into a set based process.
The ball is now in your court. As I said above you did a great job posting the code you are running. We just can't do much to help because we don't have tables or data to test with.
--EDIT-- spelling is very challenging late on a Friday afternoon.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
July 27, 2012 at 7:07 pm
sivaj2k (7/26/2012)
HiI am sending sample procedure to avoid cursors. Implement this logic in your procedures. This may help you.
declare @test_tab TABLE
(
[id1] [int] NULL,
query [varchar](50) NULL,
[result] [bit] NULL)
insert into @test_tab values(1,'select 30 - 50',Null)
insert into @test_tab values(2,'select 70 - 50',Null)
insert into @test_tab values(3,'select 20 - 20',Null)
select * from @test_tab
declare @start int=1,@vquery varchar(max),@result int
while @start <= (select COUNT(*) from @test_tab)
begin
select @vquery = query from @test_tab where id1 = @start
exec (@vquery)
set @start = @start + 1
end
Regard
Siva Kumar J:-)
Sorry, Siva. Like I said, While loops are no better than well written cursors. Apparently the company the op is working for agrees because they have rejected his loop code.
--Jeff Moden
Change is inevitable... Change for the better is not.
July 27, 2012 at 7:08 pm
Lynn Pettis (7/27/2012)
Sean Lange (7/27/2012)
Jeff Moden (7/27/2012)
morepainot (7/27/2012)
but I need a better format instead of creating 5 seperate temp _tablesTell them what you told me in the email you sent me.
And what you told me in mine too.
See why I said it is far better to use the forum instead of individuals? 😀
Is there a reason neither of you can post what he said in his emails to you?
Only because I hold emails from people in confidence. It's up to the OP to discuss it here if he wants.
--Jeff Moden
Change is inevitable... Change for the better is not.
July 27, 2012 at 7:17 pm
morepainot (7/27/2012)
I cant tell if you guys are being sarcastic or not...
Actually, there is a bit of ironic tone in my posts now. I told you at the beginning of this thread that While loops and Temp Tables were no better than cursors and that you'd be better off writing set-based code. Apparently the company you're contracted to agrees with what I said. Now you post 500 lines of code looking for someone to fix it and I find that a bit ironic as well because you're the one "contracted" by a company to fix that code.
Also, the term "set based" is a standard term and if you didn't know what that meant, you should have asked.
If you're done telling us that you want to write While loops to replace cursors after you've been told that's not the thing to do, I can take a look at your code and give you an example of how to convert one of the loops to "set based" code. The rest will be up to you.
{EDIT} I also need for you to attach an example of what the "strlog" parameters of the first stored procedure look like.
--Jeff Moden
Change is inevitable... Change for the better is not.
July 27, 2012 at 8:01 pm
Sorry, made a mistake... I'll repost the code in a couple of minutes...
--Jeff Moden
Change is inevitable... Change for the better is not.
July 27, 2012 at 8:55 pm
Sorry for my previous mis-post. I forgot that you were trying to add messages to each extracted row.
First, a bit of an explanation about the first section of the first stored procedure in the code you posted...
1. You load a temporary structure with data, just like a cursor.
That the first "trip" to the database.
2. In a While loop, just like a cursor, you...
2.a. You have a SELECT, which creates or reuses a separate execution plan, to read just one row
into a set of variables. That's a "trip" to the database for each and every row.
2.b. You create the same message over and over for each row. That's a simple waste of clock
cycles whether you're using a cursor or a While/Temp combo.
2.c. Then you INSERT, which creates or reuses a separate execution plan, to save just one row
into a table. That's another "trip" to the database for each and every row.
2.d. Finally you increment a counter. Since the counter isn't needed in set based programmin,
it's a simple waste of clock cycles. Then, you loop back to 2.a and do it over and over
and over once for each and every row.
Is it any wonder the company you're contracted to has a real problem with accepting either Cursors or While loops?
I've reworked that section of code to work in a set based fashion. The minor difficulty here is that you're trying to add a message to each of of the result set of a bit of executed SQL. Stop thinking in rows for these things. Think about how to do it all with columns and you'll be well on your way to making set based code that runs nasty fast.
Here's the code... the details are in the comments. YOU should always put comments in the code. It makes it easier on the next person (which very well could be you) and your customers will absolutely adore you for it. It also makes you look like a professional instead of just some hack.
CREATE PROCEDURE usp_PreStageValidation
@SQLog_str1 NVARCHAR(MAX),
@SQLog_str2 NVARCHAR(MAX),
@SQLog_str3 NVARCHAR(MAX),
@SQLog_str4 NVARCHAR(MAX),
@SQLog_str5 NVARCHAR(MAX)
AS
BEGIN
--===== Declare some obviously named variables
DECLARE @Catalog NVARCHAR(128),
@Table NVARCHAR(128),
@Column NVARCHAR(128),
@Message NVARCHAR(128),
@RecCount INT,
@index INT
;
--=====================================================================================================================
-- Presets
--=====================================================================================================================
--===== Suppress the auto-display of rowcounts to prevent GUIs from returning false errors.
SET NOCOUNT ON;
--===== Build the reusable temp table to hold the rows extracted from the input parameters.
-- We only need to build it once and then reuse it.
CREATE TABLE #ExtractedRows
(
Cat NVARCHAR(128),
Tbl NVARCHAR(128)
)
;
--=====================================================================================================================
-- Expand, capture, and mark each row from the first input parameter as
-- "This Stage table does not exist in this SOURCE database"
--=====================================================================================================================
--===== Preset the message for this section
SELECT @Message = N'This Stage table does not exist in this SOURCE database'
;
INSERT INTO #ExtractedRows
(Cat, Tbl)
EXEC sp_executeSQL @SQLog_str1,
N'@AppliCationName VARCHAR(100)',
@AppliCationName=@AppliCationName
;
--===== Add the extacted information and message to the log table.
-- I tried to follow best practices for making code bullet proof here
-- but I don't know your column names. You'll likely neet to change them.
INSERT INTO syd_LogTable
(Cat, Tbl, somecol, Message)
SELECT Cat,
Tbl,
somecol = NULL,
Message = @Message
FROM #ExtractedRows
;
--===== Truncate the working table so we don't have to build it again.
-- If they won't give you privs to truncate the temp table, then
-- you may have to go back to dropping it and recreating it.
TRUNCATE TABLE #ExtractedRows;
Obviously, I couldn't test this code.
Also, name your temp tables after what they hold as a form of self documenting code and be consistent with your formatting (especially on the leading / trailing comma thing) so your code is easier to read an looks more profession.
Try to write the rest of the code in a similar fashion to get rid of the rest of the loops. Ask questions about how to do something if you need to.
I hope this helps.
--Jeff Moden
Change is inevitable... Change for the better is not.
July 28, 2012 at 9:05 am
Jeff Moden (7/27/2012)
Sorry for my previous mis-post. I forgot that you were trying to add messages to each extracted row.First, a bit of an explanation about the first section of the first stored procedure in the code you posted...
1. You load a temporary structure with data, just like a cursor.That the first "trip" to the database.
2. In a While loop, just like a cursor, you...
2.a. You have a SELECT, which creates or reuses a separate execution plan, to read just one row
into a set of variables. That's a "trip" to the database for each and every row.
2.b. You create the same message over and over for each row. That's a simple waste of clock
cycles whether you're using a cursor or a While/Temp combo.
2.c. Then you INSERT, which creates or reuses a separate execution plan, to save just one row
into a table. That's another "trip" to the database for each and every row.
2.d. Finally you increment a counter. Since the counter isn't needed in set based programmin,
it's a simple waste of clock cycles. Then, you loop back to 2.a and do it over and over
and over once for each and every row.
Is it any wonder the company you're contracted to has a real problem with accepting either Cursors or While loops?
I've reworked that section of code to work in a set based fashion. The minor difficulty here is that you're trying to add a message to each of of the result set of a bit of executed SQL. Stop thinking in rows for these things. Think about how to do it all with columns and you'll be well on your way to making set based code that runs nasty fast.
Here's the code... the details are in the comments. YOU should always put comments in the code. It makes it easier on the next person (which very well could be you) and your customers will absolutely adore you for it. It also makes you look like a professional instead of just some hack.
CREATE PROCEDURE usp_PreStageValidation@SQLog_str1 NVARCHAR(MAX),
@SQLog_str2 NVARCHAR(MAX),
@SQLog_str3 NVARCHAR(MAX),
@SQLog_str4 NVARCHAR(MAX),
@SQLog_str5 NVARCHAR(MAX)
AS
BEGIN
--===== Declare some obviously named variables
DECLARE @Catalog NVARCHAR(128),
@Table NVARCHAR(128),
@Column NVARCHAR(128),
@Message NVARCHAR(128),
@RecCount INT,
@index INT
;
--=====================================================================================================================
-- Presets
--=====================================================================================================================
--===== Suppress the auto-display of rowcounts to prevent GUIs from returning false errors.
SET NOCOUNT ON;
--===== Build the reusable temp table to hold the rows extracted from the input parameters.
-- We only need to build it once and then reuse it.
CREATE TABLE #ExtractedRows
(
Cat NVARCHAR(128),
Tbl NVARCHAR(128)
)
;
--=====================================================================================================================
-- Expand, capture, and mark each row from the first input parameter as
-- "This Stage table does not exist in this SOURCE database"
--=====================================================================================================================
--===== Preset the message for this section
SELECT @Message = N'This Stage table does not exist in this SOURCE database'
;
INSERT INTO #ExtractedRows
(Cat, Tbl)
EXEC sp_executeSQL @SQLog_str1,
N'@AppliCationName VARCHAR(100)',
@AppliCationName=@AppliCationName
;
--===== Add the extacted information and message to the log table.
-- I tried to follow best practices for making code bullet proof here
-- but I don't know your column names. You'll likely neet to change them.
INSERT INTO syd_LogTable
(Cat, Tbl, somecol, Message)
SELECT Cat,
Tbl,
somecol = NULL,
Message = @Message
FROM #ExtractedRows
;
--===== Truncate the working table so we don't have to build it again.
-- If they won't give you privs to truncate the temp table, then
-- you may have to go back to dropping it and recreating it.
TRUNCATE TABLE #ExtractedRows;
Obviously, I couldn't test this code.
Also, name your temp tables after what they hold as a form of self documenting code and be consistent with your formatting (especially on the leading / trailing comma thing) so your code is easier to read an looks more profession.
Try to write the rest of the code in a similar fashion to get rid of the rest of the loops. Ask questions about how to do something if you need to.
I hope this helps.
Wow, thanks a lot Jeff!I know you advised me against looping from the beginning but after I talked to one of their senior developers he said looping was "the way to go". When it came to demo my code they HATED it. They said looping was a terrible idea and they need me to rework the code. My "achilles heel" is that even though I have worked as a developer before this is the first time Im given responsibility to develop the code on my own. I have always worked with other developers. Ive never had to test codes, either. I would write a code and send it to my lead so he can testit.
Thank you for being realy detailed and EXPLAINING what youre doing and why. I really appreciate the time you have taken to help me. Im probably gonna need help with this some more lol. I have to demo this code again and it would be great if I could knock their socks off this time around.
Viewing 15 posts - 16 through 30 (of 137 total)
You must be logged in to reply to this topic. Login to reply