how to know if there is no budget data in this sp? - SQL Server Forums

Home | Weblogs | Forums

Site Sponsored By: SQLDSC - SQL Server Desired State Configuration

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

General SQL Server Forums

New to SQL Server Programming

how to know if there is no budget data in this sp?

Author

Topic

AdamWest
Constraint Violating Yak Guru

360 Posts

Posted - 2011-01-26 : 18:11:06

I want to be able to know if after running this Stored proc there was no data for budgets for the customer.
this is used in an app, and if they have no budgets, certains buttons and processes should not display. the only way to know would be here, unless to create a column in customer table that they don't 'do' budgets obviously that is not preferred method.

GO
/****** Object: StoredProcedure [dbo].[GetMonthlyBudgets] Script Date: 01/26/2011 18:06:41 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

-- drop procedure GetMonthlyBudgets
-- exec GetMonthlyBudgets 104
ALTER PROCEDURE [dbo].[GetMonthlyBudgets]

@customerId INT

AS

BEGIN

SET NOCOUNT ON;

CREATE TABLE #Months (
Month DATETIME
)

DECLARE @tmpdate DATETIME = CONVERT(VARCHAR(7), GETDATE(), 126) + '-01T00:00:00' -- the first of the start month
DECLARE @i INT = 0

WHILE @i < 24
BEGIN
INSERT INTO #Months VALUES (@tmpdate)
SET @tmpdate = DATEADD(MONTH, -1, @tmpdate)
SET @i = @i + 1
END

SELECT m.Month
,b.DepartmentId
,LTRIM(RTRIM(d.DepartmentName)) AS DepartmentName
,b.CategoryId
,LTRIM(RTRIM(c.CategoryLongName)) AS CategoryName
,b.BudgetAmount AS BudgetAmount
FROM #Months m
LEFT JOIN Budget b ON m.Month >= BudgetDateFrom and m.Month < BudgetDateTo
LEFT JOIN Categories c ON c.CategoryId = b.CategoryId
LEFT JOIN Departments d ON d.DepartmentId = b.DepartmentId --and d.CustomerId = b.CustomerId
WHERE b.CustomerId = @customerId
ORDER BY m.Month,c.CategoryName

END

--- select * from Budget

--- select * from Customers or

MIK_2008
Master Smack Fu Yak Hacker

1054 Posts

Posted - 2011-01-27 : 05:58:04

well i think if you execute the stored procedure you would be able to see that which of the rows are not having budget data. Since the stored procedure will return a data set (having one or more rows), which can be looked up again for the rows that have NULL values under the budget field. Simply control those rows at the application level as required.

Jim Beam
Posting Yak Master

137 Posts

Posted - 2011-01-27 : 06:05:29

Basic question, why do you have to explicitly declare a variable in some sprocs, but not in others, like the '@customerId' one above?

MIK_2008
Master Smack Fu Yak Hacker

1054 Posts

Posted - 2011-01-27 : 06:12:00

@jim is it your answer to adam's question or asking Another Question :)

Jim Beam
Posting Yak Master

137 Posts

Posted - 2011-01-27 : 06:19:32

quote:
Originally posted by MIK_2008

@jim is it your answer to adam's question or asking Another Question :)

@Mik Yes, I'm asking a question. When I'm answering a question I don't like to wrap it up inside another question, personally.

MIK_2008
Master Smack Fu Yak Hacker

1054 Posts

Posted - 2011-01-27 : 06:31:52

ok here's your Question:

"Basic question, why do you have to explicitly declare a variable in some sprocs, but not in others, like the '@customerId' one above?"

declaring variable(s) at the specified location refers to the Term "Parameters". Hope you'll know the purpose of parameters. In some cases you might not require to filter the data set, rather would be interested in all data. That would be the case where you will not find a parameter as in the above one.

E.g. i need to run a stored procedure that list down total number of employees in Employees Table so the SP would be like as

Create Proc spGetCountofAllStaff
As
Select count(*) from tblEmployees

However if you want to have count of staff in a specific department then you will need to introduce a parameter so that the query can give you a result as required

Create Proc spGetCountofAllStaff_Dept
@DeptID int
As
Select count(*) from tblEmployees Where DeptID=@DeptID

While calling the second one you must provide a Department ID e.g. Exec spGetCountofAllStaff_Dept 20
But not for the first one Exec spGetCountofAllStaff

Jim Beam
Posting Yak Master

137 Posts

Posted - 2011-01-27 : 09:10:15

Ahhhhhhh!! So to summarise, would it be correct to say that a Parameter and a Local Variable are similar in that they both handle values that change, but differ in that:

1) Values for the former are passed in by the calling process/user, whereas those for the latter are generated internally;

2) The former need not be explictly declared by using the word 'Declare', but the latter must?

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources