SQL Server Forums
Profile | Register | 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		  New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

AdamWest
Constraint Violating Yak Guru

USA
302 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
302 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
302 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  
 New 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.08 seconds. Powered By: Snitz Forums 2000