Can I do an "alter table add column", with an existing named default?

Hello folks,
Shouldn't I be able to do this? I'm not able to get the syntax to work
Alter table TableName add ColumnName tinyint not null default
DefaultZero
This works:
Alter table TableName add ColumnName tinyint not null default (0)
...but I need to immediately drop the default programatically, and for
that I need the default name, so I'd like to name it myself.
I tried this:
EXEC sp_unbindefault 'TableName .ColumnName'
but got the error "Cannot unbind from 'TableName .ColumnName'. Use
ALTER TABLE DROP CONSTRAINT.
I guess I could query the system tables to figure out the name, but
would prefer not to if I can avoid it.
thanks for any ideas!
SylviaHere's an example with the proper syntax:
ALTER TABLE TableName
ADD ColumnName tinyint NOT NULL
CONSTRAINT DF_TableName_ColumnName DEFAULT 0
Hope this helps.
Dan Guzman
SQL Server MVP
"Sylvia" <Puget4753@.yahoo.com> wrote in message
news:1116288687.747349.22520@.g47g2000cwa.googlegroups.com...
> Hello folks,
> Shouldn't I be able to do this? I'm not able to get the syntax to work
> Alter table TableName add ColumnName tinyint not null default
> DefaultZero
> This works:
> Alter table TableName add ColumnName tinyint not null default (0)
> ...but I need to immediately drop the default programatically, and for
> that I need the default name, so I'd like to name it myself.
> I tried this:
> EXEC sp_unbindefault 'TableName .ColumnName'
> but got the error "Cannot unbind from 'TableName .ColumnName'. Use
> ALTER TABLE DROP CONSTRAINT.
> I guess I could query the system tables to figure out the name, but
> would prefer not to if I can avoid it.
> thanks for any ideas!
> Sylvia
>|||thanks - this works perfectly!

Can I disable log for a table?

Hi,
I Oracle we can disable redo log for a table by using this command:
ALTER TABLE your_table_name NOLOGGING
Do we have such capability in either 2000 or 2005 versions of SQL server?
Thanks,
MaxNo. You can change the recovery model but only at the database level.
--
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Toronto, ON Canada
.
"Maxwell2006" <alanalan@.newsgroup.nospam> wrote in message
news:O7XrOfaYGHA.3448@.TK2MSFTNGP04.phx.gbl...
Hi,
I Oracle we can disable redo log for a table by using this command:
ALTER TABLE your_table_name NOLOGGING
Do we have such capability in either 2000 or 2005 versions of SQL server?
Thanks,
Max|||"Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message
news:u3H$8laYGHA.1204@.TK2MSFTNGP04.phx.gbl...
> No. You can change the recovery model but only at the database level.
True, but to be complete, you can change it to a simple recovery model but
some logging still occurs.
So you can't elimiante it cmopletely.
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> SQL Server MVP
> Toronto, ON Canada
> .
> "Maxwell2006" <alanalan@.newsgroup.nospam> wrote in message
> news:O7XrOfaYGHA.3448@.TK2MSFTNGP04.phx.gbl...
> Hi,
> I Oracle we can disable redo log for a table by using this command:
>
> ALTER TABLE your_table_name NOLOGGING
>
> Do we have such capability in either 2000 or 2005 versions of SQL server?
>
> Thanks,
> Max
>|||"Maxwell2006" <alanalan@.newsgroup.nospam> wrote in message
news:O7XrOfaYGHA.3448@.TK2MSFTNGP04.phx.gbl...
> Hi,
> I Oracle we can disable redo log for a table by using this command:
>
> ALTER TABLE your_table_name NOLOGGING
>
> Do we have such capability in either 2000 or 2005 versions of SQL server?
>
> Thanks,
> Max
>
You could put the table in its own database and use the Simple Recovery
model in that database. You can then create the reference to that table as a
view or synonym in another database.
Think about the consequences for your data integrity if you ever have to
restore the transaction log.
--
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--|||You can eliminate logging completely, but you have to place the database in
a read only state to do so.
--
Mike
http://www.solidqualitylearning.com
Disclaimer: This communication is an original work and represents my sole
views on the subject. It does not represent the views of any other person
or entity either by inference or direct reference.
"Greg D. Moore (Strider)" <mooregr_deleteth1s@.greenms.com> wrote in message
news:e7sg7xaYGHA.4688@.TK2MSFTNGP04.phx.gbl...
> "Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message
> news:u3H$8laYGHA.1204@.TK2MSFTNGP04.phx.gbl...
>> No. You can change the recovery model but only at the database level.
> True, but to be complete, you can change it to a simple recovery model but
> some logging still occurs.
> So you can't elimiante it cmopletely.
>
>> --
>> Tom
>> ----
>> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
>> SQL Server MVP
>> Toronto, ON Canada
>> .
>> "Maxwell2006" <alanalan@.newsgroup.nospam> wrote in message
>> news:O7XrOfaYGHA.3448@.TK2MSFTNGP04.phx.gbl...
>> Hi,
>> I Oracle we can disable redo log for a table by using this command:
>>
>> ALTER TABLE your_table_name NOLOGGING
>>
>> Do we have such capability in either 2000 or 2005 versions of SQL server?
>>
>> Thanks,
>> Max
>>
>|||"Michael Hotek" <mike@.solidqualitylearning.com> wrote in message
news:u5zmgmcYGHA.4796@.TK2MSFTNGP04.phx.gbl...
> You can eliminate logging completely, but you have to place the database
in
> a read only state to do so.
Details, details. ;-)
But I have to admit that's true.
> --
> Mike
> http://www.solidqualitylearning.com
> Disclaimer: This communication is an original work and represents my sole
> views on the subject. It does not represent the views of any other person
> or entity either by inference or direct reference.
>
> "Greg D. Moore (Strider)" <mooregr_deleteth1s@.greenms.com> wrote in
message
> news:e7sg7xaYGHA.4688@.TK2MSFTNGP04.phx.gbl...
> >
> > "Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message
> > news:u3H$8laYGHA.1204@.TK2MSFTNGP04.phx.gbl...
> >> No. You can change the recovery model but only at the database level.
> >
> > True, but to be complete, you can change it to a simple recovery model
but
> > some logging still occurs.
> >
> > So you can't elimiante it cmopletely.
> >
> >
> >>
> >> --
> >> Tom
> >>
> >> ----
> >> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> >> SQL Server MVP
> >> Toronto, ON Canada
> >> .
> >> "Maxwell2006" <alanalan@.newsgroup.nospam> wrote in message
> >> news:O7XrOfaYGHA.3448@.TK2MSFTNGP04.phx.gbl...
> >> Hi,
> >>
> >> I Oracle we can disable redo log for a table by using this command:
> >>
> >>
> >>
> >> ALTER TABLE your_table_name NOLOGGING
> >>
> >>
> >>
> >> Do we have such capability in either 2000 or 2005 versions of SQL
server?
> >>
> >>
> >>
> >> Thanks,
> >>
> >> Max
> >>
> >>
> >
> >
>

Can I Alter A Report that has already been deployed to my Report Manager

I have some OLAP reports in which I have to alter the MDX statements each month, in the VS2005 report designer. They are deployed to the web and they contain Descriptions and Subscriptions which I create once they get to the web. The way I am currently updating them each month is by editing the reports in VS2005 and re-deploying them to the Reports Server. I then have to add the descriptions again and create new Subscriptions because the orig reports are deleted when the new ones, with the same name are deployed. There has to be a way that I can just alter the existing reports in the web environment and not have to recreate the Subscriptions each month. Any help would be appreciated.

If you go to the actual report that you want to update in report manager and go to the general tab there is a button that says update that you can select and then upload the new version. Then all of your descriptions and your history will remain and you just upload the new version of the report.

Properties-General-Update-Do not forget to hit apply when finished choosing rdl file.

can anybody tell me why the following stored procedure is not inserting into my database t

ALTER PROCEDUREAddListAndReturnNewIDValue (

@.EditorIdint,

@.CategoryIDint,

@.ListTitlenvarchar(50),

@.Blurbnvarchar(250),

@.FileNamenvarchar(50),

@.ByLinenvarchar(50),

@.HTMLCopynvarchar(MAX),

@.MainStorybit,

@.MainStoryImageFilenvarchar(50),

@.Publishbit,

@.PublishDatesmalldatetime,

@.ListIdint OUTPUT

)

AS

-- Insert the record into the database

INSERT INTOshortlist (EditorId,CategoryID,ListTitle,Blurb,FileName,ByLine,HTMLCopy,MainStory,MainStoryImageFile,Publish,PublishDate)

VALUES(@.EditorID,@.CategoryID,@.ListTitle,@.Blurb, @.FileName, @.ByLine, @.HTMLCopy, @.MainStory, @.MainStoryImageFile,@.Publish,@.PublishDate)

-- Read the just-inserted ProductID into @.NewProductID

SET@.ListId =SCOPE_IDENTITY()

here is the sqlDataSource

<asp:SqlDataSource

id="srcShortList"

ConnectionString="<%$ ConnectionStrings:ShortList %>"

SelectCommand="SELECT Id,EditorId,CategoryID,ListTitle,Blurb,FileName, ByLine, HTMLCopy, MainStory, MainStoryImageFile, Publish,PublishDate,Date,Deleted FROM shortlist"

InsertCommand="AddProductAndReturnNewProductIDValue"

SelectCommandType="StoredProcedure"

UpdateCommand="UPDATE shortlist SET CategoryID=@.CategoryID,ListTitle=@.ListTitle,Blurb=@.Blurb,ByLine=@.ByLine,HTMLCopy=@.HTMLCopy,MainStory=@.MainStory,Publish=@.Publish,PublishDate=@.PublishDate WHERE Id=@.Id"

Runat="server">

<SelectParameters>

<asp:QueryStringParameter

Name="Id"

QueryStringField="Id"/>

</SelectParameters>

</asp:SqlDataSource>

Is there a section for your InsertParameters, if you've given everything there then it looks like the paramaters just aren't being created.

Test your stored procedure works by calling it from SQL Server Management Studio, i.e.

exec AddListAndReturnNewIDValue
@.EditorId=1,@.CategoryID = 1,@.ListTitle= 'Hello world',@.Blurb = 'This is a test',@.FileName ='testfile.txt',@.ByLine ='Me',@.HTMLCopy = '<html></html>',@.MainStory= 1,
@.MainStoryImageFile ='myfile.txt',@.Publish =0,@.PublishDate = '2007/09/07 12:00:00'

If that works the problem is in your code, and I'd guess it's the Insert parameters. Try creating the SQL Data Source again from scratch.

|||

Thanks for help

can alter table drop multiple columns

i tried to run this script:
ALTER TABLE RDKCOCUS
DROP COLUMN [Administration Executive],
DROP COLUMN [Administrator],
DROP COLUMN [Auditor],
DROP COLUMN [Chairman],
DROP COLUMN [Chief Executive Officer],
DROP COLUMN [Chief Financial Officer],
DROP COLUMN [Chief Operating Officer],
DROP COLUMN [Co-Chairman/Vice Chairman],
DROP COLUMN [Controller],
DROP COLUMN [Corporate Communications Executive],
DROP COLUMN [Corporate Secretary],
DROP COLUMN [Data Processing Executive],
DROP COLUMN [Director],
DROP COLUMN [Executive Director],
DROP COLUMN [Executive Vice President],
DROP COLUMN [Finance Executive],
DROP COLUMN [General Counsel],
DROP COLUMN [Human Resources Executive],
DROP COLUMN [Manager],
DROP COLUMN [Marketing Executive],
DROP COLUMN [Officer],
DROP COLUMN [Operations Executive],
DROP COLUMN [Owner],
DROP COLUMN [Partner],
DROP COLUMN [Plant Manager],
DROP COLUMN [President],
DROP COLUMN [Principal],
DROP COLUMN [Publisher],
DROP COLUMN [Purchasing Agent],
DROP COLUMN [Sales Executive],
DROP COLUMN [Senior Vice President],
DROP COLUMN [Telecommunications Executive],
DROP COLUMN [Treasurer],
DROP COLUMN [Vice President]
and i got the error, incorrect syntax near keyword drop
so i tried this script instead:
ALTER TABLE RDKCOCUS
DROP COLUMN [Administration Executive]
and it worked. so i guess my question is, do i really have to rerun the
script for every columnlist them one after another spereated by comma's
example
CREATE TABLE doc_exe ( column_a INT, column_a_un int, colC int)
GO
select * from doc_exe
GO
ALTER TABLE doc_exe DROP COLUMN column_a_un,colC
GO
select * from doc_exe
GO
drop table doc_exe
GO
http://sqlservercode.blogspot.com/|||tyty
"SQL" <denis.gobo@.gmail.com> wrote in message
news:1136924539.257435.5670@.f14g2000cwb.googlegroups.com...
> list them one after another spereated by comma's
> example
> CREATE TABLE doc_exe ( column_a INT, column_a_un int, colC int)
> GO
> select * from doc_exe
> GO
> ALTER TABLE doc_exe DROP COLUMN column_a_un,colC
> GO
> select * from doc_exe
> GO
> drop table doc_exe
> GO
> http://sqlservercode.blogspot.com/
>|||Cool. I didn't realize that this would work :)
----
Louis Davidson - http://spaces.msn.com/members/drsql/
SQL Server MVP
"Arguments are to be avoided: they are always vulgar and often convincing."
(Oscar Wilde)
"SQL" <denis.gobo@.gmail.com> wrote in message
news:1136924539.257435.5670@.f14g2000cwb.googlegroups.com...
> list them one after another spereated by comma's
> example
> CREATE TABLE doc_exe ( column_a INT, column_a_un int, colC int)
> GO
> select * from doc_exe
> GO
> ALTER TABLE doc_exe DROP COLUMN column_a_un,colC
> GO
> select * from doc_exe
> GO
> drop table doc_exe
> GO
> http://sqlservercode.blogspot.com/
>|||Use drop column followed by a list of fields, seperated by commas. Dont
repeat the drop column keywords for each column.
ALTER TABLE RDKCOCUS
DROP COLUMN [Administration Executive],
[Administrator],
[Auditor],
[Chairman],....
"Abraham Andres Luna" <abe@.rdk.com> wrote in message
news:uy2OKKiFGHA.2708@.TK2MSFTNGP11.phx.gbl...
> i tried to run this script:
> ALTER TABLE RDKCOCUS
> DROP COLUMN [Administration Executive],
> DROP COLUMN [Administrator],
> DROP COLUMN [Auditor],
> DROP COLUMN [Chairman],
> DROP COLUMN [Chief Executive Officer],
> DROP COLUMN [Chief Financial Officer],
> DROP COLUMN [Chief Operating Officer],
> DROP COLUMN [Co-Chairman/Vice Chairman],
> DROP COLUMN [Controller],
> DROP COLUMN [Corporate Communications Executive],
> DROP COLUMN [Corporate Secretary],
> DROP COLUMN [Data Processing Executive],
> DROP COLUMN [Director],
> DROP COLUMN [Executive Director],
> DROP COLUMN [Executive Vice President],
> DROP COLUMN [Finance Executive],
> DROP COLUMN [General Counsel],
> DROP COLUMN [Human Resources Executive],
> DROP COLUMN [Manager],
> DROP COLUMN [Marketing Executive],
> DROP COLUMN [Officer],
> DROP COLUMN [Operations Executive],
> DROP COLUMN [Owner],
> DROP COLUMN [Partner],
> DROP COLUMN [Plant Manager],
> DROP COLUMN [President],
> DROP COLUMN [Principal],
> DROP COLUMN [Publisher],
> DROP COLUMN [Purchasing Agent],
> DROP COLUMN [Sales Executive],
> DROP COLUMN [Senior Vice President],
> DROP COLUMN [Telecommunications Executive],
> DROP COLUMN [Treasurer],
> DROP COLUMN [Vice President]
>
> and i got the error, incorrect syntax near keyword drop
> so i tried this script instead:
> ALTER TABLE RDKCOCUS
> DROP COLUMN [Administration Executive]
> and it worked. so i guess my question is, do i really have to rerun the
> script for every column
>|||I remember reading somewhere that you can drop multiple objects in 1
shot
Like this
CREATE TABLE doc_exe1 ( name INT)
GO
CREATE TABLE doc_exe2 ( name INT)
GO
DROP TABLE doc_exe1,doc_exe2
I just tried it with columns and it seems to work

Can Alter (current) Database?

I have a *.sql script that creates database tables, and I need to modify the database to enable the service broker. In addition, the actual name of the database is not known in advance - it is set per instance of the application.

I know I need to do:

ALTER DATABASE dbname SET ENABLE_BROKER

But I must avoid including the name of the database in the script. I did wonder if this would work:

DECLARE @.DB varchar(50)

SELECT @.DB = DB_NAME()

ALTER DATABASE @.DB SET ENABLE_BROKER

But I just get a syntax error. Presumably this also rules out setting the database name as a parameter to the script (SqlParameter stuff)

The only option I can think of is dynamically creating the statement, either in T-SQL or in the calling .NET environment.

Any thoughts?

Ruth

Hi,

I guess you have to create a dynamic statement to make it work, something like:

DECLARE @.DB varchar(50)

SELECT @.DB = DB_NAME()

DECLARE @.SQLString VARCHAR(200)
SET @.SQLString = ' ALTER DATABASE ' + @.DB + 'SET ENABLE_BROKER'
EXEC(@.SqlString)

HTH, jens Suessmeyer.

http://www.sqlserver2005.de

|||

Thanks, that looks good. You can of course replace @.DB with DB_NAME() in the SET.

Ruth

|||

Dynamic SQL is the only solution I know of. Make sure you protect yourself against SQL injection problems (e.g. use QUOTENAME on the database name). Also, ALTER DATABASE requires exclusive lock on the database, see http://blogs.msdn.com/remusrusanu/archive/2006/01/30/519685.aspx

HTH,
~ Remus

|||Remus,

I assumed that the value returned from DB_NAME() would be acceptable in SQL. Probably a bad assumption, really!

So, I did have:
SET @.AlterStmt = 'ALTER DATABASE ' + DB_NAME() + ' SET ENABLE_BROKER'

but I should really have this?
SET @.AlterStmt = 'ALTER DATABASE ' + QUOTENAME(DB_NAME()) + ' SET ENABLE_BROKER'

Thinking about injection, what is the best method to use when creating SQL on the fly. I don't want to use SqlParameters for everything as they obfuscate the code significantly. Is there an System.Data.SqlClient equivalent of QUOTENAME() ?

Ruth
|||

DB_NAME() is fine, it doesn't need to be passed into QUOTENAME. When I formulated the reply, Jens' post simply wasn't there and I didn't see it and I assumed the database name comes in as an argument.

You should worry about SQL Injection if the database name comes from an external (potentialy untrusted) source like a web form text field.

There is no equivalent to QUOTENAME, I usually use the simplest String.Replace method, like this:

string quotedDbName = "[" + dbnameVariable.Replace("]","]]") + "]";

HTH,
~ Remus

Calling user-created functions with default values

The documentation talks about default values, but gives no examples. I'm
trying to get this to work...
ALTER FUNCTION FirstDayOfYear(@.date datetime = getdate) RETURNS datetime
BEGIN
RETURN convert(datetime, '1/1/' + convert(varchar, YEAR(@.date)))
END
They say you need to pass in "default", but I can't figure it out. I tried...
select dbo.FirstDayOfYear(default)
select default dbo.FirstDayOfYear()
select dbo.FirstDayOfYear() default
Any pointers?
Maury
I do not know why SQL Server let us create the function with the keyword
GETDATE as a parameter's default value. GETDATE is a function, so it should
be:
...
@.date datetime = getdate()
...
but then sql server give an error and this make sense. Try using a number
and you will see that it works (using default keyword when you call the
function).
alter FUNCTION FirstDayOfYear(
@.date datetime = 32500
)
RETURNS datetime
as
BEGIN
RETURN convert(datetime, '1/1/' + convert(varchar, YEAR(@.date)))
END
go
select dbo.FirstDayOfYear(default)
go
I think you have to get rid of the default and allways pass a value to this
function.
AMB
"Maury Markowitz" wrote:

> The documentation talks about default values, but gives no examples. I'm
> trying to get this to work...
> ALTER FUNCTION FirstDayOfYear(@.date datetime = getdate) RETURNS datetime
> BEGIN
> RETURN convert(datetime, '1/1/' + convert(varchar, YEAR(@.date)))
> END
> They say you need to pass in "default", but I can't figure it out. I tried...
> select dbo.FirstDayOfYear(default)
> select default dbo.FirstDayOfYear()
> select dbo.FirstDayOfYear() default
> Any pointers?
> Maury
|||That's actually looking for the string, 'getdate' -- actually calling the
GETDATE function requires parens (GETDATE()) -- however, SQL Server will not
accept that (I'm not sure why). You'll have to actually call the function
with GETDATE() as the argument to do what you need.
Adam Machanic
SQL Server MVP
http://www.datamanipulation.net
"Maury Markowitz" <MauryMarkowitz@.discussions.microsoft.com> wrote in
message news:24DEC598-40A2-491E-A975-D5E9026BD10D@.microsoft.com...
> The documentation talks about default values, but gives no examples. I'm
> trying to get this to work...
> ALTER FUNCTION FirstDayOfYear(@.date datetime = getdate) RETURNS datetime
> BEGIN
> RETURN convert(datetime, '1/1/' + convert(varchar, YEAR(@.date)))
> END
> They say you need to pass in "default", but I can't figure it out. I
tried...
> select dbo.FirstDayOfYear(default)
> select default dbo.FirstDayOfYear()
> select dbo.FirstDayOfYear() default
> Any pointers?
> Maury
|||"Adam Machanic" wrote:

> That's actually looking for the string, 'getdate' -- actually calling the
> GETDATE function requires parens (GETDATE()) -- however, SQL Server will not
> accept that (I'm not sure why). You'll have to actually call the function
> with GETDATE() as the argument to do what you need.
Got it. It doesn't really need to have this feature -- a default that is --
but it would make the callee syntax a little nicer.
Maury

Calling user-created functions with default values

The documentation talks about default values, but gives no examples. I'm
trying to get this to work...
ALTER FUNCTION FirstDayOfYear(@.date datetime = getdate) RETURNS datetime
BEGIN
RETURN convert(datetime, '1/1/' + convert(varchar, YEAR(@.date)))
END
They say you need to pass in "default", but I can't figure it out. I tried..
.
select dbo.FirstDayOfYear(default)
select default dbo.FirstDayOfYear()
select dbo.FirstDayOfYear() default
Any pointers?
MauryI do not know why SQL Server let us create the function with the keyword
GETDATE as a parameter's default value. GETDATE is a function, so it should
be:
...
@.date datetime = getdate()
...
but then sql server give an error and this make sense. Try using a number
and you will see that it works (using default keyword when you call the
function).
alter FUNCTION FirstDayOfYear(
@.date datetime = 32500
)
RETURNS datetime
as
BEGIN
RETURN convert(datetime, '1/1/' + convert(varchar, YEAR(@.date)))
END
go
select dbo.FirstDayOfYear(default)
go
I think you have to get rid of the default and allways pass a value to this
function.
AMB
"Maury Markowitz" wrote:

> The documentation talks about default values, but gives no examples. I'm
> trying to get this to work...
> ALTER FUNCTION FirstDayOfYear(@.date datetime = getdate) RETURNS datetime
> BEGIN
> RETURN convert(datetime, '1/1/' + convert(varchar, YEAR(@.date)))
> END
> They say you need to pass in "default", but I can't figure it out. I tried
..
> select dbo.FirstDayOfYear(default)
> select default dbo.FirstDayOfYear()
> select dbo.FirstDayOfYear() default
> Any pointers?
> Maury|||That's actually looking for the string, 'getdate' -- actually calling the
GETDATE function requires parens (GETDATE()) -- however, SQL Server will not
accept that (I'm not sure why). You'll have to actually call the function
with GETDATE() as the argument to do what you need.
Adam Machanic
SQL Server MVP
http://www.datamanipulation.net
--
"Maury Markowitz" <MauryMarkowitz@.discussions.microsoft.com> wrote in
message news:24DEC598-40A2-491E-A975-D5E9026BD10D@.microsoft.com...
> The documentation talks about default values, but gives no examples. I'm
> trying to get this to work...
> ALTER FUNCTION FirstDayOfYear(@.date datetime = getdate) RETURNS datetime
> BEGIN
> RETURN convert(datetime, '1/1/' + convert(varchar, YEAR(@.date)))
> END
> They say you need to pass in "default", but I can't figure it out. I
tried...
> select dbo.FirstDayOfYear(default)
> select default dbo.FirstDayOfYear()
> select dbo.FirstDayOfYear() default
> Any pointers?
> Maury|||"Adam Machanic" wrote:

> That's actually looking for the string, 'getdate' -- actually calling the
> GETDATE function requires parens (GETDATE()) -- however, SQL Server will n
ot
> accept that (I'm not sure why). You'll have to actually call the function
> with GETDATE() as the argument to do what you need.
Got it. It doesn't really need to have this feature -- a default that is --
but it would make the callee syntax a little nicer.
Maury

Calling user-created functions with default values

The documentation talks about default values, but gives no examples. I'm
trying to get this to work...
ALTER FUNCTION FirstDayOfYear(@.date datetime = getdate) RETURNS datetime
BEGIN
RETURN convert(datetime, '1/1/' + convert(varchar, YEAR(@.date)))
END
They say you need to pass in "default", but I can't figure it out. I tried...
select dbo.FirstDayOfYear(default)
select default dbo.FirstDayOfYear()
select dbo.FirstDayOfYear() default
Any pointers?
MauryI do not know why SQL Server let us create the function with the keyword
GETDATE as a parameter's default value. GETDATE is a function, so it should
be:
...
@.date datetime = getdate()
...
but then sql server give an error and this make sense. Try using a number
and you will see that it works (using default keyword when you call the
function).
alter FUNCTION FirstDayOfYear(
@.date datetime = 32500
)
RETURNS datetime
as
BEGIN
RETURN convert(datetime, '1/1/' + convert(varchar, YEAR(@.date)))
END
go
select dbo.FirstDayOfYear(default)
go
I think you have to get rid of the default and allways pass a value to this
function.
AMB
"Maury Markowitz" wrote:
> The documentation talks about default values, but gives no examples. I'm
> trying to get this to work...
> ALTER FUNCTION FirstDayOfYear(@.date datetime = getdate) RETURNS datetime
> BEGIN
> RETURN convert(datetime, '1/1/' + convert(varchar, YEAR(@.date)))
> END
> They say you need to pass in "default", but I can't figure it out. I tried...
> select dbo.FirstDayOfYear(default)
> select default dbo.FirstDayOfYear()
> select dbo.FirstDayOfYear() default
> Any pointers?
> Maury|||That's actually looking for the string, 'getdate' -- actually calling the
GETDATE function requires parens (GETDATE()) -- however, SQL Server will not
accept that (I'm not sure why). You'll have to actually call the function
with GETDATE() as the argument to do what you need.
Adam Machanic
SQL Server MVP
http://www.datamanipulation.net
--
"Maury Markowitz" <MauryMarkowitz@.discussions.microsoft.com> wrote in
message news:24DEC598-40A2-491E-A975-D5E9026BD10D@.microsoft.com...
> The documentation talks about default values, but gives no examples. I'm
> trying to get this to work...
> ALTER FUNCTION FirstDayOfYear(@.date datetime = getdate) RETURNS datetime
> BEGIN
> RETURN convert(datetime, '1/1/' + convert(varchar, YEAR(@.date)))
> END
> They say you need to pass in "default", but I can't figure it out. I
tried...
> select dbo.FirstDayOfYear(default)
> select default dbo.FirstDayOfYear()
> select dbo.FirstDayOfYear() default
> Any pointers?
> Maury|||"Adam Machanic" wrote:
> That's actually looking for the string, 'getdate' -- actually calling the
> GETDATE function requires parens (GETDATE()) -- however, SQL Server will not
> accept that (I'm not sure why). You'll have to actually call the function
> with GETDATE() as the argument to do what you need.
Got it. It doesn't really need to have this feature -- a default that is --
but it would make the callee syntax a little nicer.
Maury