Static Parameters

Question

Static Parameters

rmartz

SSC Journeyman

Points: 76
More actions
April 14, 2008 at 9:27 am

#184427

I have a store procedure that I would like to automatically provide results for the Day/Month/Year. I will be putting this up on an ASP.NET website for a news feed. Can you help me configure it so that the Parameter is static and it will not prompt for an input from a users? Here is the stored procedure that I have built right now.
Create PROCEDURE [dbo].[usp_ReportGlassStatsWeb]
@StartDatedatetime
,@EndDatedatetime
AS
BEGIN
SET NOCOUNT ON
SELECT Distinct
sul.liteNumber,
sul.productGroup,
sul.productType,
((sul.blockWidth * sul.blockHeight) / 144) as sqft,
sul.productCTCF,
sul.productDescription,
sul.blockHeight,
sul.blockWidth,
sul.productCode,
sul.unitID,
su.MfgSource,
sul.completed,
sul.completedDate,
sul.routing
FROM
LGCLUSTER.ProductionLG.dbo.t_ScheduleUnitLites as sul
INNER JOIN
LGCLUSTER.ProductionLG.dbo.t_ScheduleUnits as su on
sul.unitID = su.unitID and
sul.scheduleDate = su.scheduleDate and
sul.scheduleNumber = su.scheduleNumber
WHERE sul.completed = 1
and CONVERT(DATETIME, FLOOR(CONVERT(FLOAT, sul.completedDate))) >= CONVERT(DATETIME, FLOOR(CONVERT(FLOAT, @StartDate)))
AND CONVERT(DATETIME, FLOOR(CONVERT(FLOAT, sul.completedDate))) <= CONVERT(DATETIME, FLOOR(CONVERT(FLOAT, @EndDate)))
AND su.mfgSource = 'MFG'
Order By sul.productDescription asc
END

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Mahesh Bote SSCrazy Eights Points: 9107 More actions · Answer 1

[font="Verdana"]Something seems to be contrast in what you required and what you have already written. You have two date parameters in your Store Procedure. The same you have used in WHERE clause. On the other hand you need not want these parameter values to be prompted for input to client. Suppose, if you will use NULL instead, your SELECT statement will return nothing. If possible, provide some more details

Mahesh[/font]

MH-09-AM-8694

Tomm Carr SSCertifiable Points: 5426 More actions · Answer 2

Do you mean something like this:

Create PROCEDURE [dbo].[usp_ReportGlassStatsWeb]

@StartDate datetime = null, -- Allow a null value

@EndDate datetime = null -- Allow a null value

AS BEGIN

if @StartDate is null

set @StartDate = default-start-date;

if @EndDate is null

set @EndDate = default-end-date;

select ...

Tomm Carr
--
Version Normal Form -- http://groups.google.com/group/vrdbms

Mahesh Bote SSCrazy Eights Points: 9107 More actions · Answer 3

or like this ...

(Have stolen Tom's Code :D)

Create PROCEDURE [dbo].[usp_ReportGlassStatsWeb]

@StartDate datetime = null, -- Allow a null value

@EndDate datetime = null -- Allow a null value

AS BEGIN

if @StartDate is null

set @StartDate = GetDate() - {Some days}

if @EndDate is null

set @EndDate = GetDate()

select ...

Mahesh

MH-09-AM-8694

rmartz SSC Journeyman Points: 76 More actions · Answer 4

That helps out a lot. For the start date is there a way to have it start on the 1st for each month, or even first of each year, without having to manually put in that date in the code?

Tomm Carr SSCertifiable Points: 5426 More actions · Answer 5

No problem. To set to beginning of the current month:

set @BeginDate = DateAdd( mm, DateDiff( mm, 0, GetDate()), 0 );

To set to beginning of the current year:

set @BeginDate = DateAdd( yy, DateDiff( yy, 0, GetDate()), 0 );

Tomm Carr
--
Version Normal Form -- http://groups.google.com/group/vrdbms