Which function to use? - SQL Server Forums

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

SQL Server 2005 Forums

Transact-SQL (2005)

Which function to use?

Author

Topic

ganatra.neha
Starting Member

30 Posts

Posted - 2007-11-02 : 12:45:05

I have to write a stored procedure where i input a date and i need the backup size in Mb and its location and other few colums

following is my code

it breaks if i just enter the date without time or if i pass
any other kind of data.

For null values passedin input parameter i am trying to use Coalesce function however it gives me following error

"Coalesece' is not a recognized built-in function name."

for only date also should i use dateadd , datepart or datediff function

Or should i use cast funciton ?

Use Ganatra
Go
Alter Procedure backup1
@a datetime
As
Select Bu.Name , Bu.backup_start_date,Bu.backup_finish_date,
B.physical_drive, (B.backup_size/1000000)As FilesizeMB From msdb.dbo.backupfile
As B Inner Join msdb.dbo.backupset As Bu
On B.backup_set_id = Bu.backup_set_id
Where Bu.backup_start_date = Coalesece (@a)

Kristen
Test

22859 Posts

Posted - 2007-11-02 : 12:49:28

"Coalesece" should be spelt "Coalesce" and needs at least a second parameter.

Kristen

anonymous1
Posting Yak Master

185 Posts

Posted - 2007-11-02 : 12:51:56

in the sql COALESCE is mispelled Coalesece ;)
the purpose of the function is to return the first non null parameter value back
COALESCE (null,1,null,2) = 1
COALESCE (1,2,null,null) = 1
COALESCE (null,null,1,2) = 1

ganatra.neha
Starting Member

30 Posts

Posted - 2007-11-02 : 13:07:11

So how do i go about checking what data is entered in date and what function should i use for checking null value

anonymous1
Posting Yak Master

185 Posts

Posted - 2007-11-02 : 13:29:20

what values do you want to return when the value is null, what values when the value is only a date (versus datetime)?

ganatra.neha
Starting Member

30 Posts

Posted - 2007-11-02 : 13:35:42

When the value is null i just want a message saying that the input parameter is wrong.

Secondly i have modified the code .

Please let me know if i am on right path adn moreover this is the follwoing error message is received
Use Ganatra
Go
Alter Procedure backup1
@a datetime
As
Declare @b varchar
Set @b = cast(Bu.backup_start_date As varchar)
If
@a Is Not Null
Select Bu.Name , Bu.backup_start_date,Bu.backup_finish_date,
B.physical_drive, (B.backup_size/1000000)As FilesizeMB From msdb.dbo.backupfile
As B Inner Join msdb.dbo.backupset As Bu
On B.backup_set_id = Bu.backup_set_id
Where @b = @a
Else
Print'The input parameter has to be a date'

The multi-part identifier "Bu.backup_start_date" could not be bound.

ganatra.neha
Starting Member

30 Posts

Posted - 2007-11-02 : 13:36:43

when the value is only date instead of date and time it should still show the required columns mentioned in the Select statement

anonymous1
Posting Yak Master

185 Posts

Posted - 2007-11-02 : 13:45:16

is this correct?

CREATE PROCEDURE dbo.backupByDate

@backupDate datetime

AS

if @backupDate IS NULL
begin
print 'The input parameter has to be a date'
end
else
begin
SELECT
msdb.dbo.backupset.Name
,msdb.dbo.backupset.backup_start_date
,msdb.dbo.backupset.backup_finish_date
,msdb.dbo.backupfile.physical_drive
,(msdb.dbo.backupfile.backup_size/1000000)As FilesizeMB
FROM
msdb.dbo.backupfile
JOIN
msdb.dbo.backupset
ON msdb.dbo.backupset.backup_set_id = msdb.dbo.backupfile.backup_set_id
WHERE
msdb.dbo.backupset.backup_start_date BETWEEN CONVERT(char(10), @backupDate, 121) AND CONVERT(char(10), @backupDate, 121) + ' 23:59:59.997'
end

ganatra.neha
Starting Member

30 Posts

Posted - 2007-11-02 : 14:52:00

Hi i tried executing the code with

Execute backup1 '32 October 2007 '

Execute backup 'afdgb'

The error it gives is 'Error converting data type varchar to datetime.'

I need to check if anything else besided date is passed i can give an error message.

Can you please tell me which direction or function i should look for

anonymous1
Posting Yak Master

185 Posts

Posted - 2007-11-02 : 15:01:49

sorry misunderstood. change two lines...

@backupDate varchar(max)
if @backupDate IS NULL OR ISDATE(@backupDate) = 0

Kristen
Test

22859 Posts

Posted - 2007-11-02 : 15:10:23

"@backupDate varchar(max)"

You don't really mean MAX here, do you?

Kristen

anonymous1
Posting Yak Master

185 Posts

Posted - 2007-11-02 : 15:22:34

seems very strange why any string should be allowed??? if they want to never have the proc call break it seems like the only datatype available, is there another? were you thinking nvarchar(max)?

ganatra.neha
Starting Member

30 Posts

Posted - 2007-11-02 : 15:24:44

Well IsDAte and Is null function do work but now the problem has become more interesting.

The varaible @a which i have declared as data type datetime.

If i pass 'abc' it gives me error message as follows:

'Error converting data type varchar to datetime.'

If i convert the variable to @a to nvarchar than it works but if i pass datetime in input paramter again it has an error converting datetime data type.

Kristen
Test

22859 Posts

Posted - 2007-11-02 : 15:27:11

"if they want to never have the proc call break it seems like the only datatype available"

Trouble that I perceive is that it will cause all processes to have to use > 8,000 character handling, which is not good for performance.

I think better to set it to a "reasonable maximum size" and, yes, let it break if data wider than that is presented - the "caller" needs fixing in such instances

The original code only checks it for NULL and then converts it to CHAR(10), so I think varchar(MAX) is "excessive"

Kristen

anonymous1
Posting Yak Master

185 Posts

Posted - 2007-11-02 : 15:35:22

make sure you do not populate variable @a until after the else statement, to be sure the parm is a date, also as kriten suggests if you will be using this proc often enough that performance will be a concern you should reduce the length to something more practical

ganatra.neha
Starting Member

30 Posts

Posted - 2007-11-02 : 15:49:13

thanks a lot , The code does work and
and i made a mistake of using nvarchar.

however, i fail to understand how varchar can take the datetime datatype and check whether its or not.

Secondly if i execute the query
Execute backup1 wtih 2007-10-29 19:53:19.000 I get desired value but however if i
Execute backup1 with 2007-10-29 It does not return any value as it is empty.
However is there anyway i can see the same output no matter even if i do not enter time.

anonymous1
Posting Yak Master

185 Posts

Posted - 2007-11-02 : 16:02:51

please post the code you are using

ganatra.neha
Starting Member

30 Posts

Posted - 2007-11-02 : 16:13:01

Use Ganatra
Go
Alter Procedure backup1
@a varchar(max)
As
If
@a Is Null
Or
IsDate(@a)=0
begin
Print 'The input parameter cannot be null or is wrong. It has to be a date'
End
Else
Select Bu.Name , Bu.backup_start_date,Bu.backup_finish_date,
B.physical_drive, (B.backup_size/1000000)As FilesizeMB From msdb.dbo.backupfile
As B Inner Join msdb.dbo.backupset As Bu
On B.backup_set_id = Bu.backup_set_id
Where Bu.backup_start_date = @a

Execute backup1 2007-10-29 19:53:19.000
This works

Excute backup1 2007-10-29
This shows null value

anonymous1
Posting Yak Master

185 Posts

Posted - 2007-11-02 : 16:17:23

change...
Where Bu.backup_start_date = @a

to...
Where Bu.backup_start_date BETWEEN CONVERT(char(10), @a, 121) AND CONVERT(char(10), @a, 121) + ' 23:59:59.997'

ganatra.neha
Starting Member

30 Posts

Posted - 2007-11-02 : 16:23:06

it does work

just the last issue, is it possible that if i pass the date as
Execute backup1 '2007-10-30 '

It can "Print that no back up was taken on this date after not finding the same date in Back Bu.backup_start_date

Kristen
Test

22859 Posts

Posted - 2007-11-02 : 16:37:03

This procedure should have a parameter with a DATETIME datatype. VARCHAR is a bad choice here. The Front End should make sure that a "reasonable" date is chosen - as a fail-safe SQL will raise an error if an invalid date is provided. Any String Date should be passed in the format "yyyymmdd" - note: No punctuation, No hyphens.

The parameter should NOT be called "@a" - the name implies nothing to anyone who will have to perform maintenance, at some future date, on this procedure.

When selecting for a specific day RANGE (i.e. ignoring time) it is better to use


WHERE     Bu.backup_start_date >= DATEADD(Day, DATEDIFF(Day, 0, @DateParam), 0)
      AND Bu.backup_start_date <  DATEADD(Day, DATEDIFF(Day, 0, @DateParam)+1, 0)

to use native datetime datatype and thus avoid time consuming conversion to String.

and ganatra.neha you should take the trouble to format your code carefully so that you take pride in your work, which in turn will lead to better quality code because inappropriate formatting will alert you to mistakes in your code - such as keyword capitalisation not matching the editor's Colour Coding, and indentation not agreeing with Logic Blocks.

Otherwise I will make the assumption that you are just chucking any old rubbish together with the expectation that you can use our good auspices to figure it out for you.

Kristen

Next Page

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources