correct syntax to define a variable ? - SQL Server Forums

Home | Weblogs | Forums

Site Sponsored By: SQLDSC - SQL Server Desired State Configuration

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

General SQL Server Forums

New to SQL Server Programming

correct syntax to define a variable ?

Author

Topic

java148
Yak Posting Veteran

63 Posts

Posted - 2011-10-31 : 20:25:02

what is the correct syntax to define a variable ? master.dbo.currentDateTime() is a function to return current date time.


declare @backup_time varchar(20);
--set @backup_time = select master.dbo.currentDateTime();
--select @backup_time =  master.dbo.currentDateTime();
select master.dbo.currentDateTime() into @backup_time
print "backup at @backup_time";

pduffin
Yak Posting Veteran

68 Posts

Posted - 2011-10-31 : 21:16:23

select @backup_time = master.dbo.currentDateTime()

or select @backup_time = getdate()

Likes to run, hates the runs!

java148
Yak Posting Veteran

63 Posts

Posted - 2011-10-31 : 22:58:47

Move forward from here, I coded this, still has syntax error.

Please help. thanks.


declare @backup_time varchar(20);
declare @dbpath varchar(20);

select @backup_time = master.dbo.currentDateTime();
print 'backup at '+ @backup_time;

SET @dbpath = 'C:\mssql2008\backup\mts_agent_' +  @backup_time + '.trn WITH INIT' ; 
print 'transaction log backup ' + @dbpath;
backup log mtsnew to disk = @dbpath

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2011-10-31 : 23:23:54

what data type does master.dbo.currentDateTime() returns ?

KH
[spoiler]Time is always against us[/spoiler]

jassi.singh
Posting Yak Master

122 Posts

Posted - 2011-11-01 : 04:18:59

declare @backup_time datetime;
declare @dbpath varchar(20);

select @backup_time = GETDATE();
print 'backup at '+ CAST(@backup_time AS VARCHAR(max));

SET @dbpath = 'C:\mssql2008\backup\mts_agent_' + CAST(@backup_time AS VARCHAR(max)) + '.trn WITH INIT' ;
print 'transaction log backup ' + @dbpath;
backup log mtsnew to disk = @dbpath

Please mark answer as accepted if it helped you.

Thanks,
Jassi Singh

java148
Yak Posting Veteran

63 Posts

Posted - 2011-11-01 : 21:46:54

It doesn't work. My function will return varchar(20), so should not have cast problem .


 FUNCTION [dbo].[currentDateTime]()
RETURNS varchar(20)
WITH EXECUTE AS CALLER
AS
BEGIN
     DECLARE @currentDateTime varchar(20);
     SET @currentDateTime= replace(replace(replace(convert(varchar(19), getdate(), 121), '-', ''), ' ', ''), ':', '');
     RETURN(@currentDateTime);
END;

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2011-11-01 : 21:54:53

Checked the print output of @dbpath in your query ?

quote:

Originally posted by java148

Move forward from here, I coded this, still has syntax error.

Please help. thanks.


declare @backup_time varchar(20);
declare @dbpath varchar(200);

select @backup_time = master.dbo.currentDateTime();
print 'backup at '+ @backup_time;

SET @dbpath = 'C:\mssql2008\backup\mts_agent_' +  @backup_time + '.trn WITH INIT' ; 
print 'transaction log backup ' + @dbpath;
backup log mtsnew to disk = @dbpath

KH
[spoiler]Time is always against us[/spoiler]

java148
Yak Posting Veteran

63 Posts

Posted - 2011-11-01 : 23:18:53

yes, you are right, it works now. so I change to varchar(max)

Thanks

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2011-11-01 : 23:21:48

welcome

KH
[spoiler]Time is always against us[/spoiler]

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2011-11-03 : 06:20:32

Also refer this post
http://beyondrelational.com/blogs/madhivanan/archive/2007/12/04/column-length-and-data-length.aspx

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources