SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2012 Forums
 Transact-SQL (2012)
 Stored Procedure and multiple ifs
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

gomcknez
Starting Member

3 Posts

Posted - 03/02/2014 :  17:04:30  Show Profile  Reply with Quote
I have the following procedure.

What I am trying to accomplish is:
Run first select, if rowcount >0 then that is my answer. If it is =0 do the next select. If the rowcount >0 then that is my answer. If it is = 0 then do the third select.

What happens is if first select is >0 it skips second select but still executes third select. I'm not quite sure how to structure the logic to get this to work correctly.

CREATE PROCEDURE [dbo].[getCostTemplate]
@DWR NVARCHAR(14),
@JOB NVARCHAR(16),
@CLIENT NVARCHAR(16),
@templateID INT OUTPUT,
@templateDesc NVARCHAR(50) OUTPUT,
@costID INT OUTPUT,
@expID INT OUTPUT
AS
BEGIN
SET NOCOUNT OFF;

BEGIN
SELECT @templateID = i_CosttemplateID, @templateDesc = s_CostTemplateDescription from tbl_CostTemplate
where i_CosttemplateID in
(select i_CostTemplateID from tbl_Cost_Client_Templates where s_JobNo=@JOB
AND b_IsPrimaryTemplate = 'True')
if (@@ROWCOUNT = 0)
select @templateID = i_CosttemplateID, @templateDesc = s_CostTemplateDescription from tbl_CostTemplate
where i_CostTemplateID in (select i_CostTemplateID from tbl_Cost_Client_Templates
where s_Client=@CLIENT AND b_IsPrimaryTemplate = 'True' AND Prov = [dbo].getProvince(@JOB))
if (@@ROWCOUNT = 0)
select @templateID = i_CosttemplateID, @templateDesc = s_CostTemplateDescription from tbl_CostTemplate
where i_CostTemplateID in (select i_CostTemplateID from tbl_Cost_Client_Templates
where Prov=[dbo].getProvince(@JOB) AND s_Client = 'Default')
END
SELECT @costID = int_ViewsCostID from tbl_Cost_Client_Templates
where i_CostTemplateID = @templateID
SELECT @expID = int_ViewsExpID from tbl_Cost_Client_Templates
where i_CostTemplateID = @templateID
END

visakh16
Very Important crosS Applying yaK Herder

India
52325 Posts

Posted - 03/03/2014 :  04:33:32  Show Profile  Reply with Quote

CREATE PROCEDURE [dbo].[getCostTemplate] 
@DWR NVARCHAR(14),
@JOB NVARCHAR(16),
@CLIENT NVARCHAR(16),
@templateID INT OUTPUT,
@templateDesc NVARCHAR(50) OUTPUT,
@costID INT OUTPUT,
@expID INT OUTPUT
AS
BEGIN
SET NOCOUNT OFF;

BEGIN
DECLARE @rowsAffected int
SELECT @templateID = i_CosttemplateID, @templateDesc = s_CostTemplateDescription from tbl_CostTemplate 
where i_CosttemplateID in 
(select i_CostTemplateID from tbl_Cost_Client_Templates where s_JobNo=@JOB
AND b_IsPrimaryTemplate = 'True')

SET @rowsAffected = @@ROWCOUNT

if (@rowsAffected  = 0)
select @templateID = i_CosttemplateID, @templateDesc = s_CostTemplateDescription from tbl_CostTemplate
where i_CostTemplateID in (select i_CostTemplateID from tbl_Cost_Client_Templates
where s_Client=@CLIENT AND b_IsPrimaryTemplate = 'True' AND Prov = [dbo].getProvince(@JOB))

SET @rowsAffected = @@ROWCOUNT

if (@rowsAffected  = 0)
select @templateID = i_CosttemplateID, @templateDesc = s_CostTemplateDescription from tbl_CostTemplate
where i_CostTemplateID in (select i_CostTemplateID from tbl_Cost_Client_Templates
where Prov=[dbo].getProvince(@JOB) AND s_Client = 'Default')
END
SELECT @costID = int_ViewsCostID from tbl_Cost_Client_Templates 
where i_CostTemplateID = @templateID
SELECT @expID = int_ViewsExpID from tbl_Cost_Client_Templates 
where i_CostTemplateID = @templateID
END


------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52325 Posts

Posted - 03/03/2014 :  05:36:09  Show Profile  Reply with Quote

CREATE PROCEDURE [dbo].[getCostTemplate] 
@DWR NVARCHAR(14),
@JOB NVARCHAR(16),
@CLIENT NVARCHAR(16),
@templateID INT OUTPUT,
@templateDesc NVARCHAR(50) OUTPUT,
@costID INT OUTPUT,
@expID INT OUTPUT
AS
BEGIN
SET NOCOUNT OFF;

BEGIN
DECLARE @rowsAffected int
SELECT @templateID = i_CosttemplateID, @templateDesc = s_CostTemplateDescription from tbl_CostTemplate 
where i_CosttemplateID in 
(select i_CostTemplateID from tbl_Cost_Client_Templates where s_JobNo=@JOB
AND b_IsPrimaryTemplate = 'True')

SET @rowsAffected = @@ROWCOUNT

if (@rowsAffected  = 0)
select @templateID = i_CosttemplateID, @templateDesc = s_CostTemplateDescription from tbl_CostTemplate
where i_CostTemplateID in (select i_CostTemplateID from tbl_Cost_Client_Templates
where s_Client=@CLIENT AND b_IsPrimaryTemplate = 'True' AND Prov = [dbo].getProvince(@JOB))

SET @rowsAffected = @@ROWCOUNT

if (@rowsAffected  = 0)
select @templateID = i_CosttemplateID, @templateDesc = s_CostTemplateDescription from tbl_CostTemplate
where i_CostTemplateID in (select i_CostTemplateID from tbl_Cost_Client_Templates
where Prov=[dbo].getProvince(@JOB) AND s_Client = 'Default')
END
SELECT @costID = int_ViewsCostID from tbl_Cost_Client_Templates 
where i_CostTemplateID = @templateID
SELECT @expID = int_ViewsExpID from tbl_Cost_Client_Templates 
where i_CostTemplateID = @templateID
END


------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page

gomcknez
Starting Member

3 Posts

Posted - 03/03/2014 :  11:37:10  Show Profile  Reply with Quote
quote:
Originally posted by visakh16


CREATE PROCEDURE [dbo].[getCostTemplate] 
@DWR NVARCHAR(14),
@JOB NVARCHAR(16),
@CLIENT NVARCHAR(16),
@templateID INT OUTPUT,
@templateDesc NVARCHAR(50) OUTPUT,
@costID INT OUTPUT,
@expID INT OUTPUT
AS
BEGIN
SET NOCOUNT OFF;

BEGIN
DECLARE @rowsAffected int
SELECT @templateID = i_CosttemplateID, @templateDesc = s_CostTemplateDescription from tbl_CostTemplate 
where i_CosttemplateID in 
(select i_CostTemplateID from tbl_Cost_Client_Templates where s_JobNo=@JOB
AND b_IsPrimaryTemplate = 'True')

SET @rowsAffected = @@ROWCOUNT

if (@rowsAffected  = 0)
select @templateID = i_CosttemplateID, @templateDesc = s_CostTemplateDescription from tbl_CostTemplate
where i_CostTemplateID in (select i_CostTemplateID from tbl_Cost_Client_Templates
where s_Client=@CLIENT AND b_IsPrimaryTemplate = 'True' AND Prov = [dbo].getProvince(@JOB))

SET @rowsAffected = @@ROWCOUNT

if (@rowsAffected  = 0)
select @templateID = i_CosttemplateID, @templateDesc = s_CostTemplateDescription from tbl_CostTemplate
where i_CostTemplateID in (select i_CostTemplateID from tbl_Cost_Client_Templates
where Prov=[dbo].getProvince(@JOB) AND s_Client = 'Default')
END
SELECT @costID = int_ViewsCostID from tbl_Cost_Client_Templates 
where i_CostTemplateID = @templateID
SELECT @expID = int_ViewsExpID from tbl_Cost_Client_Templates 
where i_CostTemplateID = @templateID
END


------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs




Thank you for the answer. Unfortunately I get the exact same result as before. If I make a procedure that just contains statements 1 and 2, Iget the correct result which is the answer form select statement 1. If I add the third select statement to the procedure and query with the same arguments, I get the third select statement results.
Go to Top of Page

gomcknez
Starting Member

3 Posts

Posted - 03/03/2014 :  11:53:31  Show Profile  Reply with Quote
This works.

CREATE PROCEDURE [dbo].[getCostTemplate]
@DWR NVARCHAR(14),
@JOB NVARCHAR(16),
@CLIENT NVARCHAR(16),
@templateID INT OUTPUT,
@templateDesc NVARCHAR(50) OUTPUT,
@costID INT OUTPUT,
@expID INT OUTPUT
AS
BEGIN
SET NOCOUNT OFF;

BEGIN

DECLARE @rowsAffected int
SELECT @templateID = i_CosttemplateID, @templateDesc = s_CostTemplateDescription from tbl_CostTemplate
where i_CosttemplateID in
(select i_CostTemplateID from tbl_Cost_Client_Templates where s_JobNo=@JOB
AND b_IsPrimaryTemplate = 'True')
SET @rowsAffected = @@ROWCOUNT
if (@rowsAffected = 0)
BEGIN
select @templateID = i_CosttemplateID, @templateDesc = s_CostTemplateDescription from tbl_CostTemplate
where i_CostTemplateID in (select i_CostTemplateID from tbl_Cost_Client_Templates
where s_Client=@CLIENT AND b_IsPrimaryTemplate = 'True' AND Prov = [dbo].getProvince(@JOB))
SET @rowsAffected = @@ROWCOUNT
END
if (@rowsAffected = 0)
Begin
select @templateID = i_CosttemplateID, @templateDesc = s_CostTemplateDescription from tbl_CostTemplate
where i_CostTemplateID in (select i_CostTemplateID from tbl_Cost_Client_Templates
where Prov=[dbo].getProvince(@JOB) AND s_Client = 'Default')
End
END

SELECT @costID = int_ViewsCostID from tbl_Cost_Client_Templates
where i_CostTemplateID = @templateID
SELECT @expID = int_ViewsExpID from tbl_Cost_Client_Templates
where i_CostTemplateID = @templateID
END



GO
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.06 seconds. Powered By: Snitz Forums 2000