Getting the Stored Procedure right...

[fenneraj]

Hi all!

I am having probs with my SP's and was wondering if you guys can help.....

I have a set of tables (CERD, LMCD, TOF_CL_DETAILS) that has a two fields TOF_ID (INT) and UPDATE_DONE (BIT).
There are 8 tables with this arrangement, and this is where the prob comes in. The output i am looking to acheive would look like this:

TOF_ID | CERD_DONE | LMCD_DONE | TOF_CL_DETAILS_DONE
17 TRUE TRUE FALSE

I have tried this logic in SP for just one table (with the thought of replicationg it for all the others), but got myself all confused, it seems to compile fine, but does not display any output.

Create Procedure sp_CheckChangeID
@TOF_ID INT
As

DECLARE 
@CERD_TID INT,
@CERD_PRESENT BIT


SELECT @CERD_TID = CERD.TOF_ID
FROM CERD
WHERE UPDATE_DONE = 1
AND
TOF_ID = @TOF_ID

IF @CERD_TID is null 
BEGIN
	SELECT @CERD_PRESENT = 1 
END
ELSE
BEGIN
	SELECT @CERD_PRESENT = 0 
END

Can anyone help?

Cheers Andy

 

[donutman]

[Blue]CREATE[/Blue] [Blue]PROCEDURE[/Blue] sp_CheckChangeID
@TOF_ID [Blue]int[/Blue]
[Blue]AS[/Blue]

[Blue]DECLARE[/Blue] 
@CERD_TID [Blue]int[/Blue][Gray],[/Gray]
@CERD_PRESENT [Blue]bit[/Blue]


[Blue]SELECT[/Blue] @CERD_TID [Gray]=[/Gray] CERD.TOF_ID
[Blue]FROM[/Blue] CERD
[Blue]WHERE[/Blue] UPDATE_DONE [Gray]=[/Gray] 1
[Gray]AND[/Gray]
TOF_ID [Gray]=[/Gray] @TOF_ID

[Blue]IF[/Blue] @CERD_TID [Blue]IS[/Blue] [Gray]NULL[/Gray] 
[Blue]BEGIN[/Blue]
    [Blue]SET[/Blue] @CERD_PRESENT [Gray]=[/Gray] 1 
[Blue]END[/Blue]
[Blue]ELSE[/Blue]
[Blue]BEGIN[/Blue]
    [Blue]SET[/Blue] @CERD_PRESENT [Gray]=[/Gray] 0 
[Blue]END[/Blue]
[Blue]SELECT[/Blue] @CERD_PRESENT

You don't have to use SET instead of SELECT, but it's a good idea to make the distinction between creating a dataset with Select and just setting a variable to a value with Set.
-Karl

[red] Cursors, triggers, user-defined functions and dynamic SQL are an axis of evil![/red]
[green]Life's uncertain...eat dessert first...www.deerfieldbakery.com[/green]

 

[Pattycake245]

This is because all of your selects are used to populate variables.

SELECT @CERD_TID = CERD.TOF_ID
FROM CERD
WHERE UPDATE_DONE = 1
AND
TOF_ID = @TOF_ID

..will put whatever Id into @CERD_TID but will not display it. You would have to put in another line like:

select @CERD_TID

The results you desire don't correlate with your stored proc(unless this is only part of your stored proc). If you want to display something like this:

TOF_ID | CERD_DONE | LMCD_DONE | TOF_CL_DETAILS_DONE
17 TRUE TRUE FALSE

You would need something like

select a.TOF_ID,
case when a.UPDATE_DONE is 1 then 'TRUE' else ''FALSE' end CERD_DONE,
case when b.UPDATE_DONE is 1 then 'TRUE' else ''FALSE' end LMCD_DONE,
case when c.UPDATE_DONE is 1 then 'TRUE' else ''FALSE' end TOF_CL_DETAILS_DONE
from CERD a join LMCD b on a.TOF_ID=b.TOF_ID
join TOF_CL_DETAILS c on a.TOF_ID=c.TOF_ID

Tim