Set variable if none supplied in stared procedure 1

[sparkbyte]

How would I do this??

set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go


ALTER PROCEDURE [dbo].[usp_DoAll_Parse]
@DateParam1 datetime,
@DateParam2 datetime
AS
SET NOCOUNT ON;

--Select
--DATEADD(dd, 0, DATEADD(mm, DATEDIFF(mm, 0, CURRENT_TIMESTAMP), 0)),
--DATEADD(ss,-1,DATEADD(dd, DATEDIFF(dd, 0, CURRENT_TIMESTAMP), 1))
[highlight]
If @DateParam1 = "" Then
	@DateParam1 = DATEADD(dd, 0, DATEADD(mm, DATEDIFF(mm, 0, CURRENT_TIMESTAMP), 0))
End If

If @DateParam2 = "" Then
	@DateParam2 = DATEADD(ss,-1,DATEADD(dd, DATEDIFF(dd, 0, CURRENT_TIMESTAMP), 1))
End If
[/highlight]
---------------------------------------------------------------
---------------------------------------------------------------
--
--  Create the Parsing Table
--
---------------------------------------------------------------
---------------------------------------------------------------

IF OBJECT_ID('dbo.[tblTrackingParse','U') IS NOT NULL
DROP TABLE dbo.[tblTrackingParse]

CREATE TABLE 
	  tblTrackingParse(Tracking_ID int
	, EmployeeID varchar(50)
	, MachineName varchar(20)
	, BoxNumber varchar(45)
	, FileNumber varchar(25)
	, TrackingDate Datetime
	, TrackingNumberPrefix varchar(2)
	, TrackingNumberAct varchar(6)
	, TrackingNumberShipping varchar(2)
	, TrackingNumberParsel varchar(8))

---------------------------------------------------------------
---------------------------------------------------------------
--
--  Copy records into the Parsing Table.
--
---------------------------------------------------------------
---------------------------------------------------------------

INSERT INTO tblTrackingParse
			(Tracking_ID, EmployeeID, MachineName, BoxNumber, FileNumber, TrackingDate)

SELECT	TOP (100) PERCENT Tracking_ID, EmployeeID, MachineName, BoxNumber, FileNumber, TrackingDate
FROM	tblTrackingTable

WHERE		(TrackingDate BETWEEN CONVERT(DATETIME, @DateParam1, 102) 
				AND	DATEADD(s, -1, CONVERT(DATETIME, @DateParam2, 102))) 
AND			(FileNumber NOT LIKE '.box.end.')

ORDER BY	TrackingDate DESC

---------------------------------------------------------------
---------------------------------------------------------------
--
--  Change all File Numbers to UpperCase
--
---------------------------------------------------------------
---------------------------------------------------------------

UPDATE tblTrackingParse 
	SET tblTrackingParse.FileNumber = 
		Upper(tblTrackingParse.FileNumber)

---------------------------------------------------------------
---------------------------------------------------------------
--
--  Make sure all File Numbers are 9 digits long
--
---------------------------------------------------------------
---------------------------------------------------------------

UPDATE tblTrackingParse SET tblTrackingParse.FileNumber = 
	case 
		when ascii(substring(FileNumber,3,1))> 65 then FileNumber --If the 3-rd char is a letter, return the code
		when ascii(substring(FileNumber,2,1))> 65 then 'Second char LETTER' --If the 2-nd char is a letter, show error text
		when len(FileNumber)<10 then left(FileNumber,1) + replicate('0', 10-len(FileNumber)) 
			+ right(FileNumber, len(FileNumber)-1) --pad with zeroes
--      else 'Too long: ' + cast(len(FileNumber) as varchar(3)) + ' CHARS' 
	end 
FROM tblTrackingParse

---------------------------------------------------------------
---------------------------------------------------------------
--
--  Parse all the UPS tracking Numbers for reporting on
--  the method of shipment.
--
---------------------------------------------------------------
---------------------------------------------------------------

UPDATE tblTrackingParse 
SET tblTrackingParse.TrackingNumberPrefix = upper(substring(BoxNumber,1,2))
	, tblTrackingParse.TrackingNumberAct = upper(substring(BoxNumber,3,6))
	, tblTrackingParse.TrackingNumberShipping = upper(substring(BoxNumber,9,2))
	, tblTrackingParse.TrackingNumberParsel = upper(substring(BoxNumber,11,8))
FROM tblTrackingParse
WHERE (((substring(BoxNumber,1,2))='1Z'))

Thanks

John Fuhrman

 

[gmmastros]

ALTER PROCEDURE [dbo].[usp_DoAll_Parse]
@DateParam1 datetime,
@DateParam2 datetime
AS

@DateParam1 & 2 are defined as datetime data type. Therefore, they cannot be empty strings. It's simply not possible. Because it is defined as datetime, it must be a valid datetime or null. When calling the procedure with an invalid date will cause an error.

You'll need to determine what is actually passed in to the procedure that represents "nothing". Will it be NULL? NULL is probably the best choice. If so...

If @DateParam1 [!]Is NULL[/!] Then
    [!]Set [/!]@DateParam1 = DATEADD(dd, 0, DATEADD(mm, DATEDIFF(mm, 0, CURRENT_TIMESTAMP), 0))
End If

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[sparkbyte]

I have tried that.

If @DateParam1 Is NULL Then
	Set @DateParam1 = DATEADD(dd, 0, DATEADD(mm, DATEDIFF(mm, 0, CURRENT_TIMESTAMP), 0))
End If

If @DateParam2 Is NULL Then
	Set @DateParam2 = DATEADD(ss,-1,DATEADD(dd, DATEDIFF(dd, 0, CURRENT_TIMESTAMP), 1))
End If

Msg 156, Level 15, State 1, Procedure usp_DoAll_Parse, Line 13
Incorrect syntax near the keyword 'Then'.
Msg 156, Level 15, State 1, Procedure usp_DoAll_Parse, Line 15
Incorrect syntax near the keyword 'If'.
Msg 156, Level 15, State 1, Procedure usp_DoAll_Parse, Line 17
Incorrect syntax near the keyword 'Then'.
Msg 156, Level 15, State 1, Procedure usp_DoAll_Parse, Line 19
Incorrect syntax near the keyword 'If'.

Thanks

John Fuhrman

 

[markros]

In T-SQL there is no Then keyword after IF and there is no End If.

You're trying to write VB code, not T-SQL.

PluralSight Learning Library

 

[gmmastros]

Oooppppsss......

I didn't notice the Then's.

When you use an IF block in your code, you do NOT use THEN. You also do not use END IF. If you have multiple statements that you want to conditionally execute, you need to use begin/end. Otherwise, just the statement without begin/end. Like this:

If @DateParam1 Is NULL
    Set @DateParam1 = DATEADD(dd, 0, DATEADD(mm, DATEDIFF(mm, 0, CURRENT_TIMESTAMP), 0))

Or...

If @DateParam1 Is NULL
  [!]Begin[/!]
    Set @DateParam1 = DATEADD(dd, 0, DATEADD(mm, DATEDIFF(mm, 0, CURRENT_TIMESTAMP), 0))
  [!]End[/!]

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[sparkbyte]

Yea!!

If @DateParam1 Is NULL 
  Begin
	Set @DateParam1 = DATEADD(dd, 0, DATEADD(mm, DATEDIFF(mm, 0, CURRENT_TIMESTAMP), 0))
  End
If @DateParam2 Is NULL
  Begin
	Set @DateParam2 = DATEADD(ss,-1,DATEADD(dd, DATEDIFF(dd, 0, CURRENT_TIMESTAMP), 1))
  End

Thanks

John Fuhrman

 

[HeavenCore]

Just a quick point, if the resulting action of an if statement is only 1 line, you dont need the 'begin' 'end', this can make for easier reading on huge SP's where there are 50+ Begin/Ends all over the place. For this specific case an IF is perhaps overkill, consider ISNULL().

Set @DateParam1 = ISNULL(@DateParam1,DATEADD(dd, 0, DATEADD(mm, DATEDIFF(mm, 0, CURRENT_TIMESTAMP), 0)))
Set @DateParam2 = ISNULL(@DateParam2,DATEADD(ss,-1,DATEADD(dd, DATEDIFF(dd, 0, CURRENT_TIMESTAMP), 1))