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
 General SQL Server Forums
 New to SQL Server Programming
 want to run the sp for all not a parm
 Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

AdamWest
Constraint Violating Yak Guru

USA
360 Posts

Posted - 05/24/2010 :  11:44:20  Show Profile  Reply with Quote
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

Edited by - AdamWest on 05/24/2010 11:51:32

vijayisonly
Flowing Fount of Yak Knowledge

USA
1836 Posts

Posted - 05/24/2010 :  12:12:38  Show Profile  Reply with Quote
Show us the SP
Go to Top of Page

AdamWest
Constraint Violating Yak Guru

USA
360 Posts

Posted - 05/24/2010 :  12:17:05  Show Profile  Reply with Quote
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

Go to Top of Page

vijayisonly
Flowing Fount of Yak Knowledge

USA
1836 Posts

Posted - 05/24/2010 :  13:29:50  Show Profile  Reply with Quote
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))

Edited by - vijayisonly on 05/24/2010 14:22:54
Go to Top of Page

AdamWest
Constraint Violating Yak Guru

USA
360 Posts

Posted - 05/24/2010 :  14:29:01  Show Profile  Reply with Quote
Aha good one Vijay!
Go to Top of Page

vijayisonly
Flowing Fount of Yak Knowledge

USA
1836 Posts

Posted - 05/24/2010 :  14:37:27  Show Profile  Reply with Quote
Np. Glad to help.
Go to Top of Page

zstarsales04
Starting Member

20 Posts

Posted - 05/25/2010 :  02:34:32  Show Profile  Visit zstarsales04's Homepage  Reply with Quote
spam removed
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.07 seconds. Powered By: Snitz Forums 2000