CODEWITHSUNDEEP
sql-server
soundwave
soundwave

Reputation: 50

SQL IF Condition in Stored Procedure

I am relatively new to advanced database queries and stored procedures, and I am having an issue with the third UPDATE statement in the procedure below, the one within the IF code block. The issue is that I need to add membership numbers to these accounts, so the first iteration of UPDATE adds lp.ID_1 to my temporary table, while the second iteration adds lp.ID_2 to the temporary table. The third iteration needs to take the remaining rows from the original INSERT statement (where ident should still be NULL) and concatenate the ident column with both ID_1 and ID_2, as these accounts have both memberships.

I have tried several conditions in the IF block, but seem to come up short of a solution each time.

USE [database_1]
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

ALTER PROCEDURE [dbo].[stored_procedure_1] @acct char(8) 

AS

WITH X AS 
 (
    select distinct st.ship2, t.name, null AS ident 
    from sales_table st
    INNER JOIN accounts_table t ON st.ship2 = t.acct
    where st.bill2 = @acct
 )
SELECT x.ship2 AS s_acct
      ,x.name
      ,COALESCE(ident, lp.ID_1 +', '+ lp.ID_1 , lp.ID_2 , lp.ID_2 , '' ) AS Ident 
FROM X 
LEFT JOIN lp_table lp ON x.ship2 = lp.acct COLLATE Latin1_General_CS_AS
WHERE COALESCE(ident, lp.ID_2 +', '+ lp.ID_1 , lp.ID_1 , lp.ID_2 , '' ) <> ''

select * from X
where ident is not null

Upvotes: 1

Views: 175

Answers (2)

sarin
sarin

Reputation: 5307

The second update query won't work. The first update query is updating all ident values the #temp_table rows that have a matching "acct" with lp_table. The second query is looking at exactly the same set of rows which you have just updated with the added condition that you are only interested in ones where ident is null.... however in the first step you just made sure none were null. I think what you are looking to do is identify which rows were not updated by the first query assuming some of the idents are updated to Null. I am assuming some of the data in ID_1\ID_2 is null where as the acct columns will always be populated with data. If my assumption is correct you can do the following:

Change your query to:

create table #temp_table
(
    s_acct char(8) null,
    name varchar(50) null,
    ident varchar(10) null
)

insert into #temp_table
select distinct st.ship2, t.name, null
from sales_table st, account_table t
where st.bill2 = @acct
and st.ship2 = t.acct

update #temp_table
set ident = COALESCE(lp.ID_1, lp.ID_2, 'BOTH LPS ARE NULL?')
from lp_table lp
where #temp_table.s_acct = lp.acct COLLATE Latin1_General_CS_AS


select * from #temp_table
where ident is not null

And as per M.Ali's answer it can be improved to remove any temp table useage and do it in one go. I have included this for completeness.

Upvotes: 0

M.Ali
M.Ali

Reputation: 69494

I think this whole update and left joins shebang can be written in one single query without any updates or temp tables 

;WITH X AS 
 (
    select distinct st.ship2, t.name, null AS ident 
    from sales_table st
    INNER JOIN account_table t ON st.ship2 = t.acct
    where st.bill2 = @acct
 )
SELECT x.ship2 AS s_acct
      ,x.name
      ,COALESCE(ident, lp.ID_2 +', '+ lp.ID_1 , lp.ID_1 , lp.ID_2 , '' ) AS Ident 
FROM X 
LEFT JOIN lp_table lp ON x.ship2 = lp.acct COLLATE Latin1_General_CS_AS
WHERE COALESCE(ident, lp.ID_2 +', '+ lp.ID_1 , lp.ID_1 , lp.ID_2 , '' ) <> ''

Upvotes: 2

Related Questions