|
database
newsgroups
|
|||||||||||||||||||||||
|
|||||||||||||||||||||||
How do you rate this scriptI would like to evaluate this script written. I would like take openion of professionals like you. The author has written this in an hour time. Problem: change all nulls to zeros ( both in char and int).(It does not have time stamps) with a single procedure. This is working fine. 1) how do you rate in 1 to 10 scale 2) what improvements do you suggest in approach, coding practice, knowledge etc Code : ET ANSI_WARNINGS OFF GO SET QUOTED_IDENTIFIER ON GO SET ANSI_NULLS ON GO CREATE PROC ConvertNullsToZerosSreenu AS DECLARE MyNullCursorForSreenu cursor READ_ONLY FOR SELECT a.name as MYColumnName,b.name as MyTableName FROM syscolumns a INNER JOIN SYSOBJECTS b ON a.id = b.id where b.xtype = 'u' and b.name <> 'dtproperties' DECLARE @ColumnName varchar(200),@TableName varchar(200) DECLARE @MYVARPHRASE nvarchar(1000) DECLARE @ISIDENTVAL int DECLARE @MYVARIDSTRING nvarchar(1000) DECLARE @paramdef nvarchar(1000) OPEN MyNullCursorForSreenu FETCH NEXT FROM MyNullCursorForSreenu INTO @ColumnName,@TableName while (@@fetch_status <> -1) BEGIN IF (@@fetch_status <> -2) BEGIN IF (@ISIDENTVAL = 0) BEGIN SELECT @MYVARPHRASE = N' UPDATE ' + @TableName + ' SET ' + @columnName + ' = 0 WHERE ' + @columnName + ' IS NULL' exec(@MYVARPHRASE) END END FETCH NEXT FROM MyNullCursorForSreenu INTO @ColumnName,@TableName SELECT @ISIDENTVAL = COLUMNPROPERTY (OBJECT_ID(@TableName),@ColumnName,'isidentity') END CLOSE MyNullCursorForSreenu DEALLOCATE MyNullCursorForSreenu GO SET QUOTED_IDENTIFIER OFF GO SET ANSI_NULLS ON GO SET ANSI_WARNINGS OFF GO Hi
You don't say if this person was sat in front of a computer to write it! There is a missing S on the first line!!!! It seems incomplete, I am not sure why he is doing with the identity and they are checking if a column can contain nulls or what the columns datatype is. There is no error checking. A better design may have been to do one update per table. Afterwards did you ask him to explain his thought process and what he wanted to do? Rating someone on one query would be unfare without knowing the criteria that he was being marked against. John Show quote "R.D" wrote: > Hi > I would like to evaluate this script written. I would like take openion of > professionals like you. The author has written this in an hour time. > > Problem: change all nulls to zeros ( both in char and int).(It does not have > time stamps) with a single procedure. This is working fine. > > 1) how do you rate in 1 to 10 scale > 2) what improvements do you suggest in approach, coding practice, knowledge > etc > > Code : > > ET ANSI_WARNINGS OFF > GO > SET QUOTED_IDENTIFIER ON > GO > SET ANSI_NULLS ON > GO > CREATE PROC ConvertNullsToZerosSreenu > AS > DECLARE MyNullCursorForSreenu cursor > READ_ONLY > FOR SELECT a.name as MYColumnName,b.name as MyTableName FROM syscolumns a > INNER JOIN SYSOBJECTS b ON a.id = b.id where b.xtype = 'u' and b.name <> > 'dtproperties' > DECLARE @ColumnName varchar(200),@TableName varchar(200) > DECLARE @MYVARPHRASE nvarchar(1000) > DECLARE @ISIDENTVAL int > DECLARE @MYVARIDSTRING nvarchar(1000) > DECLARE @paramdef nvarchar(1000) > OPEN MyNullCursorForSreenu > FETCH NEXT FROM MyNullCursorForSreenu INTO @ColumnName,@TableName > while (@@fetch_status <> -1) > BEGIN > IF (@@fetch_status <> -2) > BEGIN > IF (@ISIDENTVAL = 0) > BEGIN > SELECT @MYVARPHRASE = N' UPDATE ' + @TableName + ' SET ' + @columnName + ' = > 0 WHERE ' + @columnName + ' IS NULL' > exec(@MYVARPHRASE) > END > END > FETCH NEXT FROM MyNullCursorForSreenu INTO @ColumnName,@TableName > SELECT @ISIDENTVAL = COLUMNPROPERTY > (OBJECT_ID(@TableName),@ColumnName,'isidentity') > END > CLOSE MyNullCursorForSreenu > DEALLOCATE MyNullCursorForSreenu > GO > SET QUOTED_IDENTIFIER OFF > GO > SET ANSI_NULLS ON > GO > SET ANSI_WARNINGS OFF > GO > > John
> You don't say if this person was sat in front of a computer to write it! he did sit in front of computer and wrote this script in an hour> There is a missing S on the first line!!!! S is missing because my copy , paste problem. Not his> I am not sure why he is doing with the identity he used that bcos, it throws an error when checking identity column. he does not want that error. >There is no error checking He is asked to write a procedure to update all nulls to zeros. He says he yes > Afterwards did you ask him to explain his thought process uses sysobjects table and cursor through the recordset to take columnname and table name and update each column dynamically. This procedure should work for any whrere for any number of tables. And it is working Now can you rate him. Regards r.d Show quote "John Bell" wrote: > Hi > > You don't say if this person was sat in front of a computer to write it! > > There is a missing S on the first line!!!! > > It seems incomplete, I am not sure why he is doing with the identity and > they are checking if a column can contain nulls or what the columns datatype > is. There is no error checking. A better design may have been to do one > update per table. > > Afterwards did you ask him to explain his thought process and what he wanted > to do? > > Rating someone on one query would be unfare without knowing the criteria > that he was being marked against. > > John > > "R.D" wrote: > > > Hi > > I would like to evaluate this script written. I would like take openion of > > professionals like you. The author has written this in an hour time. > > > > Problem: change all nulls to zeros ( both in char and int).(It does not have > > time stamps) with a single procedure. This is working fine. > > > > 1) how do you rate in 1 to 10 scale > > 2) what improvements do you suggest in approach, coding practice, knowledge > > etc > > > > Code : > > > > ET ANSI_WARNINGS OFF > > GO > > SET QUOTED_IDENTIFIER ON > > GO > > SET ANSI_NULLS ON > > GO > > CREATE PROC ConvertNullsToZerosSreenu > > AS > > DECLARE MyNullCursorForSreenu cursor > > READ_ONLY > > FOR SELECT a.name as MYColumnName,b.name as MyTableName FROM syscolumns a > > INNER JOIN SYSOBJECTS b ON a.id = b.id where b.xtype = 'u' and b.name <> > > 'dtproperties' > > DECLARE @ColumnName varchar(200),@TableName varchar(200) > > DECLARE @MYVARPHRASE nvarchar(1000) > > DECLARE @ISIDENTVAL int > > DECLARE @MYVARIDSTRING nvarchar(1000) > > DECLARE @paramdef nvarchar(1000) > > OPEN MyNullCursorForSreenu > > FETCH NEXT FROM MyNullCursorForSreenu INTO @ColumnName,@TableName > > while (@@fetch_status <> -1) > > BEGIN > > IF (@@fetch_status <> -2) > > BEGIN > > IF (@ISIDENTVAL = 0) > > BEGIN > > SELECT @MYVARPHRASE = N' UPDATE ' + @TableName + ' SET ' + @columnName + ' = > > 0 WHERE ' + @columnName + ' IS NULL' > > exec(@MYVARPHRASE) > > END > > END > > FETCH NEXT FROM MyNullCursorForSreenu INTO @ColumnName,@TableName > > SELECT @ISIDENTVAL = COLUMNPROPERTY > > (OBJECT_ID(@TableName),@ColumnName,'isidentity') > > END > > CLOSE MyNullCursorForSreenu > > DEALLOCATE MyNullCursorForSreenu > > GO > > SET QUOTED_IDENTIFIER OFF > > GO > > SET ANSI_NULLS ON > > GO > > SET ANSI_WARNINGS OFF > > GO > > > > Hi
For one thing I don't know what job he is applying for and what level of skill is required. I have no context, I was not present at the interview and I don't have all the facts. If i was asked to write this I would need to know why the database was not designed correctly in the first place. John Show quote "R.D" wrote: > John > > You don't say if this person was sat in front of a computer to write it! > he did sit in front of computer and wrote this script in an hour > > There is a missing S on the first line!!!! > S is missing because my copy , paste problem. Not his > > I am not sure why he is doing with the identity > he used that bcos, it throws an error when checking identity column. he does > not want that error. > >There is no error checking > yes > > Afterwards did you ask him to explain his thought process > He is asked to write a procedure to update all nulls to zeros. He says he > uses sysobjects table and cursor through the recordset to take columnname and > table name and update each column dynamically. > > This procedure should work for any whrere for any number of tables. And it > is working > > Now can you rate him. > > Regards > r.d > > > > > > > > > "John Bell" wrote: > > > Hi > > > > You don't say if this person was sat in front of a computer to write it! > > > > There is a missing S on the first line!!!! > > > > It seems incomplete, I am not sure why he is doing with the identity and > > they are checking if a column can contain nulls or what the columns datatype > > is. There is no error checking. A better design may have been to do one > > update per table. > > > > Afterwards did you ask him to explain his thought process and what he wanted > > to do? > > > > Rating someone on one query would be unfare without knowing the criteria > > that he was being marked against. > > > > John > > > > "R.D" wrote: > > > > > Hi > > > I would like to evaluate this script written. I would like take openion of > > > professionals like you. The author has written this in an hour time. > > > > > > Problem: change all nulls to zeros ( both in char and int).(It does not have > > > time stamps) with a single procedure. This is working fine. > > > > > > 1) how do you rate in 1 to 10 scale > > > 2) what improvements do you suggest in approach, coding practice, knowledge > > > etc > > > > > > Code : > > > > > > ET ANSI_WARNINGS OFF > > > GO > > > SET QUOTED_IDENTIFIER ON > > > GO > > > SET ANSI_NULLS ON > > > GO > > > CREATE PROC ConvertNullsToZerosSreenu > > > AS > > > DECLARE MyNullCursorForSreenu cursor > > > READ_ONLY > > > FOR SELECT a.name as MYColumnName,b.name as MyTableName FROM syscolumns a > > > INNER JOIN SYSOBJECTS b ON a.id = b.id where b.xtype = 'u' and b.name <> > > > 'dtproperties' > > > DECLARE @ColumnName varchar(200),@TableName varchar(200) > > > DECLARE @MYVARPHRASE nvarchar(1000) > > > DECLARE @ISIDENTVAL int > > > DECLARE @MYVARIDSTRING nvarchar(1000) > > > DECLARE @paramdef nvarchar(1000) > > > OPEN MyNullCursorForSreenu > > > FETCH NEXT FROM MyNullCursorForSreenu INTO @ColumnName,@TableName > > > while (@@fetch_status <> -1) > > > BEGIN > > > IF (@@fetch_status <> -2) > > > BEGIN > > > IF (@ISIDENTVAL = 0) > > > BEGIN > > > SELECT @MYVARPHRASE = N' UPDATE ' + @TableName + ' SET ' + @columnName + ' = > > > 0 WHERE ' + @columnName + ' IS NULL' > > > exec(@MYVARPHRASE) > > > END > > > END > > > FETCH NEXT FROM MyNullCursorForSreenu INTO @ColumnName,@TableName > > > SELECT @ISIDENTVAL = COLUMNPROPERTY > > > (OBJECT_ID(@TableName),@ColumnName,'isidentity') > > > END > > > CLOSE MyNullCursorForSreenu > > > DEALLOCATE MyNullCursorForSreenu > > > GO > > > SET QUOTED_IDENTIFIER OFF > > > GO > > > SET ANSI_NULLS ON > > > GO > > > SET ANSI_WARNINGS OFF > > > GO > > > > > > John
He did not do database design. The sole purpose is to understand whether he had the comprehension of the T-sql, sql server and lead a four to five people to develop scripts. Now can you rate him (other skills will tested later.) Regards r.d Show quote "John Bell" wrote: > Hi > > For one thing I don't know what job he is applying for and what level of > skill is required. I have no context, I was not present at the interview and > I don't have all the facts. > > If i was asked to write this I would need to know why the database was not > designed correctly in the first place. > > John > > "R.D" wrote: > > > John > > > You don't say if this person was sat in front of a computer to write it! > > he did sit in front of computer and wrote this script in an hour > > > There is a missing S on the first line!!!! > > S is missing because my copy , paste problem. Not his > > > I am not sure why he is doing with the identity > > he used that bcos, it throws an error when checking identity column. he does > > not want that error. > > >There is no error checking > > yes > > > Afterwards did you ask him to explain his thought process > > He is asked to write a procedure to update all nulls to zeros. He says he > > uses sysobjects table and cursor through the recordset to take columnname and > > table name and update each column dynamically. > > > > This procedure should work for any whrere for any number of tables. And it > > is working > > > > Now can you rate him. > > > > Regards > > r.d > > > > > > > > > > > > > > > > > > "John Bell" wrote: > > > > > Hi > > > > > > You don't say if this person was sat in front of a computer to write it! > > > > > > There is a missing S on the first line!!!! > > > > > > It seems incomplete, I am not sure why he is doing with the identity and > > > they are checking if a column can contain nulls or what the columns datatype > > > is. There is no error checking. A better design may have been to do one > > > update per table. > > > > > > Afterwards did you ask him to explain his thought process and what he wanted > > > to do? > > > > > > Rating someone on one query would be unfare without knowing the criteria > > > that he was being marked against. > > > > > > John > > > > > > "R.D" wrote: > > > > > > > Hi > > > > I would like to evaluate this script written. I would like take openion of > > > > professionals like you. The author has written this in an hour time. > > > > > > > > Problem: change all nulls to zeros ( both in char and int).(It does not have > > > > time stamps) with a single procedure. This is working fine. > > > > > > > > 1) how do you rate in 1 to 10 scale > > > > 2) what improvements do you suggest in approach, coding practice, knowledge > > > > etc > > > > > > > > Code : > > > > > > > > ET ANSI_WARNINGS OFF > > > > GO > > > > SET QUOTED_IDENTIFIER ON > > > > GO > > > > SET ANSI_NULLS ON > > > > GO > > > > CREATE PROC ConvertNullsToZerosSreenu > > > > AS > > > > DECLARE MyNullCursorForSreenu cursor > > > > READ_ONLY > > > > FOR SELECT a.name as MYColumnName,b.name as MyTableName FROM syscolumns a > > > > INNER JOIN SYSOBJECTS b ON a.id = b.id where b.xtype = 'u' and b.name <> > > > > 'dtproperties' > > > > DECLARE @ColumnName varchar(200),@TableName varchar(200) > > > > DECLARE @MYVARPHRASE nvarchar(1000) > > > > DECLARE @ISIDENTVAL int > > > > DECLARE @MYVARIDSTRING nvarchar(1000) > > > > DECLARE @paramdef nvarchar(1000) > > > > OPEN MyNullCursorForSreenu > > > > FETCH NEXT FROM MyNullCursorForSreenu INTO @ColumnName,@TableName > > > > while (@@fetch_status <> -1) > > > > BEGIN > > > > IF (@@fetch_status <> -2) > > > > BEGIN > > > > IF (@ISIDENTVAL = 0) > > > > BEGIN > > > > SELECT @MYVARPHRASE = N' UPDATE ' + @TableName + ' SET ' + @columnName + ' = > > > > 0 WHERE ' + @columnName + ' IS NULL' > > > > exec(@MYVARPHRASE) > > > > END > > > > END > > > > FETCH NEXT FROM MyNullCursorForSreenu INTO @ColumnName,@TableName > > > > SELECT @ISIDENTVAL = COLUMNPROPERTY > > > > (OBJECT_ID(@TableName),@ColumnName,'isidentity') > > > > END > > > > CLOSE MyNullCursorForSreenu > > > > DEALLOCATE MyNullCursorForSreenu > > > > GO > > > > SET QUOTED_IDENTIFIER OFF > > > > GO > > > > SET ANSI_NULLS ON > > > > GO > > > > SET ANSI_WARNINGS OFF > > > > GO > > > > > > > > Hi
Ask the people he will lead to do the same thing and then you will be able to rate him. John Show quote "R.D" wrote: > John > He did not do database design. The sole purpose is to understand whether he > had the comprehension of the T-sql, sql server and lead a four to five people > to develop scripts. > Now can you rate him (other skills will tested later.) > > Regards > r.d > > > > "John Bell" wrote: > > > Hi > > > > For one thing I don't know what job he is applying for and what level of > > skill is required. I have no context, I was not present at the interview and > > I don't have all the facts. > > > > If i was asked to write this I would need to know why the database was not > > designed correctly in the first place. > > > > John > > > > "R.D" wrote: > > > > > John > > > > You don't say if this person was sat in front of a computer to write it! > > > he did sit in front of computer and wrote this script in an hour > > > > There is a missing S on the first line!!!! > > > S is missing because my copy , paste problem. Not his > > > > I am not sure why he is doing with the identity > > > he used that bcos, it throws an error when checking identity column. he does > > > not want that error. > > > >There is no error checking > > > yes > > > > Afterwards did you ask him to explain his thought process > > > He is asked to write a procedure to update all nulls to zeros. He says he > > > uses sysobjects table and cursor through the recordset to take columnname and > > > table name and update each column dynamically. > > > > > > This procedure should work for any whrere for any number of tables. And it > > > is working > > > > > > Now can you rate him. > > > > > > Regards > > > r.d > > > > > > > > > > > > > > > > > > > > > > > > > > > "John Bell" wrote: > > > > > > > Hi > > > > > > > > You don't say if this person was sat in front of a computer to write it! > > > > > > > > There is a missing S on the first line!!!! > > > > > > > > It seems incomplete, I am not sure why he is doing with the identity and > > > > they are checking if a column can contain nulls or what the columns datatype > > > > is. There is no error checking. A better design may have been to do one > > > > update per table. > > > > > > > > Afterwards did you ask him to explain his thought process and what he wanted > > > > to do? > > > > > > > > Rating someone on one query would be unfare without knowing the criteria > > > > that he was being marked against. > > > > > > > > John > > > > > > > > "R.D" wrote: > > > > > > > > > Hi > > > > > I would like to evaluate this script written. I would like take openion of > > > > > professionals like you. The author has written this in an hour time. > > > > > > > > > > Problem: change all nulls to zeros ( both in char and int).(It does not have > > > > > time stamps) with a single procedure. This is working fine. > > > > > > > > > > 1) how do you rate in 1 to 10 scale > > > > > 2) what improvements do you suggest in approach, coding practice, knowledge > > > > > etc > > > > > > > > > > Code : > > > > > > > > > > ET ANSI_WARNINGS OFF > > > > > GO > > > > > SET QUOTED_IDENTIFIER ON > > > > > GO > > > > > SET ANSI_NULLS ON > > > > > GO > > > > > CREATE PROC ConvertNullsToZerosSreenu > > > > > AS > > > > > DECLARE MyNullCursorForSreenu cursor > > > > > READ_ONLY > > > > > FOR SELECT a.name as MYColumnName,b.name as MyTableName FROM syscolumns a > > > > > INNER JOIN SYSOBJECTS b ON a.id = b.id where b.xtype = 'u' and b.name <> > > > > > 'dtproperties' > > > > > DECLARE @ColumnName varchar(200),@TableName varchar(200) > > > > > DECLARE @MYVARPHRASE nvarchar(1000) > > > > > DECLARE @ISIDENTVAL int > > > > > DECLARE @MYVARIDSTRING nvarchar(1000) > > > > > DECLARE @paramdef nvarchar(1000) > > > > > OPEN MyNullCursorForSreenu > > > > > FETCH NEXT FROM MyNullCursorForSreenu INTO @ColumnName,@TableName > > > > > while (@@fetch_status <> -1) > > > > > BEGIN > > > > > IF (@@fetch_status <> -2) > > > > > BEGIN > > > > > IF (@ISIDENTVAL = 0) > > > > > BEGIN > > > > > SELECT @MYVARPHRASE = N' UPDATE ' + @TableName + ' SET ' + @columnName + ' = > > > > > 0 WHERE ' + @columnName + ' IS NULL' > > > > > exec(@MYVARPHRASE) > > > > > END > > > > > END > > > > > FETCH NEXT FROM MyNullCursorForSreenu INTO @ColumnName,@TableName > > > > > SELECT @ISIDENTVAL = COLUMNPROPERTY > > > > > (OBJECT_ID(@TableName),@ColumnName,'isidentity') > > > > > END > > > > > CLOSE MyNullCursorForSreenu > > > > > DEALLOCATE MyNullCursorForSreenu > > > > > GO > > > > > SET QUOTED_IDENTIFIER OFF > > > > > GO > > > > > SET ANSI_NULLS ON > > > > > GO > > > > > SET ANSI_WARNINGS OFF > > > > > GO > > > > > > > > > > On Fri, 12 Aug 2005 00:23:01 -0700, R.D wrote:
>John Hi R.D.,>He did not do database design. The sole purpose is to understand whether he >had the comprehension of the T-sql, sql server and lead a four to five people >to develop scripts. >Now can you rate him (other skills will tested later.) You can't rate him on that, since you didn't test any required capabilities. Writing a cursor is NOT something you want your developers to do. If you must rate them, rate them on their ability to AVOID the use of a cursor. If I really had to change all nulls to zeros (which is IMO a crazy requirement to begin with), I'd check the documentation, then write the queries as UPDATE Table1 SET Col1 = '0', Col2 = o WHERE Col1 IS NULL OR Col2 IS NULL UPDATE Table2 and so on If the candidate had access to the documentation, I'd give him a low rating for writing a generic script instead of checking which columns to change and writing code as above. If you didn't leave the candidate this choice, I'd rate the test quite low and I'd not rate the candidate at all, since there's not enough data. If you want to test someone's skill at SQL, let him write proper set-based SQL statements. Oh, and if the candidate has to lead four to five people, I'd say that his people skills and leadership qualities are far more important than his ability to write a cursor. Best, Hugo -- (Remove _NO_ and _SPAM_ to get my e-mail address) >> Problem: change all NULLs to zeros ( both in CHAR(n) and INTEGER).(It does not have TIMESTAMPs) with a single procedure. This is working fine. << Okay. Now add NOT NULL and DEFAULT constraints to the tables so youcan stop doing this over and over. Frankly, for the CHAR(n) columns, I would not set them to '0', but would pick an appropriate default string -- '{{no phone}}", etc. Hi sql gurus
Thank you for you responses. But all missed my point here. I know how we are all concerned with performance and design. But my point is not that. The code is generated under the circustances 1) Procedure should be useful for any database (dynamic) 2) nulls to zeros should be for all columns and tables( again dynamic) 3) It should run anywhere. 4) write instantaniously under these circumstances, can any one tell is there any better way to do. perhaps what strikes to my mind is using *Sp_Msforeachtable* undocumented procedure or using view dynamically have serial number say identity value so as to avoid cursors.Of course we can add @@error and comments to code. Now I request you to send me better code for the purpose keeping the above circumstances intact. Regards r.d Show quote "--CELKO--" wrote: > >> Problem: change all NULLs to zeros ( both in CHAR(n) and INTEGER).(It does not have TIMESTAMPs) with a single procedure. This is working fine. << > > Okay. Now add NOT NULL and DEFAULT constraints to the tables so you > can stop doing this over and over. Frankly, for the CHAR(n) columns, I > would not set them to '0', but would pick an appropriate default string > -- '{{no phone}}", etc. > > Hi
You are now introducing restrictions/circumstances that were not in your original post. It is not a good advisable to use undocumented procedures in production code, therefore the candidate was correct to use cursors. sp_MSForEachTable would not tell you the datatypes of the columns or which columns to use, unless these columns were fixed and known. As the issue is really a design change it should be addressed by a release process and not a stored procedure. Part of this would be to add constraints to stop it happening again. You should thoroughly test the system to make sure it still functions in the way you think it should. John Show quote "R.D" wrote: > Hi sql gurus > > Thank you for you responses. But all missed my point here. > I know how we are all concerned with performance and design. > But my point is not that. > > The code is generated under the circustances > 1) Procedure should be useful for any database (dynamic) > 2) nulls to zeros should be for all columns and tables( again dynamic) > 3) It should run anywhere. > 4) write instantaniously > > under these circumstances, can any one tell is there any better way to do. > perhaps what strikes to my mind is using *Sp_Msforeachtable* undocumented > procedure or using view dynamically have serial number say identity value so > as to avoid cursors.Of course we can add @@error and comments to code. > > Now I request you to send me better code for the purpose keeping the above > circumstances intact. > > Regards > r.d > > > > > > "--CELKO--" wrote: > > > >> Problem: change all NULLs to zeros ( both in CHAR(n) and INTEGER).(It does not have TIMESTAMPs) with a single procedure. This is working fine. << > > > > Okay. Now add NOT NULL and DEFAULT constraints to the tables so you > > can stop doing this over and over. Frankly, for the CHAR(n) columns, I > > would not set them to '0', but would pick an appropriate default string > > -- '{{no phone}}", etc. > > > > it would be better to have one update statement per table, not per
column, as your candidate did This is a newsgroup, not a consulting firm. Your last statement is
insulting, and something I would expect from someone of the pointy-haired variety. Issuing orders to people who generously offer up their time to help people in need crosses the boundry of good taste. If you want to issue orders, hire a consulting firm to evaluate potential hires. In fact, you should probably do that anyway inasmuchas you don't have the expertise in-house. Show quote "R.D" <R*@discussions.microsoft.com> wrote in message does not have TIMESTAMPs) with a single procedure. This is working fine. <<news:B5430212-3C14-479A-A8F2-31528B75BAE2@microsoft.com... > Hi sql gurus > > Thank you for you responses. But all missed my point here. > I know how we are all concerned with performance and design. > But my point is not that. > > The code is generated under the circustances > 1) Procedure should be useful for any database (dynamic) > 2) nulls to zeros should be for all columns and tables( again dynamic) > 3) It should run anywhere. > 4) write instantaniously > > under these circumstances, can any one tell is there any better way to do. > perhaps what strikes to my mind is using *Sp_Msforeachtable* undocumented > procedure or using view dynamically have serial number say identity value so > as to avoid cursors.Of course we can add @@error and comments to code. > > Now I request you to send me better code for the purpose keeping the above > circumstances intact. > > Regards > r.d > > > > > > "--CELKO--" wrote: > > > >> Problem: change all NULLs to zeros ( both in CHAR(n) and INTEGER).(It Show quote > > > > Okay. Now add NOT NULL and DEFAULT constraints to the tables so you > > can stop doing this over and over. Frankly, for the CHAR(n) columns, I > > would not set them to '0', but would pick an appropriate default string > > -- '{{no phone}}", etc. > > > > >> The code is generated under the circustances 1) Procedure should be useful for any database (dynamic) <<No, you should have a dialect conversion tool that can move your code -- SwissQL is one such tool. >> 2) nulls to zeros should be for all columns and tables( again dynamic) << Think about that!! The data model is so bad that we cannot tell thedifference between a NULL and a zero! And it can change in between running the same query. A good data model is static and does nor change on the fly. That to say, "Elephants do not drop out of the sky in MagicLand!!" >> 3) It should run anywhere. << Portable SQL is hard and it is impossible with cursors and proprietaryschema infomation tables. >> 4) write instantaniously << Zero execution time? The only code I can think of that comes close tothat is a "no-op" in assembly language, but even it can take a single machien cycle. Certainly no I/O operation can do this. On Fri, 12 Aug 2005 22:50:02 -0700, R.D wrote:
>Hi sql gurus Hi R.D.,> >Thank you for you responses. But all missed my point here. >I know how we are all concerned with performance and design. >But my point is not that. I think it's the other way around - you missed our point. >The code is generated under the circustances The third requirement is where you really go wrong. Any script that does>1) Procedure should be useful for any database (dynamic) >2) nulls to zeros should be for all columns and tables( again dynamic) >3) It should run anywhere. what you describe at 1 and 2 (i.e. change data in an undefined number of columns in an undefined number of tables) should actually run NOWHERE. Any DBA that allows such a script to run on his or her databases should go find some education or another job. Maybe even both. Best, Hugo -- (Remove _NO_ and _SPAM_ to get my e-mail address) Hi All
Thank you John, at leaset you have understood what i was trying to convey. Sorry for all those who are hurt especially Brian ( if they get affended when I use the word request) Brian, I aways have reagard.reverence,love for news groups. That was not an order, but shows how comfortable i am with the news groups. As you are hurt, Iam extremely sorry, if you find any thing wrong in my sentence. HUgo, You please(I beg) read all threads instead of jumping to conclusion. Regards R.D Show quote "Hugo Kornelis" wrote: > On Fri, 12 Aug 2005 22:50:02 -0700, R.D wrote: > > >Hi sql gurus > > > >Thank you for you responses. But all missed my point here. > >I know how we are all concerned with performance and design. > >But my point is not that. > > Hi R.D., > > I think it's the other way around - you missed our point. > > > >The code is generated under the circustances > >1) Procedure should be useful for any database (dynamic) > >2) nulls to zeros should be for all columns and tables( again dynamic) > >3) It should run anywhere. > > The third requirement is where you really go wrong. Any script that does > what you describe at 1 and 2 (i.e. change data in an undefined number of > columns in an undefined number of tables) should actually run NOWHERE. > > Any DBA that allows such a script to run on his or her databases should > go find some education or another job. Maybe even both. > > Best, Hugo > -- > > (Remove _NO_ and _SPAM_ to get my e-mail address) > I wasn't hurt or offended, but I wanted you to understand why I wasn't going
to apply any effort to your problem. I know an order when I see one, even if it's tempered with the word "request." I've used that same tone with shiftless and incompetent employees. Show quote "R.D" <R*@discussions.microsoft.com> wrote in message news:11872391-DCA8-416A-AF94-9EC9153B2FCD@microsoft.com... > Hi All > Thank you John, at leaset you have understood what i was trying to convey. > Sorry for all those who are hurt especially Brian ( if they get affended > when I use the word request) > Brian, I aways have reagard.reverence,love for news groups. That was not an > order, but shows how comfortable i am with the news groups. As you are hurt, > Iam extremely sorry, if you find any thing wrong in my sentence. > > HUgo, You please(I beg) read all threads instead of jumping to conclusion. > > Regards > R.D > > "Hugo Kornelis" wrote: > > > On Fri, 12 Aug 2005 22:50:02 -0700, R.D wrote: > > > > >Hi sql gurus > > > > > >Thank you for you responses. But all missed my point here. > > >I know how we are all concerned with performance and design. > > >But my point is not that. > > > > Hi R.D., > > > > I think it's the other way around - you missed our point. > > > > > > >The code is generated under the circustances > > >1) Procedure should be useful for any database (dynamic) > > >2) nulls to zeros should be for all columns and tables( again dynamic) > > >3) It should run anywhere. > > > > The third requirement is where you really go wrong. Any script that does > > what you describe at 1 and 2 (i.e. change data in an undefined number of > > columns in an undefined number of tables) should actually run NOWHERE. > > > > Any DBA that allows such a script to run on his or her databases should > > go find some education or another job. Maybe even both. > > > > Best, Hugo > > -- > > > > (Remove _NO_ and _SPAM_ to get my e-mail address) > > Brian
Lets us agree to disagee. Regards R.D Show quote "Brian Selzer" wrote: > I wasn't hurt or offended, but I wanted you to understand why I wasn't going > to apply any effort to your problem. I know an order when I see one, even > if it's tempered with the word "request." I've used that same tone with > shiftless and incompetent employees. > > "R.D" <R*@discussions.microsoft.com> wrote in message > news:11872391-DCA8-416A-AF94-9EC9153B2FCD@microsoft.com... > > Hi All > > Thank you John, at leaset you have understood what i was trying to > convey. > > Sorry for all those who are hurt especially Brian ( if they get affended > > when I use the word request) > > Brian, I aways have reagard.reverence,love for news groups. That was not > an > > order, but shows how comfortable i am with the news groups. As you are > hurt, > > Iam extremely sorry, if you find any thing wrong in my sentence. > > > > HUgo, You please(I beg) read all threads instead of jumping to conclusion. > > > > Regards > > R.D > > > > "Hugo Kornelis" wrote: > > > > > On Fri, 12 Aug 2005 22:50:02 -0700, R.D wrote: > > > > > > >Hi sql gurus > > > > > > > >Thank you for you responses. But all missed my point here. > > > >I know how we are all concerned with performance and design. > > > >But my point is not that. > > > > > > Hi R.D., > > > > > > I think it's the other way around - you missed our point. > > > > > > > > > >The code is generated under the circustances > > > >1) Procedure should be useful for any database (dynamic) > > > >2) nulls to zeros should be for all columns and tables( again dynamic) > > > >3) It should run anywhere. > > > > > > The third requirement is where you really go wrong. Any script that does > > > what you describe at 1 and 2 (i.e. change data in an undefined number of > > > columns in an undefined number of tables) should actually run NOWHERE. > > > > > > Any DBA that allows such a script to run on his or her databases should > > > go find some education or another job. Maybe even both. > > > > > > Best, Hugo > > > -- > > > > > > (Remove _NO_ and _SPAM_ to get my e-mail address) > > > > > >  |
|||||||||||||||||||||||
Other interesting topics