want to run the sp for all not a parm

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

want to run the sp for all not a parm

Author

Topic

AdamWest
Constraint Violating Yak Guru

360 Posts

Posted - 2010-05-24 : 11:44:20

I want to run this sp but to tell it to take all the customers. to ignore the customer parm it has.

exec dbo.spendanalyzer

how can i tell it to take ALL customers for the query, if i put all
it doesn't work
USE [caretech2]
GO

DECLARE @return_value int

EXEC @return_value = [dbo].[SpendAnalyzer]
@customername = N'ALL'

SELECT 'Return Value' = @return_value

GO

vijayisonly
Master Smack Fu Yak Hacker

1836 Posts

Posted - 2010-05-24 : 12:12:38

Show us the SP

AdamWest
Constraint Violating Yak Guru

360 Posts

Posted - 2010-05-24 : 12:17:05

This is the sp. but i can't modify on my own, i have to go through a process with them.

USE [care2]
GO
/****** Object: StoredProcedure [dbo].[SpendAnalyzer] Script Date: 05/24/2010 12:15:44 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[SpendAnalyzer]

@customername varchar(50) = NULL

AS

BEGIN

SET NOCOUNT ON;

SELECT

Department,

Price As Expense,

Quantity,

Customer as CUSTNAME,

item_name as Description,

Date_Created AS DateCreated,

Category,

Category as USCATVAL,

Budget,

po as InvoiceNum

FROM dbo.V_Dashboard_expenses

WHERE

Date_Created >= DATEADD (YYYY, - 2, GETDATE()) AND

Date_Created <= GETDATE() AND

LTrim(RTrim(Customer)) = @customername

ORDER BY date_Created DESC

END

vijayisonly
Master Smack Fu Yak Hacker

1836 Posts

Posted - 2010-05-24 : 13:29:50

You will have to modify your SP. One option is to change it like

WHERE 
Date_Created >= DATEADD (YYYY, - 2, GETDATE()) AND 
Date_Created <= GETDATE() AND 
((LTrim(RTrim(Customer)) = @customername)  OR (@customername  IS NULL))

SO when you dont pass any value to @customername it picks up all customers.

EDIT: Also I would change this

Date_Created >= DATEADD (YYYY, - 2, GETDATE())

to this

Date_Created >= DATEADD (YYYY, - 2,dateadd(d, datediff(d, 0, getdate()), 0))

AdamWest
Constraint Violating Yak Guru

360 Posts

Posted - 2010-05-24 : 14:29:01

Aha good one Vijay!

vijayisonly
Master Smack Fu Yak Hacker

1836 Posts

Posted - 2010-05-24 : 14:37:27

Np. Glad to help.

zstarsales04
Starting Member

20 Posts

Posted - 2010-05-25 : 02:34:32

spam removed

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources