Allow zero length strings

In Microsoft Access there is the option to set a field to accept zero length
strings.
This of course means that an empty text box could be entered into the
database without an error occuring.
Is there any way I can do this in SQL Server.
I know that when developing a front end I could write some code to check
each text box and when a textbox is empty, push a " " into it before
insertion but I'm sick of this.
Is there any other way ?We use a Default in SQL Server 2000 like the following:
if exists (select * from dbo.sysobjects where id =object_id(N'[dbo].[EmptyString]') and OBJECTPROPERTY(id,
N'IsDefault') = 1)
drop default [dbo].[EmptyString]
GO
create default [Space] as ''
GO
>--Original Message--
>In Microsoft Access there is the option to set a field to
accept zero length
>strings.
>This of course means that an empty text box could be
entered into the
>database without an error occuring.
>Is there any way I can do this in SQL Server.
>I know that when developing a front end I could write
some code to check
>each text box and when a textbox is empty, push a " "
into it before
>insertion but I'm sick of this.
>Is there any other way ?
>
>.
>|||You can insert an empty (zero-length) string into a varchar (or nvarchar)
column:
CREATE TABLE MyTable
(
MyColumn varchar(10) NOT NULL
)
INSERT INTO MyTable VALUES('')
SELECT
MyColumn,
DATALENGTH(MyColumn)
FROM MyTable
GO
I don't know much about Access programming so there may be programming
considerations. Does an empty text box return in a zero-length string value
or does this return a NULL value?
--
Hope this helps.
Dan Guzman
SQL Server MVP
"Poppy" <paul.diamond@.NOSPAMthemedialounge.com> wrote in message
news:ePYQ%23tctDHA.980@.TK2MSFTNGP10.phx.gbl...
> In Microsoft Access there is the option to set a field to accept zero
length
> strings.
> This of course means that an empty text box could be entered into the
> database without an error occuring.
> Is there any way I can do this in SQL Server.
> I know that when developing a front end I could write some code to check
> each text box and when a textbox is empty, push a " " into it before
> insertion but I'm sick of this.
> Is there any other way ?
>

Allow Any Date in SP Parameter

I have an SP that includes a date as one of the input parameters. In some
instances I would like to accept any date. Is there a way to set a data
parameter to "any"? I tried playing with '%' but no luck there.
WayneRe-read your question Wayne and see if you understand what you mean.
Isn't "any date" still a date?
Do you mean a default value for a parameter?
"Wayne Wengert" <wayneDONTWANTSPAM@.wengert.com> wrote in message
news:u2l$$epEFHA.1408@.TK2MSFTNGP10.phx.gbl...
> I have an SP that includes a date as one of the input parameters. In some
> instances I would like to accept any date. Is there a way to set a data
> parameter to "any"? I tried playing with '%' but no luck there.
> Wayne
>|||Hi,
You should put NULL as input parameter and check it inside your statment
Ex. WHERE data1 = COALESCE(@.data, data1)
"Wayne Wengert" wrote:

> I have an SP that includes a date as one of the input parameters. In some
> instances I would like to accept any date. Is there a way to set a data
> parameter to "any"? I tried playing with '%' but no luck there.
> Wayne
>
>|||On Mon, 14 Feb 2005 06:18:47 -0700, Wayne Wengert wrote:

>I have an SP that includes a date as one of the input parameters. In some
>instances I would like to accept any date. Is there a way to set a data
>parameter to "any"? I tried playing with '%' but no luck there.
Hi Wayne,
Maybe this web site has some useful idea for you:
http://www.sommarskog.se/dyn-search.html
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)|||Sorry I wasn't clear...
I want the query inside the SP, which uses that data parameter value as one
of the "WHERE" clauses, to accept all records, no matter what date is in the
record. For example, if the Select statement inside the SP is something
like...
SELECT CustomerID, CustomerName FROM Customers WHERE PurchaseDate =
@.dateparameter
I suspect I'll need to use some dummy date like '1/1/1900' and when the date
parameter has that value, just leave that part of the WHERE clause out.
Wayne
"raydan" <rdanjou@.savantsoftNOSPAM.com> wrote in message
news:%23BF3UqpEFHA.3244@.TK2MSFTNGP15.phx.gbl...
> Re-read your question Wayne and see if you understand what you mean.
> Isn't "any date" still a date?
> Do you mean a default value for a parameter?
> "Wayne Wengert" <wayneDONTWANTSPAM@.wengert.com> wrote in message
> news:u2l$$epEFHA.1408@.TK2MSFTNGP10.phx.gbl...
some
>|||Thanks - that is probably what I'll do
Wayne
"Tomasz Borawski" <TomaszBorawski@.discussions.microsoft.com> wrote in
message news:24CE82C5-2B9A-4B61-BF91-C7E2F21B232D@.microsoft.com...
> Hi,
> You should put NULL as input parameter and check it inside your statment
> Ex. WHERE data1 = COALESCE(@.data, data1)
> "Wayne Wengert" wrote:
>
some|||Very nice resource - thanks
Wayne
"Hugo Kornelis" <hugo@.pe_NO_rFact.in_SPAM_fo> wrote in message
news:9sa111datfaa5r7g45p8ft0blhmg6amune@.
4ax.com...
> On Mon, 14 Feb 2005 06:18:47 -0700, Wayne Wengert wrote:
>
> Hi Wayne,
> Maybe this web site has some useful idea for you:
> http://www.sommarskog.se/dyn-search.html
> Best, Hugo
> --
> (Remove _NO_ and _SPAM_ to get my e-mail address)