need help constructing a stored procedure

you said it to be insert stored procedure.
does this stored procedure will perform insert into second table, and then insert into first table accordingly ?

or this stored procedure will insert into first table, base on data already exists in second table (and maybe with PersonID as input parameter) ?

Inset only to First table, NO updates..

>>>or this stored procedure will insert into first table, base on data already exists in second table (and maybe with PersonID as input parameter)  YES

I think there should be a link between 2 tables which is personID then, check first if  for example
code 28 exists --this is the prmdate  field  in the second table   which is the Ndate value in first table,
if exist ignore it , if not insert code 28 and the Ndate in first table..
This would be the same method for inserting record  for   codes  60,37,49 in first table
For every code inserted, the dates or 'Yes; values are also inserted with it.

so, the mapping is as following ?

KeyCode   2nd table Field    1st Table Field
60             Completed           Alpha
28             prmDate              NDate
49             Date_Paid            XDate
37             report_date         YDate

CORRECT!  those are only 3 examples  of the many codes that have to be inserted in the first table along with dates and alpha values, So one personID can have many records in the first table

Since I don't know what is your data structure.
I just assume by your given example that, if there is no data, it will be blank string.
and since it can be blank string, data type should be char, not datetime or int etc.

here is the code

CREATE PROCEDURE CheckFirstTable
	@PersonID int
AS
Declare @Alpha varchar(20)
Declare @NDate varchar(20)
Declare @XDate varchar(20)
Declare @YDate varchar(20)

-- get value for each variable
SELECT @Alpha = Completed, @NDate = prmDate, @XDate = Date_Paid, @YDate = report_date
FROM SecondTable
WHERE PersonID = @PersonID

-- prepare expected data table for firsttable
Declare @Temp TABLE (KeyCode int, KeyValue varchar(20))
IF @Alpha <> '' INSERT INTO @Temp VALUES (60, @Alpha)
IF @NDate <> '' INSERT INTO @Temp VALUES (28, @Ndate)
IF @XDate <> '' INSERT INTO @Temp VALUES (49, @Xdate)
IF @YDate <> '' INSERT INTO @Temp VALUES (37, @Ydate)

-- insert data into firsttable, only for KeyCode which is not there
INSERT INTO FirstTable
SELECT
	0, 1, @PersonID, T.KeyCode, 
	CASE T.KeyCode WHEN 60 THEN T.KeyValue ELSE '' END,
	CASE T.KeyCode WHEN 28 THEN T.KeyValue ELSE '' END,
	CASE T.KeyCode WHEN 49 THEN T.KeyValue ELSE '' END,
	CASE T.KeyCode WHEN 37 THEN T.KeyValue ELSE '' END
FROM
	@Temp T
WHERE
	KeyCode NOT IN (SELECT KeyCode FROM FirstTable WHERE PersonID = @PersonID)

GO

Select all Open in new window

Is @Temp a temporary table or can I substitute it to the real table..?

@temp is temporary table "variable"
you may change it to temporary table

Declare @Temp TABLE (KeyCode int, KeyValue varchar(20)) --> CREATE TABLE #Temp (KeyCode int, KeyValue varchar(20))

and replace all @Temp with #Temp

but if you want to change it to Actual Table, then you might need to delete old data in the table before insert value into it.

What do you mean by delete data from  table?
What is a better method ? Use a temp variable or #Temp
Table ? Maybe I'm misunderstanding the concept ?
Of temp variable or temp table

uses either kind of temp table is fine.
(just some older version of MSSQL may not support table variable)

the delete I mention, is in case you create permanent table (so, you don't have to create it inside stored procedure)
if you are using temp table as give example, we don't have to worry about delete, since table always new create.

the date params are datetime types not varchar-fyi

the code as well as the values corresponding to it like
keycode 28  is 'prmdate' '4/1/2009' should be inserted as well

btw whatis T.keyvalue in the temp table

modified table structure for First table
Instead of this...

First Table has ff. structure and sample data

coID  smPID  PersonID         KeyCode Alpha            NDate             XDate                  YDate
0         1       32322                28                              4/1/2009
0          1      32322                49                                                       8/4/2005
0          1      32322                60              Yes                                          
0         1       92888                28                                7/1/2005    
0          1      92888                 60             Yes          
0          1      92888               37                                                                                  10/1/2009

.. there willl be one field to handle all dates


First Table has ff. structure and sample data

coID  smPID  PersonID         KeyCode Alpha            Nfield             XDate
0                 32322                28                                                      4/1/2009
0          1      32322                49                                                       8/4/2005
0          1      32322                60              Yes                                          
0         1       92888                28                                                         7/1/2005    
0          1      92888                 60             Yes          
0          1      92888               37                                                         10/1/2009

Since "Date" field is datetime data type,
then back to sample table in your question.

the Second Table,  for PersonID = 32322, the report_date have no value display.
in database, what is this field stored?  is it NULL value?

First of All,when Ipq copied and pasted,the fields were misaligned.All the date fields in the second table -prmdate,ndate,report date will be mapped to the xdate field of the second table,but with different keycodes.The alpha field will remain same.

Alright, here is what I'm assume for Second Table
1. Completed, will stored either 'Yes'   or empty string  ''
2. prmDate/Date_Paid/report_date  will either contain some date value or NULL

PersonID	prmDate		Completed	Date_paid	report_date
82333		6/1/2000				4/5/2001		8/1/2001
32322		4/1/2009	Yes		8/4/2005		NULL
92888		7/1/2005	Yes		NULL		10/1/2009

Select all Open in new window

Alright, here is what I'm assume for Second Table
1. Completed, will stored either 'Yes'   or empty string  ''
2. prmDate/Date_Paid/report_date  will either contain some date value or NULL

PersonID	prmDate		Completed	Date_paid	report_date
82333		6/1/2000			4/5/2001	8/1/2001
32322		4/1/2009	Yes		8/4/2005	NULL
92888		7/1/2005	Yes		NULL		10/1/2009

Select all Open in new window

and here is new stored procedure.

CREATE PROCEDURE CheckFirstTable
	@PersonID int
AS
Declare @Alpha varchar(20)
Declare @NDate datetime
Declare @XDate datetime
Declare @YDate datetime

-- get value for each variable
SELECT @Alpha = Completed, @NDate = prmDate, @XDate = Date_Paid, @YDate = report_date
FROM SecondTable
WHERE PersonID = @PersonID

-- prepare expected data table for firsttable
Declare @Temp TABLE (KeyCode int, Alpha varchar(20), XDate datetime)
IF @Alpha <> '' INSERT INTO @Temp VALUES (60, @Alpha, NULL)
IF @NDate IS NOT NULL INSERT INTO @Temp VALUES (28, NULL, @Ndate)
IF @XDate IS NOT NULL INSERT INTO @Temp VALUES (49, NULL, @Xdate)
IF @YDate IS NOT NULL INSERT INTO @Temp VALUES (37, NULL, @Ydate)

-- insert data into firsttable, only for KeyCode which is not there
INSERT INTO FirstTable (coID, smPID, PersonID, KeyCode, Alpha, XDate)
SELECT
	0, 1, @PersonID, T.KeyCode, 
	CASE T.KeyCode WHEN 60 THEN T.Alpha ELSE '' END,
	CASE T.KeyCode
		WHEN 28 THEN T.XDate ELSE NULL END,
		WHEN 49 THEN T.XDate ELSE NULL END,
		WHEN 37 THEN T.XDate ELSE NULL END
FROM
	@Temp T
WHERE
	KeyCode NOT IN (SELECT KeyCode FROM FirstTable WHERE PersonID = @PersonID)

GO

Select all Open in new window

If null,does it write Null or "?It has to " not Null

getting error 156--Incorrect syntax near the word 'When'

ok syntax is good but will test it. thanks

Got an insert fail error
Cannot insert the value NULL int column 'XDate'
first table;column does not allow nulls. statment terminated

Great! found the error but still testing

I tried to change a date of one in the second table and run it and I get the error

"Syntax error converting the varchar value 'yes    ' to a column of data type int."
notice how 'yes got extra spaces

As mention earlier that, I don't know your table structure
and how the data stored in case you consider there is no value

anyway, for error convert 'yes   ' to int
mean Completed is char, while Alpha is int
then I will assume (again) that Alpha = 1 if yes

CREATE PROCEDURE CheckFirstTable
	@PersonID int
AS
Declare @Completed varchar(20)
Declare @prmDate datetime
Declare @Date_Paid datetime
Declare @report_date datetime

-- get value for each variable
SELECT @Completed = Completed, @prmDate = prmDate, @Date_Paid = Date_Paid, @report_date = report_date
FROM SecondTable
WHERE PersonID = @PersonID

-- prepare expected data table for firsttable
Declare @Temp TABLE (KeyCode int, Alpha int, XDate datetime)
IF RTRIM(@Completed) = 'yes' INSERT INTO @Temp VALUES (60, 1, NULL)
IF @prmDate IS NOT NULL INSERT INTO @Temp VALUES (28, NULL, @prmdate)
IF @Date_Paid IS NOT NULL INSERT INTO @Temp VALUES (49, NULL, @Date_Paid)
IF @report_date IS NOT NULL INSERT INTO @Temp VALUES (37, NULL, @report_date)

-- insert data into firsttable, only for KeyCode which is not there
INSERT INTO FirstTable (coID, smPID, PersonID, KeyCode, Alpha, XDate)
SELECT
	0, 1, @PersonID, T.KeyCode, 
	CASE T.KeyCode WHEN 60 THEN T.Alpha ELSE 0 END,
	CASE WHEN T.KeyCode IN (28, 49, 37) THEN T.XDate ELSE NULL END
FROM
	@Temp T
WHERE
	KeyCode NOT IN (SELECT KeyCode FROM FirstTable WHERE PersonID = @PersonID)

GO

Select all Open in new window

alpha and completed have botg data types of CHAR.

I have 4 keys in the first table which are

coID  smPID  PersonID         KeyCode

Using the previous sp (not the one above) I was able to insert the records.  However for testing purposes, I changed the date paid under that personID,  and that's when I get the error.

What I'd like the stored proc to do is to not to have the ability to change the xdate or yes values if they already exist in the first table even though it was changed in the second table. So there should be somekind of checking. I don;t know if the 4 primary keys would already be enough to handle that.
OK, I will test your new stored procedure.

my given code design to perform insert into first table for any missing related information base on second table.
if already exists, will just ignore,  no matter value is matched with second table or not.

the code perform following step
1. read data from second table for input @PersonID and put in into variable
    - you should adjust the data type to match with your table
2. check from variable to consider if it contain data, if it is, insert into @Temp table
    - you have to adjust the check condition
      for ex if RTRIM(@Completed) = 'yes'
               if @prmDate IS NOT NULL           -- if no data = NULL
               if @prmDate = '01 JAN 1900'       -- if no data contain date value as 01 JAN 1900
3. insert into first table for KeyCode which not there in first table for selected @PersonID
    - I don't know the condition of coID or smPID, I just put fixed value base on your given example
    - the CASE command, is to design which value to put into which field
    so, field Alpha will put value if KeyCode = 60
    and field XDate will put value if KeyCode in 28, 37, 48
    - you may need to adjust the value after ELSE to record proper value for your "no data"

I wish this give you clear understanding on what is my process try to do, So you can adjust it to meet your requirement.
Let me know if anything unclear and further help need,

Here's my tbl structure on First Table where the insert happens.hope it helps


coID       smallint   for this I enter 0
smPID      tinyint     always 1
PersonID   int  
KeyCode    char
Alpha      char
Nfield     int     always 0
XDate      datetime
cc         char       always ''
cn         tinyint     always 0  
bc         char         always ''
bn          tinyint      always 0
ba         int           always  0
hw         char         always 'K'

BTW, the alpha field should be char because it takes differenr description for different keycodes

I got it all working now,I hope

Just to let you know,I stick with keeping Alpha and Completed fields to be chars data types. I modified and added the line If Rtrim('yes').....Thanks a lot. I never  done anything like this before. Fantastic!

So far brilliant!

glad to hear you start to find some way out of the maze :)

let me know later if still something you can't solve.