Please start any new threads on our new site at http://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

Our new SQL Server Forums are live! Come on over! We've restricted the ability to create new threads on these forums.

SQL Server Forums
Profile | 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
 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
52326 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
52326 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  
 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.09 seconds. Powered By: Snitz Forums 2000