Stored Procedure - Verification
-
Can anyone help! I am capturing data through scanning (OMR) and it might happen that some of the data is not recognised by the scanner hence it will be left blank, and I do not want variables with blank entries. Now, I am storing that dataset in an SQL database table. All I want is to have a stored procedure that when I run on that data it will pick up the variable that are left blank with no entry. I also want something that will help me say to check consistency in my data set. Here is the pseudocode: - Open table i.e. MyTable - Input a parameter say ID, i.e for searching - If found, locate all the variables for that parameter where the appears to be some blanks. - If blanks are found, then print thru a printer, say Gender empty for person i - Check if married couples are not of different sex, if they are print a message giving out the id for that particular record. Please help me! phokojoe
-
Can anyone help! I am capturing data through scanning (OMR) and it might happen that some of the data is not recognised by the scanner hence it will be left blank, and I do not want variables with blank entries. Now, I am storing that dataset in an SQL database table. All I want is to have a stored procedure that when I run on that data it will pick up the variable that are left blank with no entry. I also want something that will help me say to check consistency in my data set. Here is the pseudocode: - Open table i.e. MyTable - Input a parameter say ID, i.e for searching - If found, locate all the variables for that parameter where the appears to be some blanks. - If blanks are found, then print thru a printer, say Gender empty for person i - Check if married couples are not of different sex, if they are print a message giving out the id for that particular record. Please help me! phokojoe
Okay, I'm having a bit of trouble understanding what you want because the terminology you are using is inconsistent with the domain. What do you mean by "variable"? Do you mean a column in the table? By "input a parameter", do you mean you want to take as a parameter to the stored procedure the name of a column that you want to search? You use the word "variables" a second time, but this time I'm thinking that "row" may be the correct interpretation for this instance. Do you mean you want to return the rows in the table that have a null value in some column (as specified by the user)? Printing is not really a recommended action for a database server to take. It can be done, but it makes for messy applications. Your last part on married couples is more of a business rule rather than a check of the OMR. I am wondering if these checks cannot be made before the data from the OMR gets to the data. Or is this a senario where a lot of bad data has already gone into the database and you need to find it? Answering your question as best I can
CREATE PROCEDURE dbo.SearchForNull
@columnName sysname
AS
DECLARE @dynamicSql nvarchar(4000);IF NOT EXISTS(SELECT * FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = 'MyTable'
AND COLUMN_NAME = @columnName)
BEGIN
-- The column does not exist
RETURN;
ENDSET @dynamicSql = '"SELECT id FROM MyTable WHERE ['+@columnName+'] IS NULL';
EXEC(@dynamicSql);
GOThis stored procedure will return a set containing the id of any row that has a null value for the supplied @columnName. The check of the INFORMATION_SCHEMA.COLUMNS table is important as it helps prevent an error or attack if someone has managed to put in a dodgy column name (either accidentally or maliciously). Does this help?
My: Blog | Photos "Man who stand on hill with mouth open will wait long time for roast duck to drop in." -- Confucious
-
Okay, I'm having a bit of trouble understanding what you want because the terminology you are using is inconsistent with the domain. What do you mean by "variable"? Do you mean a column in the table? By "input a parameter", do you mean you want to take as a parameter to the stored procedure the name of a column that you want to search? You use the word "variables" a second time, but this time I'm thinking that "row" may be the correct interpretation for this instance. Do you mean you want to return the rows in the table that have a null value in some column (as specified by the user)? Printing is not really a recommended action for a database server to take. It can be done, but it makes for messy applications. Your last part on married couples is more of a business rule rather than a check of the OMR. I am wondering if these checks cannot be made before the data from the OMR gets to the data. Or is this a senario where a lot of bad data has already gone into the database and you need to find it? Answering your question as best I can
CREATE PROCEDURE dbo.SearchForNull
@columnName sysname
AS
DECLARE @dynamicSql nvarchar(4000);IF NOT EXISTS(SELECT * FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = 'MyTable'
AND COLUMN_NAME = @columnName)
BEGIN
-- The column does not exist
RETURN;
ENDSET @dynamicSql = '"SELECT id FROM MyTable WHERE ['+@columnName+'] IS NULL';
EXEC(@dynamicSql);
GOThis stored procedure will return a set containing the id of any row that has a null value for the supplied @columnName. The check of the INFORMATION_SCHEMA.COLUMNS table is important as it helps prevent an error or attack if someone has managed to put in a dodgy column name (either accidentally or maliciously). Does this help?
My: Blog | Photos "Man who stand on hill with mouth open will wait long time for roast duck to drop in." -- Confucious
Thank you for the code, it looks like it is going to work just few things. When I try to execute the procedure giving the parameter, it says" Server: Msg 105, Level 15, State 1, Line 1 Unclosed quotation mark before the character string 'SELECT id FROM MyTable WHERE [a1] IS NULL'. -------- By the way let me clarify it. Data is already in the database. This is the example: Id Sex Subject1 Subject2 Subject3 001 1 45 .. 50 002 . 65 80 .. 003 2 .. 90 .. Now all i want is if i supply '001' for the column id, it has to retrive the columnnames that have empty or .. for that particular id. this time it has to retrive subject2 for id 001. etc. Thank you. I am working on that message I get when executing the procedure.:) Let's take a ride. phokojoe
-
Thank you for the code, it looks like it is going to work just few things. When I try to execute the procedure giving the parameter, it says" Server: Msg 105, Level 15, State 1, Line 1 Unclosed quotation mark before the character string 'SELECT id FROM MyTable WHERE [a1] IS NULL'. -------- By the way let me clarify it. Data is already in the database. This is the example: Id Sex Subject1 Subject2 Subject3 001 1 45 .. 50 002 . 65 80 .. 003 2 .. 90 .. Now all i want is if i supply '001' for the column id, it has to retrive the columnnames that have empty or .. for that particular id. this time it has to retrive subject2 for id 001. etc. Thank you. I am working on that message I get when executing the procedure.:) Let's take a ride. phokojoe
phokojoe wrote: When I try to execute the procedure giving the parameter, it says" Server: Msg 105, Level 15, State 1, Line 1 Unclosed quotation mark before the character string 'SELECT id FROM MyTable WHERE [a1] IS NULL'. That's what I get by typing in SQL directly onto the forum. The " symbol just before the SELECT statement should be removed.
My: Blog | Photos "Man who stand on hill with mouth open will wait long time for roast duck to drop in." -- Confucious
-
Thank you for the code, it looks like it is going to work just few things. When I try to execute the procedure giving the parameter, it says" Server: Msg 105, Level 15, State 1, Line 1 Unclosed quotation mark before the character string 'SELECT id FROM MyTable WHERE [a1] IS NULL'. -------- By the way let me clarify it. Data is already in the database. This is the example: Id Sex Subject1 Subject2 Subject3 001 1 45 .. 50 002 . 65 80 .. 003 2 .. 90 .. Now all i want is if i supply '001' for the column id, it has to retrive the columnnames that have empty or .. for that particular id. this time it has to retrive subject2 for id 001. etc. Thank you. I am working on that message I get when executing the procedure.:) Let's take a ride. phokojoe
This stored procedure should be a bit easier - no dynamic SQL - but probably a bit more tedious as it is a bit repetative.
CREATE PROCEDURE dbo.GetEmptyColumnNames
@id int
AS
SELECT 'Sex' AS ColumnName
FROM MyTable
WHERE (Sex IS NULL OR Sex='..') AND id = @id
UNION
SELECT 'Subject1' AS ColumnName
FROM MyTable
WHERE (Subject1 IS NULL OR Subject1='..') AND id = @id
UNION
SELECT 'Subject2' AS ColumnName
FROM MyTable
WHERE (Subject2 IS NULL OR Subject2='..') AND id = @id
UNION -- And so on for all the columns in your table that you want to check.
GOThis just performs a number of
SELECTstatements andUNIONs them all together into one result set. Does this help?
My: Blog | Photos "Man who stand on hill with mouth open will wait long time for roast duck to drop in." -- Confucious
-
This stored procedure should be a bit easier - no dynamic SQL - but probably a bit more tedious as it is a bit repetative.
CREATE PROCEDURE dbo.GetEmptyColumnNames
@id int
AS
SELECT 'Sex' AS ColumnName
FROM MyTable
WHERE (Sex IS NULL OR Sex='..') AND id = @id
UNION
SELECT 'Subject1' AS ColumnName
FROM MyTable
WHERE (Subject1 IS NULL OR Subject1='..') AND id = @id
UNION
SELECT 'Subject2' AS ColumnName
FROM MyTable
WHERE (Subject2 IS NULL OR Subject2='..') AND id = @id
UNION -- And so on for all the columns in your table that you want to check.
GOThis just performs a number of
SELECTstatements andUNIONs them all together into one result set. Does this help?
My: Blog | Photos "Man who stand on hill with mouth open will wait long time for roast duck to drop in." -- Confucious
well this looks like it will work. Hoever, I foxed the first procedure 'searchfornull' and when I give it the parameter it returns all the columns names without records. But as for procedur GetEmptyColumnNames, it returns only the first column name in the first select but not records and it looks like it does not return all other coulmn names. e.g. This is how it looks: CREATE PROCEDURE dbo.GetEmptyColumnNames @id numeric AS Select 'a1' as A1 FROM hhdata WHERE (a1 IS NuLL Or a1='..') AND refno = @id UNION Select 'a2' as A2 FROM hhdata WHERE (a2 IS NuLL Or a2='..') AND refno = @id UNION Select 'a3' as A3 FROM hhdata WHERE (a3 IS NuLL Or a3='.') AND refno = @id exec getemptycolumnnames 043701137010041 where 043701137010041 is a record in the refno and of course there are many records in the refno column, I thought it will return all the column names in that 043701137010041 which are null or '..'. Infact that is what I want to see. ------------returns A1 not even a record in a1. phokojoe
-
well this looks like it will work. Hoever, I foxed the first procedure 'searchfornull' and when I give it the parameter it returns all the columns names without records. But as for procedur GetEmptyColumnNames, it returns only the first column name in the first select but not records and it looks like it does not return all other coulmn names. e.g. This is how it looks: CREATE PROCEDURE dbo.GetEmptyColumnNames @id numeric AS Select 'a1' as A1 FROM hhdata WHERE (a1 IS NuLL Or a1='..') AND refno = @id UNION Select 'a2' as A2 FROM hhdata WHERE (a2 IS NuLL Or a2='..') AND refno = @id UNION Select 'a3' as A3 FROM hhdata WHERE (a3 IS NuLL Or a3='.') AND refno = @id exec getemptycolumnnames 043701137010041 where 043701137010041 is a record in the refno and of course there are many records in the refno column, I thought it will return all the column names in that 043701137010041 which are null or '..'. Infact that is what I want to see. ------------returns A1 not even a record in a1. phokojoe
I notice that you are putting things like SELECT 'a1' AS A1... SELECT 'a2' AS A2... Remember this is
UNIONing all these into one set so it might confuse it* if you give all these different column names, they are supposed to be returning the result into a single column So, my first suggestion (and its a long shot) is to ensure that the A1, A2, A3 are all the same column name (If I remember I said ColumnName)DROP PROCEDURE dbo.GetEmptyColumnNames
CREATE PROCEDURE dbo.GetEmptyColumnNames
@id numeric
AS
Select 'a1' as ColumnName
FROM hhdata
WHERE (a1 IS NULL Or a1='..') AND refno = @id
UNION
Select 'a2' as ColumnName
FROM hhdata
WHERE (a2 IS NULL Or a2='..') AND refno = @id
UNION
Select 'a3' as ColumnName
FROM hhdata
WHERE (a3 IS NULL Or a3='.') AND refno = @idphokojoe wrote: where 043701137010041 is a record in the refno and of course there are many records in the refno column, I thought it will return all the column names in that 043701137010041 which are null or '..'. Infact that is what I want to see. I'm getting confused by your terminology. "043701137010041 is a record in the refno" --> a column value in the refno column?? "there are many records in the refno column" --> there are many rows with the same refno??? (A column does not contain records, it contains values) * DISCLAIMER: I've never thought of putting different column names here, so I don't know what the result would be, but it could explain why you only get the first result back.
My: Blog | Photos "Man who stand on hill with mouth open will wait long time for roast duck to drop in." -- Confucious