Create table - default for column (sql 2000)

When I have a table with two columns, can the second column default to
a value based on the value from the first column on an inserted record?
I read the section below in BOL ALTER TABLE but can't make head nor
toes.
E. Alter a table to add several columns with constraints
...
column_c INT NULL
CONSTRAINT column_c_fk
REFERENCES doc_exe(column_a),
...
Can someone explain what REFERENCES is for?
regards,
Gerard> When I have a table with two columns, can the second column default to
> a value based on the value from the first column on an inserted record?
CREATE TABLE dbo.foo
(
column_a VARCHAR(32),
column_b AS CONVERT(CHAR(8), LEFT(column_a, 8))
);
GO
SET NOCOUNT ON;
INSERT dbo.foo(column_a) SELECT 'barblatmortsplunge';
SELECT column_a, column_b FROM dbo.foo;
DROP TABLE dbo.foo;
However, my suggestion is usually to have this kind of thing in a view,
since you can always calculate it at SELECT time, without having to store it
and without tempting users to try and update it, have it be included in
column lists produced by code generators, etc. etc. For example, this
accomplishes the same thing:
CREATE TABLE dbo.foo
(
column_a VARCHAR(32)
);
GO
CREATE VIEW dbo.foo_view
AS
SELECT
column_a,
column_b = LEFT(column_a, 8)
FROM
dbo.foo
GO
SET NOCOUNT ON;
INSERT dbo.foo(column_a) SELECT 'barblatmortsplunge';
SELECT column_a, column_b FROM dbo.foo_view;
DROP VIEW dbo.foo_view;
DROP TABLE dbo.foo;

> Can someone explain what REFERENCES is for?
A foreign key constraint is completely different from what you are asking
about (computed columns). REFERENCES is indicating a separate table (think
master/detail, child/parent, and just about any type of entity
relationship). If you have an Orders table, a Customers table, a Products
table and an OrderDetails table, it is usually set up something like this
(Celko, you know where you can cram your IDENTITY comments):
CREATE TABLE dbo.Products
(
ProductID INT IDENTITY(1,1) NOT NULL PRIMARY KEY,
/*...other columns...*/
);
GO
CREATE TABLE dbo.Customers
(
CustomerID BIGINT IDENTITY(1,1) NOT NULL PRIMARY KEY,
/*...other columns...*/
);
GO
CREATE TABLE dbo.Orders
(
OrderID BIGINT IDENTITY(1,1) NOT NULL PRIMARY KEY,
CustomerID BIGINT NOT NULL FOREIGN KEY REFERENCES
dbo.Customers(CustomerID),
/*...other columns...*/
);
GO
CREATE TABLE dbo.OrderDetails
(
OrderID BIGINT FOREIGN KEY REFERENCES dbo.Orders(OrderID),
ProductID INT FOREIGN KEY REFERENCES dbo.Products(ProductID),
Quantity INT,
/*...other columns...*/
PRIMARY KEY(OrderID, ProductID)
);
GO|||"References" token as shown here is a method to explain that the new
column contents must conform to the contents of another table/column
before an INSERT or UPDATE is allowed.
No related to what you are asking to get accomplished. Sounds more like
you might be asking for a trigger which should only be used as a last
ditch effort when making the changes at the (each) of the client
interface is not possible.
Example of simple trigger:
if exists (select * from dbo.sysobjects where id =
object_id(N'[dbo].[tgr_sample_insert_update]') and OBJECTPROPERTY(id,
N'IsTrigger') = 1)
drop trigger [dbo].[tgr_sample_insert_update]
GO
CREATE TRIGGER dbo.tgr_sample_insert_update ON dbo.tmp_sample
FOR INSERT,UPDATE
AS
SET NOCOUNT ON
UPDATE inserted SET colb = cola * tax_percentage
GO
Cheers
http://rickhathaway.blogspot.com/|||Thanks to you both for your replies. I will experiment a little to see
which is best for me.
regards,
Gerard|||The computed column was not an option as it can not be updated, quite
logical really.
A trigger was too much overhead for what I was trying to achieve so I I
have resolved my issue by including the logic to set the value of the
column on the "client side"
The reason I was wondering about REFERENCES was that I hoped that
something like this would be possible:
create table aTest (
col_a int default 0,
col_b as case when col_a = 1 then 1 when col_a = 2 then 2 else 3 end
)
insert into aTest (col_a) values (0)
select * from aTest
update aTest set col_b = 9
drop table aTest
--
But as I noted above, the update cannot be done.
Thanks again for your replies.
regards,
Gerard

CREATE script that filters out empty columns

All:
Say I need to duplicate a table, but the CREATE script must only
include those columns of the table where the value in ALL the available
rows is not null. For an ad-hoc exercise (one or two tables), this is
easy, but for duplicating, say, 90 tables with the empty columns
filtered out, I assume I need an SP that uses each table's metadata to
test each column individually, for each table. A temp table could then
keep the name of those non-empty columns, and the script would
recreate the new table's script from the resulting set.
If someone can suggest a script to do this, I'll be more than happy...BTW, I do know that information_schema.columns is involved... I know
what the logic should be, I simply don't know how to translate that
logic into T-SQL well enough to be efficient... and maybe VB.NET should
be involved, instead should be something like:
(code to write the beginning of the CREATE TABLE statement, plus the
first bracket)
For all tables in the database
For each column in current_table
SELECT DISTINCT (current_column) , COUNT(*) FROM current_table
GROUP BY (current_column)
If (COUNT(*) >= 1 AND (individual value in the column) <> NULL
then /* This implies that the only value there is not NULL */
(write the name of current_column to a file, plus its data
type and width, and a
comma if not the last column)
end if
next column
next table
(write the closing bracket)
Any suggestions?

Create query based on a field that wont be the same value in both tables

I have two tables: TestA and TestB. Both tables have 3 fields: ID,
Name, and RunDate. I need to create a query which will join the two
tables first on Name but then I need to match up the RunDates even
though the RunDates won't be the same.

CREATE TABLE TestA (ID INT IDENTITY, Name VARCHAR(255), RunDate
DATETIME)
CREATE TABLE TestB (ID INT IDENTITY, Name VARCHAR(255), RunDate
DATETIME)

INSERT INTO TestA VALUES ('Account 1', '9/1/2004 12:00PM')
INSERT INTO TestB VALUES ('Account 1', '9/1/2004 12:15PM')
INSERT INTO TestA VALUES ('Account 1', '9/2/2004 1:00PM')
INSERT INTO TestB VALUES ('Account 1', '9/2/2004 1:15PM')
INSERT INTO TestA VALUES ('Account 1', '9/3/2004 3:00PM')
INSERT INTO TestA VALUES ('Account 2', '9/5/2004 4:00PM')
INSERT INTO TestB VALUES ('Account 2', '9/5/2004 4:15PM')

Here's a common scenario:
User updates TestA data for Account 1 on 9/1/2004 at 12:00pm. Then
the user updates TestB data for Account 1, 15 minutes later. I want
these two records to match. The user must always update TestA data
before they update TestB data. Therefore, there might be more rows in
TestA then in TestB

Here's what the results should look like for the above data.

Name TestA Date TestB Date
-- ---- ----
Account 1 9/1/2004 12:00pm 9/1/2004 12:15PM
Account 1 9/2/2004 1:00pm 9/2/2004 1:15PM
Account 1 9/3/2004 3:00pm (NULL)
Account 2 9/5/2004 4:00pm 9/5/2004 4:15PM

Any help would be much appreciated!!!!On 29 Sep 2004 07:41:18 -0700, Jim G wrote:

>Here's what the results should look like for the above data.
>Name TestA Date TestB Date
>-- ---- ----
>Account 1 9/1/2004 12:00pm 9/1/2004 12:15PM
>Account 1 9/2/2004 1:00pm 9/2/2004 1:15PM
>Account 1 9/3/2004 3:00pm (NULL)
>Account 2 9/5/2004 4:00pm 9/5/2004 4:15PM

Hi Jim,

Thanks for posting DDL ans INSERTS for sample data!

The following query gives the above results:

SELECT a.Name, a.RunDate, b.RunDate
FROM TestA AS a
LEFT JOIN TestB AS b
ON b.Name = a.Name
AND b.RunDate >= a.RunDate
AND NOT EXISTS (SELECT *
FROM TestA AS a2
WHERE a2.Name = a.Name
AND a2.RunDate > a.RunDate
AND a2.RunDate < b.RunDate)

Best, Hugo
--

(Remove _NO_ and _SPAM_ to get my e-mail address)|||Awesome! That worked perfectly. Thanks!

Create Primary Key with increment and format?

I have an access table that has a primary key (entitled "ID Number"), no duplicates, the field is an integer.
And, importantly, the value is set to "increment".
The format is "phd"000 - so it starts outphd001,phd002, and so on...
How to do this in an SQL table? Can that format be done? Or is it better not to do it via SQL but in coding instead?In SQL Server you do Unique constraint or Unique index for no duplicates the former allow nulls the later not null being primary key and set IDENTITY property on the column for auto increament. Run a search for Unique constraint and Unique index and the IDENTITY property in SQL Server BOL(books online). Hope this helps.

Create Login form(authenticate with sql)

Hi all.

M trying to create a logon form
I had something in mind but i can work it out yet

When the user wants to login into the application the value of textbox must be compared with the datafield in the sql server.
And then i want a messagebox to show up.

I created a table in sql server with 2 fields in it , User and Password.

I hope anyone could help me tnx already

You will need a login which is able to access the table or a stored procedure which does the check of the login for you. Then its up to you to either raise an error from your stored procedure or return a specific result fromthe stored procedure which is transformed into a user friendly message like "Password wrong" or "Username / password combination wrong".

HTH, Jens K. Suessmeyer.

http://www.sqlserver2005.de

Create Global Variable like VB... can it be done?

I just want to store the value of a parameter in a global variable that all my reports in the same project can use.

My goal is to create a dynamic query. For example:

Company Name: Widgets inc.

Divisions: Sales, Service, Tech, Accounting

I have a matrix and when I click on the more information button it goes to another report. I want the next report to know what division is currently selected in the dropdown parameter. So, being a VB programmer, I thought I could store parameter1.division.value into a global variable and update the variable whenever the parameter changes.

This way, on the next report, my query's where statement is the global variable.

@.GlobalVariable = parameter1.division.value

Select name, address, phone FROM Employee WHERE division = @.GlobalVariable

I am using Visual Studio to design this project although I would prefer to use VB or ASP. But this is my only stumbling block right now. Everything else is complete.

Please let me know if anyone can help.

Thanks.

John

There is not a concept of a global variable for multiple reports that I know of.

You could potentially do something with a table that could store the global value by user id & identifier, and retrieve the variable from the table. Or you could setup a web service to get/set the variable.

You may be able to add a reference to a DLL in every report, and share between them, though I woud think it would be destroyed after the report session times out.

http://www.codeproject.com/dll/data_seg_share.asp

cheers,

Andrew

|||

Looks like you need a report parameter. You don't need to show it to the user, but just default it.

create function

I'd like to return a NULL value from a mssql function, but i can't get it
to. here's my sample:
CREATE FUNCTION dbo.FixVarChar (@.val nvarchar(30) )
RETURNS nvarchar(30) AS
BEGIN
declare @.retval as varchar (30)
select @.retval = case when len(@.val)>0 then @.val else null end
return (null)
END
it does NOT return a null value. when I run:
select dbo.fixvarchar('somevalue') as test
it does not return null but what looks like a zero-length string.
i actually need to get the function to return the value (if the string has
len>0) or an actuall NULL value.
help!john wrote on Fri, 9 Jun 2006 10:09:54 -0400:

> I'd like to return a NULL value from a mssql function, but i can't get it
> to. here's my sample:
> CREATE FUNCTION dbo.FixVarChar (@.val nvarchar(30) )
> RETURNS nvarchar(30) AS
> BEGIN
> declare @.retval as varchar (30)
> select @.retval = case when len(@.val)>0 then @.val else null end
> return (null)
> END
> it does NOT return a null value. when I run:
> select dbo.fixvarchar('somevalue') as test
> it does not return null but what looks like a zero-length string.
> i actually need to get the function to return the value (if the string has
>
len>> 0) or an actuall NULL value.
> help!
I've just created your function on my SQL Server 2005 machine, and run the
same select, and get a NULL (when run in Query Analyzer) - and you'll always
get NULL too, unless you fix your last line to be RETURN (@.retval). What
version of SQL Server are you trying this on?
Dan|||Just tried it on SQL Server 2000 too, works fine.
Dan|||it's sql 2000.
I put that last line in there just to ensure i was returning a null value.
(ultimately, the function will return the non-null value if it exists or
null. I have an xml export program that expects null for non-existing
element nodes to be created).
"Daniel Crichton" <msnews@.worldofspack.com> wrote in message
news:uKXChC9iGHA.4344@.TK2MSFTNGP05.phx.gbl...
> Just tried it on SQL Server 2000 too, works fine.
> Dan
>|||I get 'someval' returned on my SQL 2000 SP4 when I replace 'return (null)'
with 'return (@.retval)'. Are you running SP4?
In any case, I see a couple of inconsistencies. You are returning varchar
(30) but the function return data type is nvarchar(30). Also, you are
passing a varchar instead of the nvarchar expected as the function
parameter.
Hope this helps.
Dan Guzman
SQL Server MVP
"john doe" <jdoe@.doe.com> wrote in message
news:%23IjaZ78iGHA.3440@.TK2MSFTNGP02.phx.gbl...
> I'd like to return a NULL value from a mssql function, but i can't get it
> to. here's my sample:
> CREATE FUNCTION dbo.FixVarChar (@.val nvarchar(30) )
> RETURNS nvarchar(30) AS
> BEGIN
> declare @.retval as varchar (30)
> select @.retval = case when len(@.val)>0 then @.val else null end
> return (null)
> END
> it does NOT return a null value. when I run:
> select dbo.fixvarchar('somevalue') as test
> it does not return null but what looks like a zero-length string.
> i actually need to get the function to return the value (if the string has
> len>0) or an actuall NULL value.
> help!
>|||As I said, I tried it on SQL 2000 here, worked fine (returned a null as it
was, returned the value I passed in when I adjusted it to return @.retval,
and a null if the passed in value was a blank string).
Dan
john wrote on Fri, 9 Jun 2006 12:55:24 -0400:
> it's sql 2000.
> I put that last line in there just to ensure i was returning a null value.
> (ultimately, the function will return the non-null value if it exists or
> null. I have an xml export program that expects null for non-existing
> element nodes to be created).
> "Daniel Crichton" <msnews@.worldofspack.com> wrote in message news:uKXChC9i
GHA.4344@.TK2MSFTNGP05.phx.gbl...|||Forgot the ask something else in my reply - how are you testing the return
value? I used query analyser where it shows NULL in the column, but if
you're using something else maybe that is interpreting nulls as empty
strings. Without more information there's not much else I can suggest.
Dan
john wrote on Fri, 9 Jun 2006 12:55:24 -0400:
> it's sql 2000.
> I put that last line in there just to ensure i was returning a null value.
> (ultimately, the function will return the non-null value if it exists or
> null. I have an xml export program that expects null for non-existing
> element nodes to be created).
> "Daniel Crichton" <msnews@.worldofspack.com> wrote in message news:uKXChC9i
GHA.4344@.TK2MSFTNGP05.phx.gbl...

Create Extended Procedures in VS 2005

Hi

I am trying to write an extended procedure that accepts a string parameter and returns an integer value. The extended procedure calls a regular stored procedure of a database passing the string parameter as an input. The int value is an OUT parameter to this procedure.

Can I some one suggest where do I get started with respect to this in VS 2005.Why do you want to write an extended stored procedure to call a TSQL SP? This is overkill actually. Extended SPs are meant for computation intensive operations or other logic that cannot be performed efficiently using TSQL. It has it's limitations, performance, reliability and security issues. Or you trying to just learn extended SP programming? If later you can look at the SQL Server samples. You can also look at ODBC/OLEDB samples that will show you how to call SPs.|||The thing is that we need to make DML changes while calling a function. Since normal UDFs dont allow to do it I am trying to call an xp. Since we also need to look at concurrency I am having a stored procedure with transactions taken care. Hence the need of calling T_SQL sp from xp.|||Where do I look for the Extended Stored Procedure DLL Wizard while I open VS 2005 --> Open Project.

I do not see any such wizrd name.

Regards
Imtiaz|||

Use of side-effecting code from UDF is not recommended. It takes lot of work to get it right (dealing with bound connections, concurrency issues, deadlocks, scalability of xps, virtual memory issues depending on how the xp is written etc). Lastly, use of such UDFs in SELECT statement can cause unexpected behavior.

Create dimension without member

Hi
Analysis manager wont let me create dimension unless fact table filed has value init. Other words, how do I create MT dimension(dimension without member)
thanksI'm confused. Is this really a dimension? Or is this a dimension that doesn't exist now, but will in the future? (i.e. you're rolling out a new type of product and you've created new product dimension, but the fact table has no corresponding records). Can you plug an 'unknown' attribute into your fact and dimension?

Let me know if I'm way off.|||Fact table has field called Due date which contains null values. When I create Due_date dimension, it won’t let me create, gives a message unable to count the members. I have to create dimensions out of all the fields in the fact table and send the cub to this person. Later this person will load the cub into his server and change data source and run the cub.
But my question is , If in his source table due_Date has null values(no data) , when this person runs the cube , it will give the error right?

Right now his source table due_date doen’t have values, but later he will had value into it.

He wants me to create each field in the fact table one dimension, doesn’t matter the fact table filed contains data or not? Can I do that?|||Is this 2000 or 2005? I just tried this in 2000 and it worked. No errors.|||It's 2000, you mean , it let you create a dimension with out a member.