Stored Procedure and multiple ifs

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

All Forums

SQL Server 2012 Forums

Transact-SQL (2012)

Stored Procedure and multiple ifs

Author

Topic

gomcknez
Starting Member

3 Posts

Posted - 2014-03-02 : 17:04:30

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

52326 Posts

Posted - 2014-03-03 : 04:33:32

[code]
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
[/code]

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

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2014-03-03 : 05:36:09

gomcknez
Starting Member

3 Posts

Posted - 2014-03-03 : 11:37:10

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.

gomcknez
Starting Member

3 Posts

Posted - 2014-03-03 : 11:53:31

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

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources