If my data looks like this how do I one-off update it?

First_Name Last_Name Contact_Name
---- --- ----
NULL NULL YVES MERCIER
NULL NULL YUN CHUNG
NULL NULL YUKITO YAMASAKI
NULL NULL YUICHI MOCHIZUKI
NULL NULL YUGUNDERA BHIDE

My goal is to split the full contact name into first and last as well. Because that is what is in the database and I want to keep it consistent for the few thousand records missing it.

Do I update this using a stored Procedure?

How would it be formed?

CREATE PROCEDURE dbo.ap_Insert_FirstLastName_WhereNull

SELECT *
FROM Booth_Visitors_Data
WHERE First_Name IS NULL And Last_Name Is NULL And Contact_Name IS NOT NULL
ORDER BY Contact_Name Desc

Update Booth_Visitors_Data Set

??

Or is this impossible with a stored procedure?

SHould I wirte a .aspx page to do it?

Or can I do it all in code-behind??

ProtectedSub btnUpdate_Click(ByVal senderAsObject,ByVal eAs System.EventArgs)Handles btnUpdate.ClickDim conAsNew SqlClient.SqlConnection

con.ConnectionString ="Data Source=10.10.10.10;Initial Catalog=Leads;Persist Security Info=True;User ID=xxxxx;Password=xxxxx"

Dim myCommandAsNew SqlClient.SqlCommand

myCommand.CommandText ="SELECT * FROM(Booth_Visitors_Data) WHERE(First_Name Is NULL And Last_Name Is NULL And Contact_Name Is Not NULL) ORDER BY Contact_Name Desc"

myCommand.BeginExecuteReader()

myCommand.Connection = con

con.Open()

con.Close()

EndSub

If I can do it in code-behind, how does it look in modern .net code as opposed to the old classic asp way?

[Edited by Dinakar Nethi]

Masked userid/pwd in connection string.

;

Does the data in your contact_Name column have only 2 words - for first name and last name or could there be any middle names, Jr, Sr or I, II, III etc too?

|||

Here's a sample:

Declare@.Namevarchar(100)

Set@.Name='YUKITO YAMASAKI'

Select FirstName=substring(@.Name ,1,charindex(' ',@.Name) )

,LastName=substring(@.Name,charindex(' ',@.Name),len(@.Name)-charindex(' ',@.Name)+1)

|||

Well there *Could be* 3, so I was thinking we could go to the far right and go back to the first space and call that the last name.

YOO JI YE
For example...

SO the first name would be Yoo JI and the last name Ye

But I guess that would not work with all the sr. jr. possiblities.

So can this all be done in stored procedures?

Can Stored Procedures loop through records?

|||

>>So can this all be done in stored procedures?

Yes.

>> Can Stored Procedures loop through records?

Ofcourse.

The bigger issue here is to identify the pattern so you can split the full name into its constituent names. The sample I provided earlier works only if its 2 words. Anything more will be tricky.

Check if this post helps:http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=44123

|||

It was fun coming up with this. This will work for 3 words but not for 2 words.

Declare @.Name varchar(100)

Set @.Name = 'YOO JI YE'

Select FirstName = substring(@.Name ,1, charIndex(' ', @.Name, charIndex(' ', @.Name)) - 1 + charIndex(' ', @.Name)),
LastName = SubString(@.Name, charIndex(' ', @.Name) - 1 + charIndex(' ', @.Name),len(@.name) - charIndex(' ', @.Name) - 1 + charIndex(' ', @.Name))

My suggestion would be to follow the link ndinakar provided for you.

|||

I worked with the link ndinakar provided for you abit and came up with a script for you that will work with both 2 part and 3 part full names.

If you pass in 'YOOJI YE' then you get back as a first name of 'YOOJI' and lastname of 'YE'. If you pass in 'YOO JI YE' then you get back a first name of 'YOO JI' with a last name of 'YE'

Declare @.Name varchar(100)Set @.Name ='YOOJI YE'selectcase When parsename(replace(@.name,' ','.'), 3) IS NULL Then parsename(replace(@.name,' ','.'), 2) Else parsename(replace(@.name,' ','.'), 3) +' ' + parsename(replace(@.name,' ','.'), 2)End As FirstName,
parsename(replace(@.name,' ','.'), 1) As LastName

sql

If Isnull Question

I have a SP I am trying to set a value for a variable in. The default value
for the variable is Null.
If there is not parameter passed I want to do an If Isnull statement to set
the variable in the SP, except I keep recveiving an incorrect Syntax error.
If any one can suggest a fix I would greatly appreciate it.
Here is my statement
If IsNull(Convert(VarChar(50), @.Client), Set @.Client in ('HUD601Solomon',
'Citi Special Servicing', 'HUD601SalomonDeal3'))
Thanks
-ChrisYou should look up the IF statement in Books Online, also look at how
variables should be checked for null values.

> If IsNull(Convert(VarChar(50), @.Client), Set @.Client in ('HUD601Solomon',
> 'Citi Special Servicing', 'HUD601SalomonDeal3'))
Is this a part of a query? Then you should also look up the CASE expression
in Books Online.
After you're done with BOL, check out this recent thread:
http://msdn.microsoft.com/newsgroup...5f-b8940be08178
I'm guessing you want something like that.
ML
http://milambda.blogspot.com/|||you want "if @.Client is null. "
isnull is a function which will replace a null occurence with a value
"@.Client is null" is using the is keyword, which will do an equality
check(ish - null != null).
beware not to use if @.Client = null as this will not work.sql

If Is Null in Select Statement

Greetings,
I am getting the following error
"Server: Msg 156, Level 15, State 1, Line 2
Incorrect syntax near the keyword 'IF'."
My SQL statement is:
SELECT dbo.tbl1.col1, dbo.tbl1.col2, dbo.tbl1.col3, dbo.tbl1.col4,
IF IS NULL(dbo.qry1.col5) THEN BEGIN dbo.qry2.col3 END ELSE BEGIN
dbo.qry1.col5 END
FROM dbo.tbl1 LEFT OUTER JOIN
dbo.qry2 ON dbo.tbl1.col4 = dbo.qr1.col3
LEFT OUTER JOIN
dbo.qry1 ON dbo.tbl1.col4 = dbo.qry1.col5
Note: qry1 and qry2 are the same query but am join different columns to the
same column in the table
Thanks for the help
KeithSELECT ..., COALESCE(qry1.col5, qry2.col3)
This is my signature. It is a general reminder.
Please post DDL, sample data and desired results.
See http://www.aspfaq.com/5006 for info.
"Keith" <Keith@.discussions.microsoft.com> wrote in message
news:6CBEB225-5880-4A26-A132-AD7F960FAD58@.microsoft.com...
> Greetings,
> I am getting the following error
> "Server: Msg 156, Level 15, State 1, Line 2
> Incorrect syntax near the keyword 'IF'."
> My SQL statement is:
> SELECT dbo.tbl1.col1, dbo.tbl1.col2, dbo.tbl1.col3, dbo.tbl1.col4,
> IF IS NULL(dbo.qry1.col5) THEN BEGIN dbo.qry2.col3 END ELSE BEGIN
> dbo.qry1.col5 END
> FROM dbo.tbl1 LEFT OUTER JOIN
> dbo.qry2 ON dbo.tbl1.col4 = dbo.qr1.col3
> LEFT OUTER JOIN
> dbo.qry1 ON dbo.tbl1.col4 = dbo.qry1.col5
> Note: qry1 and qry2 are the same query but am join different columns to
> the
> same column in the table
> Thanks for the help
> Keith|||IF IS NULL doesnt exist in SQL Server. Use ISNULL(Columtocheck, ElseValue),
OR COALESCE(Columntocheck[,ColumnTocheck], ElseValue). If you wanna put an
IF / CAse Expression in your query refer to the BOL and to the syntax of
CASE, example:
CASE Somecolumn
WHEN NULL THEN 'SomeValue'[Or a cloumn]
WHEN ...
...
ELSE 'Something'
END
HTH, Jens Suessmeyer.
http://www.sqlserver2005.de
--
"Keith" <Keith@.discussions.microsoft.com> schrieb im Newsbeitrag
news:6CBEB225-5880-4A26-A132-AD7F960FAD58@.microsoft.com...
> Greetings,
> I am getting the following error
> "Server: Msg 156, Level 15, State 1, Line 2
> Incorrect syntax near the keyword 'IF'."
> My SQL statement is:
> SELECT dbo.tbl1.col1, dbo.tbl1.col2, dbo.tbl1.col3, dbo.tbl1.col4,
> IF IS NULL(dbo.qry1.col5) THEN BEGIN dbo.qry2.col3 END ELSE BEGIN
> dbo.qry1.col5 END
> FROM dbo.tbl1 LEFT OUTER JOIN
> dbo.qry2 ON dbo.tbl1.col4 = dbo.qr1.col3
> LEFT OUTER JOIN
> dbo.qry1 ON dbo.tbl1.col4 = dbo.qry1.col5
> Note: qry1 and qry2 are the same query but am join different columns to
> the
> same column in the table
> Thanks for the help
> Keith|||If is a Transact SQL Control flow statement, and cannot be used inside of a
SQL Statement. What you want is the SQL Case Expression. (look it up in
Books OnLIne)
as Folows:
SELECT dbo.tbl1.col1, dbo.tbl1.col2, dbo.tbl1.col3, dbo.tbl1.col4,
Case When dbo.qry1.col5 Is Null
Then dbo.qry2.col3
Else dbo.qry1.col5 End
FROM dbo.tbl1
LEFT OUTER JOIN dbo.qry2
ON dbo.tbl1.col4 = dbo.qr1.col3
LEFT OUTER JOIN dbo.qry1
ON dbo.tbl1.col4 = dbo.qry1.col5
"Keith" wrote:

> Greetings,
> I am getting the following error
> "Server: Msg 156, Level 15, State 1, Line 2
> Incorrect syntax near the keyword 'IF'."
> My SQL statement is:
> SELECT dbo.tbl1.col1, dbo.tbl1.col2, dbo.tbl1.col3, dbo.tbl1.col4,
> IF IS NULL(dbo.qry1.col5) THEN BEGIN dbo.qry2.col3 END ELSE BEGIN
> dbo.qry1.col5 END
> FROM dbo.tbl1 LEFT OUTER JOIN
> dbo.qry2 ON dbo.tbl1.col4 = dbo.qr1.col3
> LEFT OUTER JOIN
> dbo.qry1 ON dbo.tbl1.col4 = dbo.qry1.col5
> Note: qry1 and qry2 are the same query but am join different columns to th
e
> same column in the table
> Thanks for the help
> Keith|||Hi Keith,
The query can be re-written as
SELECT dbo.tbl1.col1, dbo.tbl1.col2, dbo.tbl1.col3, dbo.tbl1.col4,
ISNULL(dbo.qry1.col5, dbo.qry2.col3)
FROM dbo.tbl1 LEFT OUTER JOIN
dbo.qry2 ON dbo.tbl1.col4 = dbo.qr1.col3
LEFT OUTER JOIN
dbo.qry1 ON dbo.tbl1.col4 = dbo.qry1.col5
best Regards,
Chandra
---
"Keith" wrote:

> Greetings,
> I am getting the following error
> "Server: Msg 156, Level 15, State 1, Line 2
> Incorrect syntax near the keyword 'IF'."
> My SQL statement is:
> SELECT dbo.tbl1.col1, dbo.tbl1.col2, dbo.tbl1.col3, dbo.tbl1.col4,
> IF IS NULL(dbo.qry1.col5) THEN BEGIN dbo.qry2.col3 END ELSE BEGIN
> dbo.qry1.col5 END
> FROM dbo.tbl1 LEFT OUTER JOIN
> dbo.qry2 ON dbo.tbl1.col4 = dbo.qr1.col3
> LEFT OUTER JOIN
> dbo.qry1 ON dbo.tbl1.col4 = dbo.qry1.col5
> Note: qry1 and qry2 are the same query but am join different columns to th
e
> same column in the table
> Thanks for the help
> Keith

if else statement problems

Hello to all...

I have an if-else statement that looks for a null array. I know the array is null, because I didn't put anything in it, but my Sql will still try to insert:

if (Line1Array ==null) Response.Write("Array 1 is empty");else for (int i = 0; i < Line1Array.Length; i++) SqlDataSource1.InsertParameters.Clear(); SqlDataSource1.InsertParameters.Add("Shift", ShiftDDL.Text); SqlDataSource1.InsertParameters.Add("Line", LinesDDL.Text); SqlDataSource1.InsertParameters.Add("Product", ProductsDDL.Text); SqlDataSource1.InsertParameters.Add("OPProfile", OPSLabel.Text); SqlDataSource1.InsertParameters.Add("OPAsstProfile", AsstLabel.Text); SqlDataSource1.InsertParameters.Add("OPActual", OPActualTextBox.Text); SqlDataSource1.InsertParameters.Add("OPAsstActual", OPAsstActualTextBox.Text); SqlDataSource1.InsertParameters.Add("Notes", NotesTextBox.Text); SqlDataSource1.InsertParameters.Add("Date", Calendar1.SelectedDate.ToString()); SqlDataSource1.InsertParameters.Add("Day", strCalDateDay); SqlDataSource1.InsertParameters.Add("Week", strCalDateWeek); SqlDataSource1.InsertParameters.Add("Month", strCalDateMonth); SqlDataSource1.InsertParameters.Add("Year", strCalDateYear); SqlDataSource1.Insert();

Does anyone see what is wrong with the statement?

Thanks for your help

Just because you didn't put any items into your array doesn't mean it will evaluate to null. If you created a new instance of the array, then the array will not evaluate to null. Instead, the Length will simply be zero.|||

if (Line1Array ==null)

{ Response.Write("Array 1 is empty");

}else

{ for (int i = 0; i < Line1Array.Length; i++)

{ SqlDataSource1.InsertParameters.Clear(); SqlDataSource1.InsertParameters.Add("Shift", ShiftDDL.Text); SqlDataSource1.InsertParameters.Add("Line", LinesDDL.Text); SqlDataSource1.InsertParameters.Add("Product", ProductsDDL.Text); SqlDataSource1.InsertParameters.Add("OPProfile", OPSLabel.Text); SqlDataSource1.InsertParameters.Add("OPAsstProfile", AsstLabel.Text); SqlDataSource1.InsertParameters.Add("OPActual", OPActualTextBox.Text); SqlDataSource1.InsertParameters.Add("OPAsstActual", OPAsstActualTextBox.Text); SqlDataSource1.InsertParameters.Add("Notes", NotesTextBox.Text); SqlDataSource1.InsertParameters.Add("Date", Calendar1.SelectedDate.ToString()); SqlDataSource1.InsertParameters.Add("Day", strCalDateDay); SqlDataSource1.InsertParameters.Add("Week", strCalDateWeek); SqlDataSource1.InsertParameters.Add("Month", strCalDateMonth); SqlDataSource1.InsertParameters.Add("Year", strCalDateYear); SqlDataSource1.Insert();

}

}

|||

Thanks for shedding light on that... I guess thats why you guys get paid the big bucks! I ended up doing this.. and it works nicely!

if (Line1Array.GetValue(1) ==null) Response.Write("Array 1 is empty");else for (int i = 0; i < Line1Array.Length; i++) SqlDataSource1.InsertParameters.Clear(); SqlDataSource1.InsertParameters.Add("Shift", ShiftDDL.Text); SqlDataSource1.InsertParameters.Add("Line", LinesDDL.Text); SqlDataSource1.InsertParameters.Add("Product", ProductsDDL.Text);if (OPSLabel !=null) { SqlDataSource1.InsertParameters.Add("OPProfile", OPSLabel.Text); SqlDataSource1.InsertParameters.Add("OPAsstProfile", AsstLabel.Text); } SqlDataSource1.InsertParameters.Add("OPActual", OPActualTextBox.Text); SqlDataSource1.InsertParameters.Add("OPAsstActual", OPAsstActualTextBox.Text); SqlDataSource1.InsertParameters.Add("Notes", NotesTextBox.Text); SqlDataSource1.InsertParameters.Add("Date", Calendar1.SelectedDate.ToString()); SqlDataSource1.InsertParameters.Add("Day", strCalDateDay); SqlDataSource1.InsertParameters.Add("Week", strCalDateWeek); SqlDataSource1.InsertParameters.Add("Month", strCalDateMonth); SqlDataSource1.InsertParameters.Add("Year", strCalDateYear);if (OPSLabel !=null) { SqlDataSource1.Insert(); }

|||

I still don't believe that is what you want it to do. Let me fix your indentation, so it reads how it is coded:

if (Line1Array.GetValue(1) ==null)Response.Write("Array 1 is empty");elsefor (int i = 0; i < Line1Array.Length; i++)SqlDataSource1.InsertParameters.Clear();SqlDataSource1.InsertParameters.Add("Shift", ShiftDDL.Text);SqlDataSource1.InsertParameters.Add("Line", LinesDDL.Text);SqlDataSource1.InsertParameters.Add("Product", ProductsDDL.Text);if (OPSLabel !=null){SqlDataSource1.InsertParameters.Add("OPProfile", OPSLabel.Text);SqlDataSource1.InsertParameters.Add("OPAsstProfile", AsstLabel.Text);}SqlDataSource1.InsertParameters.Add("OPActual", OPActualTextBox.Text);SqlDataSource1.InsertParameters.Add("OPAsstActual", OPAsstActualTextBox.Text);SqlDataSource1.InsertParameters.Add("Notes", NotesTextBox.Text);SqlDataSource1.InsertParameters.Add("Date", Calendar1.SelectedDate.ToString());SqlDataSource1.InsertParameters.Add("Day", strCalDateDay);SqlDataSource1.InsertParameters.Add("Week", strCalDateWeek);SqlDataSource1.InsertParameters.Add("Month", strCalDateMonth);SqlDataSource1.InsertParameters.Add("Year", strCalDateYear);if (OPSLabel !=null){SqlDataSource1.Insert();}

Because your for loop and else clause is not enclosed in braces {}, only the first statement is being done.

In this case if Line1Array is not null, you are clearing the parameters from the SqlDataSource1 once for each element.

Then you add a bunch of parameters (always, and always only once).

Then you do an insert if OPSLabel isn't null.

This begs the questions.. Why are you repeatedly clearing the SqlDataSource parameters in a loop? That's probably not what you meant.

Why are you going through the whole process of clearing, and setting a bunch of values, only to not do anything with them at the end?

(Why check if OPSLabel is null at the end instead of at the beginning?)

|||

You are right.. I have fixed that now. Another problem with the if's and else's -- I need to verify if another array block is empty, but just before the insert, not just skipping by the whole line:

if (Line1Array.GetValue(3) ==null) { Response.Write("<script language='javascript'>alert('null');</script>"); }else {if (Line1Array.GetValue(6) ==null) { Label1.Text = Line1Array.GetValue(3).ToString(); Response.Write("<script language='javascript'>alert('You are missing an entry for Actual Operators on ROW 1');</script>"); }else { SqlDataSource1.InsertParameters.Add("Shift", ShiftDDL.Text); SqlDataSource1.InsertParameters.Add("Line", LinesDDL.Text); SqlDataSource1.InsertParameters.Add("Product", ProductsDDL.Text); SqlDataSource1.InsertParameters.Add("OPProfile", OPSLabel.Text); SqlDataSource1.InsertParameters.Add("OPAsstProfile", AsstLabel.Text); SqlDataSource1.InsertParameters.Add("OPActual", OPActualTextBox.Text); SqlDataSource1.InsertParameters.Add("OPAsstActual", OPAsstActualTextBox.Text); SqlDataSource1.InsertParameters.Add("Notes", NotesTextBox.Text); SqlDataSource1.InsertParameters.Add("Date", Calendar1.SelectedDate.ToString()); SqlDataSource1.InsertParameters.Add("Day", strCalDateDay); SqlDataSource1.InsertParameters.Add("Week", strCalDateWeek); SqlDataSource1.InsertParameters.Add("Month", strCalDateMonth); SqlDataSource1.InsertParameters.Add("Year", strCalDateYear);//SqlDataSource1.Insert(); SqlDataSource1.InsertParameters.Clear(); } }

but this doesn't work! Value(6) is null, but is passes on and tries to insert. I know the javascript works, verified outside of the statement.

Any ideas?

Thanks

|||

I'm afraid I can't answer that one for you. I'm not well versed enough in C# to give you the answer you seek. However, in your response.write, the first less than is being sent out as <, and I'm pretty sure that isn't valid.

sql

IF ELSE alternative for stored procedure

Hi,

I'm trying to create a stored procedure that checks to see whether the parameters are NULL. If they are NOT NULL, then the parameter should be used in the WHERE clause of the SELECT statement otherwise all records should be returned.

sample code:

SET ANSI_NULLSONGOSET QUOTED_IDENTIFIERONGOCREATE PROCEDURE [dbo].[GetProjectInfo](@.ProjectTitlevarchar(300), @.ProjectManagerIDint, @.DeptCodevarchar(20), @.ProjIDvarchar(50), @.DateRequesteddatetime, @.DueDatedatetime, @.ProjectStatusIDint)ASBEGINSET NOCOUNT ONIF @.ProjectTitleISNOT NULL AND @.ProjectManagerIDISNULL AND @.DeptCodeISNULL AND @.ProjIDISNULL AND @.DateRequestedISNULL AND @.DueDateISNULL AND @.ProjectStatusIDISNULLSELECT ProjID, ProjectTitle, ProjectDetails, ProjectManagerID, RequestedBy, DateRequested, DueDate, ProjectStatusIDFROM dbo.tbl_ProjectWHERE ProjectTitle = @.ProjectTitle;ELSE IF @.ProjectTitleISNOT NULL AND @.ProjectManagerIDISNOT NULL AND @.DeptCodeISNULL AND @.ProjIDISNULL AND @.DateRequestedISNULL AND @.DueDateISNULL AND @.ProjectStatusIDISNULLSELECT ProjID, ProjectTitle, ProjectDetails, ProjectManagerID, RequestedBy, DateRequested, DueDate, ProjectStatusIDFROM dbo.tbl_ProjectWHERE ProjectTitle = @.ProjectTitleAND ProjectManagerID = @.ProjectManagerID;ELSESELECT ProjID, ProjectTitle, ProjectDetails, ProjectManagerID, RequestedBy, DateRequested, DueDate, ProjectStatusIDFROM dbo.tbl_Project;

I could do this using IF-ELSE but that would require a ridiculous amount of conditional statements (basically 1 for each combination of NULLs and NOT NULLs). Is there a way to do this without all the IF-ELSEs?

Thanks.

it will be easier to dynamically build your sql string, while you'll still have to use IF-ELSE you won't have to check for each and every condition combination, just check for each parameter. for example...

CREATE PROCEDURE [dbo].[GetProjectInfo]
(@.ProjectTitlevarchar(300), @.ProjectManagerIDint, @.DeptCodevarchar(20), @.ProjIDvarchar(50),
@.DateRequesteddatetime, @.DueDatedatetime, @.ProjectStatusIDint)
AS
BEGIN
SET NOCOUNT ON

DECLARE @.strSQL varchar(4000)

SET @.strSQL = "SELECT ProjID, ProjectTitle, ProjectDetails, ProjectManagerID, RequestedBy, DateRequested, DueDate, ProjectStatusID "
SET @.strSQL = @.strSQL + "FROM dbo.tbl_Project "

SET @.strSQL = @.strSQL + "WHERE "

IF @.ProjectTitle IS NOT NULL

BEGIN

SET @.strSQL = @.strSQL + "ProjectTitle = " + @.ProjectTitle

END

IF @.ProjectManagerId IS NOT NULL

BEGIN

SET @.strSQL = @.strSQL + " AND ProjectMangerId = " + @.ProjectMangerId

END

<conditions for each param>

EXEC(@.strSQL)

GO

|||

There is a better way than using lots of IF and ELSE statements. Rewrite your stored proecedure so that it doesn't matter what parameters are passed it will always work. eg.

CREATEPROCEDURE [dbo].[GetProjectInfo]

(

@.ProjectTitlevarchar(300)=NULL, @.ProjectManagerIDint=NULL, @.DeptCodevarchar(20)=NULL,

@.ProjID

varchar(50)=NULL, @.DateRequesteddatetime=NULL, @.DueDatedatetime=NULL, @.ProjectStatusIDint=NULL)

AS

BEGIN

SET

NOCOUNTONSELECT ProjID, ProjectTitle, ProjectDetails, ProjectManagerID, RequestedBy, DateRequested, DueDate, ProjectStatusIDFROM dbo.tbl_ProjectWHERE(ProjectTitle= @.ProjectTitleOR @.ProjectTitleISNULL)AND(ProjectManagerID= @.ProjectManagerIDOR @.ProjectManagerIDISNULL)AND(DeptCode= @.DeptCodeOR @.DeptCodeISNULL)AND(ProjID= @.ProjIDOR @.ProjIDISNULL)AND(.... [rest of parameters])

END

|||SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[GetProjectInfo]
(@.ProjectTitle varchar(300), @.ProjectManagerID int, @.DeptCode varchar(20), @.ProjID varchar(50),
@.DateRequested datetime, @.DueDate datetime, @.ProjectStatusID int)
AS
SET NOCOUNT ON
IF DATALENGTH(RTRIM(@.ProjectTitle)) = 0 SET @.ProjectTitle = NULL
IF DATALENGTH(RTRIM(@.ProjectManagerID)) = 0 SET @.ProjectManagerID = NULL
IF DATALENGTH(RTRIM(@.ProjectStatusID)) = 0 SET @.ProjectStatusID = NULL
IF DATALENGTH(RTRIM(@.DeptCode)) = 0 SET @.DeptCode = NULL
IF @.DueDate >= CONVERT(DATETIME, '9999-12-31 00:00:00') SET @.DueDate = NULL
IF @.DateRequested >= CONVERT(DATETIME, '9999-12-31 00:00:00') SET @.DateRequested = NULL
SELECT ProjID, ProjectTitle, ProjectDetails, ProjectManagerID, RequestedBy, DateRequested, DueDate, ProjectStatusID
FROM dbo.tbl_Project
WHERE ProjectTitle = COALESCE(@.ProjectTitle, ProjectTitle)
AND ProjectManagerID = COALESCE(@.ProjectManagerID, ProjectManagerID)
AND ProjectStatusID = COALESCE(@.ProjectStatusID, ProjectStatusID)
AND DeptCode = COALESCE(@.DeptCode, DeptCode)
AND DateRequested = COALESCE(@.DateRequested, DateRequested)
AND DueDate = COALESCE(@.DueDate, DueDate)|||

The beauty of the S.P. I just posted is that it remains very simple no matter what combination of search is used. For unused string crriteria, use ''. For Integer use use 0. For dates pass a date of 31/Dec/9999.

The idea was suggested some years ago to me by Joe Celko.

|||

Woah.

Thanks for all the quick replies.

I'm away from my development machine right now, but I'll try the solutions in a few hours and let you know how it works out.

Thanks again.

|||I would recommend user "Connect"'s solution. Too many IF loops can screw up the query plan.|||

Connect:

There is a better way than using lots of IF and ELSE statements. Rewrite your stored proecedure so that it doesn't matter what parameters are passed it will always work. eg.

CREATEPROCEDURE [dbo].[GetProjectInfo]

(@.ProjectTitlevarchar(300)=NULL, @.ProjectManagerIDint=NULL, @.DeptCodevarchar(20)=NULL,

@.ProjIDvarchar(50)=NULL, @.DateRequesteddatetime=NULL, @.DueDatedatetime=NULL, @.ProjectStatusIDint=NULL)

AS

BEGIN

SETNOCOUNTON

SELECT ProjID, ProjectTitle, ProjectDetails, ProjectManagerID, RequestedBy, DateRequested, DueDate, ProjectStatusID

FROM dbo.tbl_Project

WHERE(ProjectTitle= @.ProjectTitleOR @.ProjectTitleISNULL)

AND(ProjectManagerID= @.ProjectManagerIDOR @.ProjectManagerIDISNULL)

AND(DeptCode= @.DeptCodeOR @.DeptCodeISNULL)

AND(ProjID= @.ProjIDOR @.ProjIDISNULL)

AND(.... [rest of parameters])

END

I was going over the code you posted, and it occurred to me that I made a *slight* error.

The ProjID is actually of the form DeptCode-Number (e.g. ACCT-1) and I don't have the DeptCode field in my tbl_Project.

Basically, I want to do something like:

CREATE PROCEDURE [dbo].[GetProjectInfo](@.ProjectTitlevarchar(300) =NULL, @.ProjectManagerIDint =NULL, @.DeptCodevarchar(20) =NULL,@.ProjIDvarchar(50) =NULL, @.DateRequesteddatetime =NULL, @.DueDatedatetime =NULL, @.ProjectStatusIDint =NULL)ASBEGINSETNOCOUNT ONSELECT ProjID, ProjectTitle, ProjectDetails, ProjectManagerID, RequestedBy, DateRequested, DueDate, ProjectStatusIDFROM dbo.tbl_ProjectWHERE (ProjectTitle = @.ProjectTitleOR @.ProjectTitleISNULL)AND (ProjectManagerID = @.ProjectManagerIDOR @.ProjectManagerIDISNULL)--AND (DeptCode = @.DeptCode OR @.DeptCode IS NULL)AND ((IF @.DeptCodeISNOT NULL ProjID = @.DeptCode +'-' +'%')ELSE ProjID = @.ProjIDOR @.ProjIDISNULL)-- OR @.DeptCode IS NULL))AND (DateRequested = @.DateRequestedOR @.DateRequestedISNULL)AND (DueDate = @.DueDateOR @.DueDateISNULL)AND (ProjectStatusID = @.ProjectStatusIDOR @.ProjectStatusIDISNULL)END

which is to say,

if @.DeptCode is not null, ProjID = @.DeptCode + '-' + (any number)

else ProjID = @.ProjID

I tried the code I posted, but understandably I get errors of Incorrect syntax near keyword IF an near ProjID.

Thanks again.

|||

Hi,

I was wondering if someone could help me out with the problem of implementing

IF @.DeptCode IS NOT NULL

ProjID = @.DeptCode + '-' + (any number)

ELSE ProjID = @.ProjID

in the code given by Connect.

Thanks.

|||

Try something like this:

CREATE PROCEDURE [dbo].[GetProjectInfo](@.ProjectTitlevarchar(300) =NULL, @.ProjectManagerIDint =NULL, @.DeptCodevarchar(20) =NULL,@.ProjIDvarchar(50) =NULL, @.DateRequesteddatetime =NULL, @.DueDatedatetime =NULL, @.ProjectStatusIDint =NULL)ASBEGINSET NOCOUNT ONDeclare @.nintIF @.DeptCodeISNOT NULLSET @.Deptcode = @.Deptcode +'-' +convert(varchar,@.n)SELECT ProjID, ProjectTitle, ProjectDetails, ProjectManagerID, RequestedBy, DateRequested, DueDate, ProjectStatusIDFROM dbo.tbl_ProjectWHERE (ProjectTitle = @.ProjectTitleOR @.ProjectTitleISNULL)AND (ProjectManagerID = @.ProjectManagerIDOR @.ProjectManagerIDISNULL)--AND (DeptCode = @.DeptCode OR @.DeptCode IS NULL)AND (ProjID =CaseWHEN @.DeptcodeISNOT NULLTHEN @.DeptCodeELSE @.ProjIDEND)--AND ((IF @.DeptCode IS NOT NULL ProjID = @.DeptCode + '-' + '%') ELSE ProjID = @.ProjID OR @.ProjID IS NULL)-- OR @.DeptCode IS NULL))AND (DateRequested = @.DateRequestedOR @.DateRequestedISNULL)AND (DueDate = @.DueDateOR @.DueDateISNULL)AND (ProjectStatusID = @.ProjectStatusIDOR @.ProjectStatusIDISNULL)END

|||

Hi,

Thanks for the reply.

The code you posted doesn't seem to work. Now I get 0 rows returned when using any of the parameters.

|||

I did not set any value for @.n. You just mentioned "number" but didnt say what number? does it come from a lookup table/user? For example:

SET @.n = 4

right before the check for NULL on @.Deptcode.

|||

oh. What I meant by number was, any number using a wildcard. The only wildcard I know for sql is '%', that's why if you see my example code, I did the following

ProjID = @.DeptCode + '-' + '%'

I don't know if that would work, but it shows the general idea.

|||I dont understand..can you provide some sample parameters and how you expect the ProjID to turn out?|||

sure. Let's say I run the code given below:

DECLARE@.return_valueintEXEC@.return_value = [dbo].[GetProjectInfo]@.ProjectTitle =NULL,@.ProjectManagerID =NULL,@.DeptCode ='BAT',@.ProjID =NULL,@.DateRequested =NULL,@.DueDate =NULL,@.ProjectStatusID =NULLSELECT'Return Value' = @.return_valueGO

I would want that to return all records where the ProjID = BAT-*

That is, all records that have the word BAT as the first part of the ProjID. 

If a running total is null then how to show 0.00?

Hi, I'm using Crystal Reports 8.5 and I have this issue:
I have a numeric running total field which is evaluated using a formula.
If no records satisfy that formula then the running total field becomes empty, but, instead of showing nothing (empty) I would like to show 0.00
How can I do this?

Thanks in advance

PS: I use visual basic 6.0 and reports are .rpt files outside of the .exe file.Ok, I solved it. I used a formula field which evaluates the running total, and I put the formula instead the running total on the report.|||How did you do that, Thanks.|||Create a formula having the code

If {RunningTotalField} is null then
0
else
{RunningTotalField}

IF (SQL server)

How can I make something that in MS Access query looks like this:
SELECT IIF([Amount] Is Not Null,Amount,0) AS PayedAmount ...

Regards,
MarkonniUse CASE as a general replacement for IIF. In this instance though you
can use COALESCE:
SELECT COALESCE(amount,0) ...

--
David Portas
SQL Server MVP
--|||Thank you!

"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
news:1109093023.132174.307660@.z14g2000cwz.googlegr oups.com...
> Use CASE as a general replacement for IIF. In this instance though you
> can use COALESCE:
> SELECT COALESCE(amount,0) ...
> --
> David Portas
> SQL Server MVP
> --sql

IDENTITY values in a stored procedure

Hi All,
This is my stored procedure

CREATE PROCEDURE testProc AS
BEGIN
CREATE TABLE #tblTest(ID INT NOT NULL IDENTITY, Col1 INT)
INSERT INTO #tblTest(Col1)
SELECT colA FROM tableA ORDER BY colA

END

This is my simple procedure, I wanted to know whether the IDENTITY values created in #tblTest will always be consistent, I mean without losing any number in between. i.e. ID column will have values 1,2,3,4,5....
or is there any chance of ID column having values like 1,2, 4, 6,7,8...

Please reply...
qaAs long as you don't do any deletes from your temp table, your identity column should remain sequential with no gaps.|||Thanks for your quick response.sql

Identity keys in a One-to-Zero-or-Many relationship

Considering the following:
CREATE TABLE CustomerTypes
(
PKCustomerType INT IDENTITY (1,1) NOT NULL,
CustomerTypeDesc VARCHAR(30)
)
GO
INSERT CustomerTypes (CustomerTypeDesc) VALUES ('Retail')
INSERT CustomerTypes (CustomerTypeDesc) VALUES ('Wholesale')
INSERT CustomerTypes (CustomerTypeDesc) VALUES ('Corporate')
CREATE TABLE Customers
(
PKCustomer INT IDENTITY (1,1) NOT NULL,
CustomerID VARCHAR(20),
FKCustomerType INT
)
GO
INSERT Customers (CustomerID, FKCustomerType) Values ('CUST001', 1)
INSERT Customers Values ('CUST002', 3)
INSERT Customers Values ('CUST003', 2)
How should I handle a case where I don't want to specify a customer
type for whatever reason? Perhaps it is unknown or not applicable in
some cases. I could add a 'None or N/A' record in the CustomerTypes
table for that purpose, but when validating the data in an INSERT
stored procedure, for example, I would like to have a value to use as
default in the case of a missing of invalid field value being passed
for FKCustomerType. If it weren't an identity field in the
CustomerTypes table, I could just create a CustomerType record with a
PKCustomerType value of 0 and always use that value in such cases. With
it being an identity field, however, I can't count on a specific value
for that purpose. It seems I have two options:
1) Create the CustomerTypes table without PKCustomerType being an
identity field...insert the 'None or N/A' record, assigning it a
PKCustomerType value of, say, 0...alter the table to make
PKCustomerType and identity field (1,1)
2) Merely use NULL in Customers where CustomerType isn't specified.
Is there a better way? If not, any insight on why one approach would be
better than the other? I'm leaning toward option 2 at this point.> 2) Merely use NULL in Customers where CustomerType isn't specified.
If CustomerType is not specified because it is unknown, then this may be a
valid approach.
However, you may want to give users the option of saying Unknown, and have
it be a value in the CustomerTypes table. Then if you later want to
differentiate between unknown and unspecified, you can add an option.
There is no reason to hard-code the default value in the parameter to the
stored procedure. Have the application generate its dropdown from the
CustomerTypes table, and you are done.
A|||>> Considering the following: <<
Let's fix what you posted so that it follows the most basic RDBMS
design principles. For example, why do you have no keys? Why did you
think that IDENTITY is every used? Why did you use PK- prefixes in
violation of ISO-11179 rules?
CREATE TABLE CustomerTypes
(customer_type INTEGER NOT NULL PRIMARY KEY,
customer_type_desc VARCHAR(30) NOT NULL);
I see from the use of IDENTITY that you did not bother with designing
an encoding scheme. You might want to learn how to do that
CREATE TABLE Customers
(customer_id VARCHAR(20) NOT NULL PRIMARY KEY,
(customer_type INTEGER NOT NULL
REFERENCES CustomerTypes (customer_type)
ON UPDATE CASCADE,
. );
VARCHAR(20) is a bit long, but by the definition of an identifier, this
has to be your key. Frankly, I would look for a DUNS number or some
indusrty standard code.
Stop putting prefixes that tell you **how** a data element is used in a
table. The name of a data element is supposed to tell you **what** it
is.
Then you need codes for those situations if they are logically
different. For example, the ICD codes for disease have '000.000' which
means "undiagnosed" and '999.999' which means "we did all the test and
still don't know!" -- very different kinds of missing data! VERY
IMPORTANT to distinguish them!! Matter of life and death, in fact.
] value being passed for FKCustomerType [sic]. <<
Now we are getting to your REAL problem. Let's get back to the basics
of an RDBMS. Rows are not records; fields are not columns; tables are
not files; there is no sequential access or ordering in an RDBMS, so
faking a record number or the lines on a piece of paper with a
proprietary IDENTITY property is dead wrong. You do not have the right
mindset, and all you are going to get on a Newsgroup is a few kludges
to help you fake it for a long period of time before the collapse.
a specific value for that purpose. <<
Surprise! Surprise! Surprise! See how non-relational, non-verifiable,
non-portable proprietary extensions screw up things?
Yes; do it right or kludge it :)
<<
Almost! Stop using IDENTITY. This is an RDBMS and not a sequential
file system.
Maybe; do you need to know anything about the missing values? Or just
that it is missing? Get a copy of SQL PROGRAMMING STYLE, SQL FOR
SMARTIES or DATA & DATABASES and then read the chapters on scales &
measurements, and how to design encoding schemes. The research pattern
is simple:
1) Look for industry standards (Google it!)
2) Look for company standards (see the accounting department for help)
3) In the remaining 5% of the cases where you have to invent something,
pick a type of encoding and follow the rules for good design. My guess
in this example is a hierachy or vector code because customers break
down into tax/tax exempt, domestic/foreign and so forth within
retailers and wholesalers.|||> design principles. For example, why do you have no keys? Why did you
> think that IDENTITY is every used? Why did you use PK- prefixes in
Why do you think SURROGATE KEYS are never used? It would appear you are one
of the only people in the industry who do not use them.
Perhaps its an implementation experience thing compared to theory.

> Almost! Stop using IDENTITY. This is an RDBMS and not a sequential
> file system.
Your bias and misunderstanding of how IDENTITY works is embarrasing - READ
THE MANUAL!

> that it is missing? Get a copy of SQL PROGRAMMING STYLE, SQL FOR
> SMARTIES or DATA & DATABASES and then read the chapters on scales &
Only if you want theory, for practical advice get a SQL Server specific
book.
Tony Rogerson
SQL Server MVP
http://sqlblogcasts.com/blogs/tonyrogerson - technical commentary from a SQL
Server Consultant
http://sqlserverfaq.com - free video tutorials
"--CELKO--" <jcelko212@.earthlink.net> wrote in message
news:1151601604.581696.66800@.75g2000cwc.googlegroups.com...
> Let's fix what you posted so that it follows the most basic RDBMS
> design principles. For example, why do you have no keys? Why did you
> think that IDENTITY is every used? Why did you use PK- prefixes in
> violation of ISO-11179 rules?
> CREATE TABLE CustomerTypes
> (customer_type INTEGER NOT NULL PRIMARY KEY,
> customer_type_desc VARCHAR(30) NOT NULL);
> I see from the use of IDENTITY that you did not bother with designing
> an encoding scheme. You might want to learn how to do that
> CREATE TABLE Customers
> (customer_id VARCHAR(20) NOT NULL PRIMARY KEY,
> (customer_type INTEGER NOT NULL
> REFERENCES CustomerTypes (customer_type)
> ON UPDATE CASCADE,
> .. );
> VARCHAR(20) is a bit long, but by the definition of an identifier, this
> has to be your key. Frankly, I would look for a DUNS number or some
> indusrty standard code.
> Stop putting prefixes that tell you **how** a data element is used in a
> table. The name of a data element is supposed to tell you **what** it
> is.
>
> Then you need codes for those situations if they are logically
> different. For example, the ICD codes for disease have '000.000' which
> means "undiagnosed" and '999.999' which means "we did all the test and
> still don't know!" -- very different kinds of missing data! VERY
> IMPORTANT to distinguish them!! Matter of life and death, in fact.
>
> Now we are getting to your REAL problem. Let's get back to the basics
> of an RDBMS. Rows are not records; fields are not columns; tables are
> not files; there is no sequential access or ordering in an RDBMS, so
> faking a record number or the lines on a piece of paper with a
> proprietary IDENTITY property is dead wrong. You do not have the right
> mindset, and all you are going to get on a Newsgroup is a few kludges
> to help you fake it for a long period of time before the collapse.
>
> Surprise! Surprise! Surprise! See how non-relational, non-verifiable,
> non-portable proprietary extensions screw up things?
>
> Yes; do it right or kludge it :)
>
> Almost! Stop using IDENTITY. This is an RDBMS and not a sequential
> file system.
>
> Maybe; do you need to know anything about the missing values? Or just
> that it is missing? Get a copy of SQL PROGRAMMING STYLE, SQL FOR
> SMARTIES or DATA & DATABASES and then read the chapters on scales &
> measurements, and how to design encoding schemes. The research pattern
> is simple:
> 1) Look for industry standards (Google it!)
> 2) Look for company standards (see the accounting department for help)
> 3) In the remaining 5% of the cases where you have to invent something,
> pick a type of encoding and follow the rules for good design. My guess
> in this example is a hierachy or vector code because customers break
> down into tax/tax exempt, domestic/foreign and so forth within
> retailers and wholesalers.
>|||> How should I handle a case where I don't want to specify a customer
> type for whatever reason? Perhaps it is unknown or not applicable in
It should be NULL because its not specified, if you want to give it a value
you really ought to have a CustomerType of 'Not Specified'.

> CREATE TABLE CustomerTypes
> (
> PKCustomerType INT IDENTITY (1,1) NOT NULL,
> CustomerTypeDesc VARCHAR(30)
> )
> GO
> INSERT CustomerTypes (CustomerTypeDesc) VALUES ('Retail')
> INSERT CustomerTypes (CustomerTypeDesc) VALUES ('Wholesale')
> INSERT CustomerTypes (CustomerTypeDesc) VALUES ('Corporate')
> CREATE TABLE Customers
> (
> PKCustomer INT IDENTITY (1,1) NOT NULL,
> CustomerID VARCHAR(20),
> FKCustomerType INT
> )
> GO
> INSERT Customers (CustomerID, FKCustomerType) Values ('CUST001', 1)
> INSERT Customers Values ('CUST002', 3)
> INSERT Customers Values ('CUST003', 2)
You probably need to have a slight rethink on the constraints....
CREATE TABLE CustomerTypes
(
id INT IDENTITY (1,1) NOT NULL constraint sk_customertypes unique
clustered,
CustomerTypeDesc VARCHAR(30) constraint pk_customertype primary key
nonclustered
)
INSERT CustomerTypes (CustomerTypeDesc) VALUES ('Retail')
INSERT CustomerTypes (CustomerTypeDesc) VALUES ('Wholesale')
INSERT CustomerTypes (CustomerTypeDesc) VALUES ('Corporate')
CREATE TABLE Customers
(
id INT IDENTITY (1,1) NOT NULL constraint sk_customer unique
clustered,
CustomerID VARCHAR(20) not null constraint pk_customer primary key
nonclustered,
CustomerType_id INT NULL references CustomerTypes( id )
)
INSERT Customers (CustomerID, CustomerType_id) Values ('CUST001', 1)
INSERT Customers (CustomerID, CustomerType_id) Values ('CUST003', NULL)
Tony.
Tony Rogerson
SQL Server MVP
http://sqlblogcasts.com/blogs/tonyrogerson - technical commentary from a SQL
Server Consultant
http://sqlserverfaq.com - free video tutorials
"Richard Carpenter" <rumbledor@.hotmail.com> wrote in message
news:1151594340.887702.165300@.b68g2000cwa.googlegroups.com...
> Considering the following:
> CREATE TABLE CustomerTypes
> (
> PKCustomerType INT IDENTITY (1,1) NOT NULL,
> CustomerTypeDesc VARCHAR(30)
> )
> GO
> INSERT CustomerTypes (CustomerTypeDesc) VALUES ('Retail')
> INSERT CustomerTypes (CustomerTypeDesc) VALUES ('Wholesale')
> INSERT CustomerTypes (CustomerTypeDesc) VALUES ('Corporate')
> CREATE TABLE Customers
> (
> PKCustomer INT IDENTITY (1,1) NOT NULL,
> CustomerID VARCHAR(20),
> FKCustomerType INT
> )
> GO
> INSERT Customers (CustomerID, FKCustomerType) Values ('CUST001', 1)
> INSERT Customers Values ('CUST002', 3)
> INSERT Customers Values ('CUST003', 2)
> How should I handle a case where I don't want to specify a customer
> type for whatever reason? Perhaps it is unknown or not applicable in
> some cases. I could add a 'None or N/A' record in the CustomerTypes
> table for that purpose, but when validating the data in an INSERT
> stored procedure, for example, I would like to have a value to use as
> default in the case of a missing of invalid field value being passed
> for FKCustomerType. If it weren't an identity field in the
> CustomerTypes table, I could just create a CustomerType record with a
> PKCustomerType value of 0 and always use that value in such cases. With
> it being an identity field, however, I can't count on a specific value
> for that purpose. It seems I have two options:
> 1) Create the CustomerTypes table without PKCustomerType being an
> identity field...insert the 'None or N/A' record, assigning it a
> PKCustomerType value of, say, 0...alter the table to make
> PKCustomerType and identity field (1,1)
> 2) Merely use NULL in Customers where CustomerType isn't specified.
> Is there a better way? If not, any insight on why one approach would be
> better than the other? I'm leaning toward option 2 at this point.
>|||Why not use a referential integrity constraint?
Unknown and Inapplicable are distinct reasons attributed for missing data.
And therefore, if your business demands that distinction, it makes sense to
use separate values to represent them in a table.
Anith|||Just to add, inapplicable attributes are best addressed using an entity
super-type/sub-type relationship. However, commonly people use to kludge
them with NULLs as an easy workaround.
Anith|||"Richard Carpenter" <rumbledor@.hotmail.com> wrote in message
news:1151594340.887702.165300@.b68g2000cwa.googlegroups.com...
> Considering the following:
> CREATE TABLE CustomerTypes
> (
> PKCustomerType INT IDENTITY (1,1) NOT NULL,
> CustomerTypeDesc VARCHAR(30)
> )
> GO
> INSERT CustomerTypes (CustomerTypeDesc) VALUES ('Retail')
> INSERT CustomerTypes (CustomerTypeDesc) VALUES ('Wholesale')
> INSERT CustomerTypes (CustomerTypeDesc) VALUES ('Corporate')
> CREATE TABLE Customers
> (
> PKCustomer INT IDENTITY (1,1) NOT NULL,
> CustomerID VARCHAR(20),
> FKCustomerType INT
> )
> GO
> INSERT Customers (CustomerID, FKCustomerType) Values ('CUST001', 1)
> INSERT Customers Values ('CUST002', 3)
> INSERT Customers Values ('CUST003', 2)
> How should I handle a case where I don't want to specify a customer
> type for whatever reason? Perhaps it is unknown or not applicable in
> some cases. I could add a 'None or N/A' record in the CustomerTypes
> table for that purpose, but when validating the data in an INSERT
> stored procedure, for example, I would like to have a value to use as
> default in the case of a missing of invalid field value being passed
> for FKCustomerType. If it weren't an identity field in the
> CustomerTypes table, I could just create a CustomerType record with a
> PKCustomerType value of 0 and always use that value in such cases. With
> it being an identity field, however, I can't count on a specific value
> for that purpose. It seems I have two options:
> 1) Create the CustomerTypes table without PKCustomerType being an
> identity field...insert the 'None or N/A' record, assigning it a
> PKCustomerType value of, say, 0...alter the table to make
> PKCustomerType and identity field (1,1)
> 2) Merely use NULL in Customers where CustomerType isn't specified.
> Is there a better way? If not, any insight on why one approach would be
> better than the other? I'm leaning toward option 2 at this point.
>
If you add some keys to the CustomerTypes table you will be able to identify
the "Unknown" value by its logical key instead of the surrogate.
The "Inapplicable" case would probably be better handled by decomposing the
Customers table.
CREATE TABLE CustomerTypes
(
PKCustomerType INT IDENTITY (1,1) NOT NULL
CONSTRAINT PK_CustomerTypes PRIMARY KEY,
CustomerTypeDesc VARCHAR(30) NOT NULL
CONSTRAINT AK1_CustomerTypes UNIQUE
);
GO
INSERT CustomerTypes (CustomerTypeDesc) VALUES ('Retail');
INSERT CustomerTypes (CustomerTypeDesc) VALUES ('Wholesale');
INSERT CustomerTypes (CustomerTypeDesc) VALUES ('Corporate');
INSERT CustomerTypes (CustomerTypeDesc) VALUES ('Unknown');
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
--|||Good gravy! I'll try to respond to your individual points without
taking too much offense to your abrasive tone...
--CELKO-- wrote:
> Let's fix what you posted so that it follows the most basic RDBMS
> design principles.
Really, don't trouble yourself. It was a simple example I threw
together to try and illustrate my question. Nothing more. I wasn't
shooting for theoretical precision. I didn't (and still don't, as I
will explain in a moment) think it that necessary in this case.

> For example, why do you have no keys? Why did you
> think that IDENTITY is every used? Why did you use PK- prefixes in
> violation of ISO-11179 rules?
> CREATE TABLE CustomerTypes
> (customer_type INTEGER NOT NULL PRIMARY KEY,
> customer_type_desc VARCHAR(30) NOT NULL);
> I see from the use of IDENTITY that you did not bother with designing
> an encoding scheme. You might want to learn how to do that
> CREATE TABLE Customers
> (customer_id VARCHAR(20) NOT NULL PRIMARY KEY,
> (customer_type INTEGER NOT NULL
> REFERENCES CustomerTypes (customer_type)
> ON UPDATE CASCADE,
> .. );
> VARCHAR(20) is a bit long, but by the definition of an identifier, this
> has to be your key. Frankly, I would look for a DUNS number or some
> indusrty standard code.
It is what it is. If my particular (hypothetical, I might add) business
process requires the use of specific proprietary customer types, then
why complicate it?

> Stop putting prefixes that tell you **how** a data element is used in a
> table. The name of a data element is supposed to tell you **what** it
> is.
I really don't see the significance of the distinction. The fact that
the name of the field starts with PK *does* tell me what it is. I
really think this one is a matter of personal preference.

> Then you need codes for those situations if they are logically
> different. For example, the ICD codes for disease have '000.000' which
> means "undiagnosed" and '999.999' which means "we did all the test and
> still don't know!" -- very different kinds of missing data! VERY
> IMPORTANT to distinguish them!! Matter of life and death, in fact.
A bit dramatic, but I hear what you're saying. However, my question
really doesn't have anything to do with what values are available to
the user, but how to handle it when the user doesn't feel inclined to
specify a fact that is not strictly required. On the application's data
entry screen, the user may only need to worry about some of the
available fields. There is no reason to force them to touch every one
when not all are relevant in every case. My question is, how to best
handle that foreign key [column] value in that case.

ic] value being passed for FKCustomerType [sic]. <<
> Now we are getting to your REAL problem. Let's get back to the basics
> of an RDBMS. Rows are not records; fields are not columns; tables are
> not files; there is no sequential access or ordering in an RDBMS, so
> faking a record number or the lines on a piece of paper with a
> proprietary IDENTITY property is dead wrong. You do not have the right
> mindset, and all you are going to get on a Newsgroup is a few kludges
> to help you fake it for a long period of time before the collapse.
Yeah, I'm guilty of referring to them in the very manner that makes a
certain type of person grit their teeth. I think that since we both
know exactly what I mean, then it really isn't an issue worth allowing
to cloud the matter. Wouldn't you agree?

on a specific value for that purpose. <<
> Surprise! Surprise! Surprise! See how non-relational, non-verifiable,
> non-portable proprietary extensions screw up things?
>
Hence my original question. Again, I really don't understand why it has
to be this difficult. How would *you* go about it, if forcing the user
to touch every field on the data entry form is *not* acceptable?

> Yes; do it right or kludge it :)
>
1) <<
> Almost! Stop using IDENTITY. This is an RDBMS and not a sequential
> file system.
There is nothing sequential about the requirements I've established
here. I don't care if the key is sequentially numbered, random
alpha-numeric or caveman hieroglyphics. All I require is that it be
guaranteed to be unique and generated automatically.

> Maybe; do you need to know anything about the missing values? Or just
> that it is missing? Get a copy of SQL PROGRAMMING STYLE, SQL FOR
> SMARTIES or DATA & DATABASES and then read the chapters on scales &
> measurements, and how to design encoding schemes. The research pattern
> is simple:
> 1) Look for industry standards (Google it!)
> 2) Look for company standards (see the accounting department for help)
> 3) In the remaining 5% of the cases where you have to invent something,
> pick a type of encoding and follow the rules for good design. My guess
> in this example is a hierachy or vector code because customers break
> down into tax/tax exempt, domestic/foreign and so forth within
> retailers and wholesalers.
Honestly, that just seemed like a whole lot of unnecessary nit-picking
and tangent traversal than was really required here. I can understand
if you feel in your infinite wisdom that it is better to suggest to me
where else I might direct my search to find the answer than give it to
me directly, but to put so much effort into avoiding answering my
question while at the same time trying to educate me (or, arguably more
accurate, indoctrinate me according to your own personal opinions) in
related regards does make me wonder who's best interest you have at
heart here.|||Anith Sen wrote:
> Why not use a referential integrity constraint?
> Unknown and Inapplicable are distinct reasons attributed for missing data.
> And therefore, if your business demands that distinction, it makes sense t
o
> use separate values to represent them in a table.
>
That would be my typical thinking as well, but RI doesn't work if the
value is not required unless a default constraint is specified. With an
identity key, the default value for the foreign key column cannot be
specified in the table definition.
On second thought, a better illustration of my question would be a name
suffix (Jr., Sr. III, etc.). A person won't necessarily *have* a suffix
in their name, yet forcing the user to select "None" from a list is
perhaps a bit cumbersome from a UI standpoint. If that suffix column in
the Customers table is a foreign key referencing the primary key column
in a reference table, and that primary key column is an identity type
key, then it would seem that the only workable foreign key value would
be NULL.

Identity Column Null? WHAT?

Hey all
Wanted to check the identity of my column so I ran this:
dbcc checkident (tablename)
It came back saying it was null!!!
"Checking identity information: current identity value 'NULL', current
column value 'NULL'."
What gives? When I enter a value in the column...it starts with 0. I want it
to start with 1!!! Any suggestions? I already tried reseeding it and it still
says null grrrr.
Thanks
Theresa
> What gives? When I enter a value in the column...it starts with 0. I want
> it
> to start with 1!!! Any suggestions?
Drop the table and create it correctly?
CREATE TABLE dbo.MyTable
(
IdentityColumn INT NOT NULL IDENTITY(1,1)
--, ... other columns
);
Aaron Bertrand
SQL Server MVP
http://www.sqlblog.com/
http://www.aspfaq.com/5006
|||"Aaron Bertrand [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
news:%23TiZk$RXHHA.4480@.TK2MSFTNGP04.phx.gbl...
> Drop the table and create it correctly?
> CREATE TABLE dbo.MyTable
> (
> IdentityColumn INT NOT NULL IDENTITY(1,1)
> --, ... other columns
> );
>
And people wonder why Celko hates IDENTITY so much. :-)

> --
> Aaron Bertrand
> SQL Server MVP
> http://www.sqlblog.com/
> http://www.aspfaq.com/5006
>
>
Greg Moore
SQL Server DBA Consulting
sql (at) greenms.com http://www.greenms.com

Identity Column Null? WHAT?

Hey all
Wanted to check the identity of my column so I ran this:
dbcc checkident (tablename)
It came back saying it was null!!!
"Checking identity information: current identity value 'NULL', current
column value 'NULL'."
What gives? When I enter a value in the column...it starts with 0. I want it
to start with 1!!! Any suggestions? I already tried reseeding it and it stil
l
says null grrrr.
Thanks
Theresa> What gives? When I enter a value in the column...it starts with 0. I want
> it
> to start with 1!!! Any suggestions?
Drop the table and create it correctly?
CREATE TABLE dbo.MyTable
(
IdentityColumn INT NOT NULL IDENTITY(1,1)
--, ... other columns
);
Aaron Bertrand
SQL Server MVP
http://www.sqlblog.com/
http://www.aspfaq.com/5006|||"Aaron Bertrand [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in mess
age
news:%23TiZk$RXHHA.4480@.TK2MSFTNGP04.phx.gbl...
> Drop the table and create it correctly?
> CREATE TABLE dbo.MyTable
> (
> IdentityColumn INT NOT NULL IDENTITY(1,1)
> --, ... other columns
> );
>
And people wonder why Celko hates IDENTITY so much. :-)

> --
> Aaron Bertrand
> SQL Server MVP
> http://www.sqlblog.com/
> http://www.aspfaq.com/5006
>
>
Greg Moore
SQL Server DBA Consulting
sql (at) greenms.com http://www.greenms.com

Identity Column Null? WHAT?

Hey all
Wanted to check the identity of my column so I ran this:
dbcc checkident (tablename)
It came back saying it was null!!!
"Checking identity information: current identity value 'NULL', current
column value 'NULL'."
What gives? When I enter a value in the column...it starts with 0. I want it
to start with 1!!! Any suggestions? I already tried reseeding it and it still
says null grrrr.
Thanks
Theresa> What gives? When I enter a value in the column...it starts with 0. I want
> it
> to start with 1!!! Any suggestions?
Drop the table and create it correctly?
CREATE TABLE dbo.MyTable
(
IdentityColumn INT NOT NULL IDENTITY(1,1)
--, ... other columns
);
--
Aaron Bertrand
SQL Server MVP
http://www.sqlblog.com/
http://www.aspfaq.com/5006|||"Aaron Bertrand [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
news:%23TiZk$RXHHA.4480@.TK2MSFTNGP04.phx.gbl...
>> What gives? When I enter a value in the column...it starts with 0. I want
>> it
>> to start with 1!!! Any suggestions?
> Drop the table and create it correctly?
> CREATE TABLE dbo.MyTable
> (
> IdentityColumn INT NOT NULL IDENTITY(1,1)
> --, ... other columns
> );
>
And people wonder why Celko hates IDENTITY so much. :-)
> --
> Aaron Bertrand
> SQL Server MVP
> http://www.sqlblog.com/
> http://www.aspfaq.com/5006
>
>
--
Greg Moore
SQL Server DBA Consulting
sql (at) greenms.com http://www.greenms.com

Identity

Hello,
I have done a lot of searching on the internet to change an "ID [int] Not
Null" to an "ID [int] Identity (1,1) Not Null". Every example I have found
does not work. I get an error at 'Identity'. I need to send a single query
against the table (Alter Table Customers) to alter the Column ID to an
identity. I am using VB.NET and a Command Object to send the query - If I
can get it to work in SQL Server Express Manager, I can get it to work in my
program.
This is what Microsoft says but does not work:
ALTER TABLE table_name
{ [ ALTER COLUMN column_name
{DROP DEFAULT
| SET DEFAULT constant_expression
| IDENTITY [ ( seed , increment ) ]
}
* ALTER TABLE Customers
ALTER COLUMN ID IDENTITY(1,1) Not Null
Any assisance will be greatly appreciated.
Thanks,
Chuck
Hi Charles
Please always let us know what version you are using.
The reason you are not finding a way to do this is that it is not possible.
Where did you find that "Microsoft" shows ALTER COLUMN to support changing a
column to IDENTITY? You can add a new column with the IDENTITY property, but
you can't add this property to an existing column. If it's actually in the
BOL, it is a bug.
If you could change a column to have the IDENTITY property, what would
expect SQL Server to do with the existing values in that column? Let us
know, and we can probably come up with a workaround, but probably not a
single statement workaround.
HTH
Kalen Delaney, SQL Server MVP
www.InsideSQLServer.com
http://sqlblog.com
"Charles A. Lackman" <Charles@.CreateItSoftware.net> wrote in message
news:u%23LgqyGqHHA.588@.TK2MSFTNGP06.phx.gbl...
> Hello,
> I have done a lot of searching on the internet to change an "ID [int] Not
> Null" to an "ID [int] Identity (1,1) Not Null". Every example I have
> found
> does not work. I get an error at 'Identity'. I need to send a single
> query
> against the table (Alter Table Customers) to alter the Column ID to an
> identity. I am using VB.NET and a Command Object to send the query - If I
> can get it to work in SQL Server Express Manager, I can get it to work in
> my
> program.
> This is what Microsoft says but does not work:
> ALTER TABLE table_name
> { [ ALTER COLUMN column_name
> {DROP DEFAULT
> | SET DEFAULT constant_expression
> | IDENTITY [ ( seed , increment ) ]
> }
> * ALTER TABLE Customers
> ALTER COLUMN ID IDENTITY(1,1) Not Null
> Any assisance will be greatly appreciated.
> Thanks,
> Chuck
>
|||Can you simply build a new table and move in the existing data? Use set
identity_insert ... on/off for this when inserting the data.
TheSQLGuru
President
Indicium Resources, Inc.
"Charles A. Lackman" <Charles@.CreateItSoftware.net> wrote in message
news:u%23LgqyGqHHA.588@.TK2MSFTNGP06.phx.gbl...
> Hello,
> I have done a lot of searching on the internet to change an "ID [int] Not
> Null" to an "ID [int] Identity (1,1) Not Null". Every example I have
> found
> does not work. I get an error at 'Identity'. I need to send a single
> query
> against the table (Alter Table Customers) to alter the Column ID to an
> identity. I am using VB.NET and a Command Object to send the query - If I
> can get it to work in SQL Server Express Manager, I can get it to work in
> my
> program.
> This is what Microsoft says but does not work:
> ALTER TABLE table_name
> { [ ALTER COLUMN column_name
> {DROP DEFAULT
> | SET DEFAULT constant_expression
> | IDENTITY [ ( seed , increment ) ]
> }
> * ALTER TABLE Customers
> ALTER COLUMN ID IDENTITY(1,1) Not Null
> Any assisance will be greatly appreciated.
> Thanks,
> Chuck
>
|||On 6 Jun, 20:18, "Kalen Delaney" <replies@.public_newsgroups.com>
wrote:
> Hi Charles
> Please always let us know what version you are using.
> The reason you are not finding a way to do this is that it is not possible.
> Where did you find that "Microsoft" shows ALTER COLUMN to support changing a
> column to IDENTITY? You can add a new column with the IDENTITY property, but
> you can't add this property to an existing column. If it's actually in the
> BOL, it is a bug.
>
Kalen,
It is in BOL but it's not a mistake, it's just that it applies to
Compact Edition only. I'd guess that Charles is using some other
edition and didn't notice the title at the top of the page:
http://msdn2.microsoft.com/en-us/library/ms174123.aspx
http://msdn2.microsoft.com/en-us/library/ms190273.aspx
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
|||Hello,
This is the link to the web site:
http://msdn2.microsoft.com/en-us/library/ms174123.aspx
I am using SQL Express 2005. I guess I could just drop the column and
recreate it as an identity column. The data in the ID column can be
dropped, providing it is replaced with unique numbers.
If I drop the column and recreate it, it places the column at the end of the
table. I know this might seem like a stupid question, but can I add the
column and have it move to the first position?
Thanks,
Chuck
"Charles A. Lackman" <Charles@.CreateItSoftware.net> wrote in message
news:u%23LgqyGqHHA.588@.TK2MSFTNGP06.phx.gbl...
Hello,
I have done a lot of searching on the internet to change an "ID [int] Not
Null" to an "ID [int] Identity (1,1) Not Null". Every example I have found
does not work. I get an error at 'Identity'. I need to send a single query
against the table (Alter Table Customers) to alter the Column ID to an
identity. I am using VB.NET and a Command Object to send the query - If I
can get it to work in SQL Server Express Manager, I can get it to work in my
program.
This is what Microsoft says but does not work:
ALTER TABLE table_name
{ [ ALTER COLUMN column_name
{DROP DEFAULT
| SET DEFAULT constant_expression
| IDENTITY [ ( seed , increment ) ]
}
* ALTER TABLE Customers
ALTER COLUMN ID IDENTITY(1,1) Not Null
Any assisance will be greatly appreciated.
Thanks,
Chuck
|||> This is the link to the web site:
> http://msdn2.microsoft.com/en-us/library/ms174123.aspx
And as David pointed out, you are looking in the wrong edition of Books
Online. Notice at the top of the page:
SQL Server 2005 Compact Edition Books Online
SQL Server Compact Edition Programming
SQL Reference (SQL Server Compact Edition)
SQL Server 2005 Compact Edition Books Online
ALTER TABLE (SQL Server Compact Edition)
It's quite unfortunate that the syntax for ALTER TABLE is different, but
it's also unfortunate that it would be hard to make it even more clear that
this topic is meant for compact edition only. :-(

> If I drop the column and recreate it, it places the column at the end of
> the
> table. I know this might seem like a stupid question, but can I add the
> column and have it move to the first position?
Column order should not really matter. If you really want it that way, then
drop the table and re-create it. (You can create a new table with a new
name and an IDENTITY column, populate the other columns from the existing
data, drop the old table, and re-name the new table.)
Aaron Bertrand
SQL Server MVP
http://www.sqlblog.com/
http://www.aspfaq.com/5006
|||Thanks, David. I guess it's important for posters to tell us both version
and edition!
HTH
Kalen Delaney, SQL Server MVP
www.InsideSQLServer.com
http://sqlblog.com
"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
news:1181158271.703889.92780@.z28g2000prd.googlegro ups.com...
> On 6 Jun, 20:18, "Kalen Delaney" <replies@.public_newsgroups.com>
> wrote:
> Kalen,
> It is in BOL but it's not a mistake, it's just that it applies to
> Compact Edition only. I'd guess that Charles is using some other
> edition and didn't notice the title at the top of the page:
> http://msdn2.microsoft.com/en-us/library/ms174123.aspx
> http://msdn2.microsoft.com/en-us/library/ms190273.aspx
> --
> 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
> --
>
>
|||On 6 Jun, 20:31, "Charles A. Lackman" <Char...@.CreateItSoftware.net>
wrote:
> Hello,
> This is the link to the web site:http://msdn2.microsoft.com/en-us/library/ms174123.aspx
> I am using SQL Express 2005. I guess I could just drop the column and
> recreate it as an identity column. The data in the ID column can be
> dropped, providing it is replaced with unique numbers.
> If I drop the column and recreate it, it places the column at the end of the
> table. I know this might seem like a stupid question, but can I add the
> column and have it move to the first position?
>
You'd have to recreate the entire table to do that. At runtime the
column order should be defined by a SELECT statement not by the order
the columns were created in, so for most purposes it shouldn't be a
problem to put the column at the end. It may cause some inconvenience
during support and development however.
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
|||Hi,
I had the exact same problem, and was very glad to find an easy solution
with a working example here:
http://decipherinfosys.wordpress.com/2007/09/14/altering-a-regular-colum
n-to-be-an-identity-column-in-sql-server/
In short, you won't need to drop and recreate the entire table, only the
primary key constraint and then the column. By switching SET
IDENTITY_INSERT on and off, you can retain the original values of the
primary key column.
But just read the article, it makes everything quite clear.
Good luck!
Dennis
*** Sent via Developersdex http://www.codecomments.com ***

Identity

Hello,
I have done a lot of searching on the internet to change an "ID [int] Not
Null" to an "ID [int] Identity (1,1) Not Null". Every example I have found
does not work. I get an error at 'Identity'. I need to send a single query
against the table (Alter Table Customers) to alter the Column ID to an
identity. I am using VB.NET and a Command Object to send the query - If I
can get it to work in SQL Server Express Manager, I can get it to work in my
program.
This is what Microsoft says but does not work:
ALTER TABLE table_name
{ [ ALTER COLUMN column_name
{DROP DEFAULT
| SET DEFAULT constant_expression
| IDENTITY [ ( seed , increment ) ]
}
* ALTER TABLE Customers
ALTER COLUMN ID IDENTITY(1,1) Not Null
Any assisance will be greatly appreciated.
Thanks,
ChuckHi Charles
Please always let us know what version you are using.
The reason you are not finding a way to do this is that it is not possible.
Where did you find that "Microsoft" shows ALTER COLUMN to support changing a
column to IDENTITY? You can add a new column with the IDENTITY property, but
you can't add this property to an existing column. If it's actually in the
BOL, it is a bug.
If you could change a column to have the IDENTITY property, what would
expect SQL Server to do with the existing values in that column? Let us
know, and we can probably come up with a workaround, but probably not a
single statement workaround.
--
HTH
Kalen Delaney, SQL Server MVP
www.InsideSQLServer.com
http://sqlblog.com
"Charles A. Lackman" <Charles@.CreateItSoftware.net> wrote in message
news:u%23LgqyGqHHA.588@.TK2MSFTNGP06.phx.gbl...
> Hello,
> I have done a lot of searching on the internet to change an "ID [int] Not
> Null" to an "ID [int] Identity (1,1) Not Null". Every example I have
> found
> does not work. I get an error at 'Identity'. I need to send a single
> query
> against the table (Alter Table Customers) to alter the Column ID to an
> identity. I am using VB.NET and a Command Object to send the query - If I
> can get it to work in SQL Server Express Manager, I can get it to work in
> my
> program.
> This is what Microsoft says but does not work:
> ALTER TABLE table_name
> { [ ALTER COLUMN column_name
> {DROP DEFAULT
> | SET DEFAULT constant_expression
> | IDENTITY [ ( seed , increment ) ]
> }
> * ALTER TABLE Customers
> ALTER COLUMN ID IDENTITY(1,1) Not Null
> Any assisance will be greatly appreciated.
> Thanks,
> Chuck
>|||Can you simply build a new table and move in the existing data? Use set
identity_insert ... on/off for this when inserting the data.
--
TheSQLGuru
President
Indicium Resources, Inc.
"Charles A. Lackman" <Charles@.CreateItSoftware.net> wrote in message
news:u%23LgqyGqHHA.588@.TK2MSFTNGP06.phx.gbl...
> Hello,
> I have done a lot of searching on the internet to change an "ID [int] Not
> Null" to an "ID [int] Identity (1,1) Not Null". Every example I have
> found
> does not work. I get an error at 'Identity'. I need to send a single
> query
> against the table (Alter Table Customers) to alter the Column ID to an
> identity. I am using VB.NET and a Command Object to send the query - If I
> can get it to work in SQL Server Express Manager, I can get it to work in
> my
> program.
> This is what Microsoft says but does not work:
> ALTER TABLE table_name
> { [ ALTER COLUMN column_name
> {DROP DEFAULT
> | SET DEFAULT constant_expression
> | IDENTITY [ ( seed , increment ) ]
> }
> * ALTER TABLE Customers
> ALTER COLUMN ID IDENTITY(1,1) Not Null
> Any assisance will be greatly appreciated.
> Thanks,
> Chuck
>|||On 6 Jun, 20:18, "Kalen Delaney" <replies@.public_newsgroups.com>
wrote:
> Hi Charles
> Please always let us know what version you are using.
> The reason you are not finding a way to do this is that it is not possible.
> Where did you find that "Microsoft" shows ALTER COLUMN to support changing a
> column to IDENTITY? You can add a new column with the IDENTITY property, but
> you can't add this property to an existing column. If it's actually in the
> BOL, it is a bug.
>
Kalen,
It is in BOL but it's not a mistake, it's just that it applies to
Compact Edition only. I'd guess that Charles is using some other
edition and didn't notice the title at the top of the page:
http://msdn2.microsoft.com/en-us/library/ms174123.aspx
http://msdn2.microsoft.com/en-us/library/ms190273.aspx
--
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
--|||Hello,
This is the link to the web site:
http://msdn2.microsoft.com/en-us/library/ms174123.aspx
I am using SQL Express 2005. I guess I could just drop the column and
recreate it as an identity column. The data in the ID column can be
dropped, providing it is replaced with unique numbers.
If I drop the column and recreate it, it places the column at the end of the
table. I know this might seem like a stupid question, but can I add the
column and have it move to the first position?
Thanks,
Chuck
"Charles A. Lackman" <Charles@.CreateItSoftware.net> wrote in message
news:u%23LgqyGqHHA.588@.TK2MSFTNGP06.phx.gbl...
Hello,
I have done a lot of searching on the internet to change an "ID [int] Not
Null" to an "ID [int] Identity (1,1) Not Null". Every example I have found
does not work. I get an error at 'Identity'. I need to send a single query
against the table (Alter Table Customers) to alter the Column ID to an
identity. I am using VB.NET and a Command Object to send the query - If I
can get it to work in SQL Server Express Manager, I can get it to work in my
program.
This is what Microsoft says but does not work:
ALTER TABLE table_name
{ [ ALTER COLUMN column_name
{DROP DEFAULT
| SET DEFAULT constant_expression
| IDENTITY [ ( seed , increment ) ]
}
* ALTER TABLE Customers
ALTER COLUMN ID IDENTITY(1,1) Not Null
Any assisance will be greatly appreciated.
Thanks,
Chuck|||> This is the link to the web site:
> http://msdn2.microsoft.com/en-us/library/ms174123.aspx
And as David pointed out, you are looking in the wrong edition of Books
Online. Notice at the top of the page:
SQL Server 2005 Compact Edition Books Online
SQL Server Compact Edition Programming
SQL Reference (SQL Server Compact Edition)
SQL Server 2005 Compact Edition Books Online
ALTER TABLE (SQL Server Compact Edition)
It's quite unfortunate that the syntax for ALTER TABLE is different, but
it's also unfortunate that it would be hard to make it even more clear that
this topic is meant for compact edition only. :-(
> If I drop the column and recreate it, it places the column at the end of
> the
> table. I know this might seem like a stupid question, but can I add the
> column and have it move to the first position?
Column order should not really matter. If you really want it that way, then
drop the table and re-create it. (You can create a new table with a new
name and an IDENTITY column, populate the other columns from the existing
data, drop the old table, and re-name the new table.)
--
Aaron Bertrand
SQL Server MVP
http://www.sqlblog.com/
http://www.aspfaq.com/5006|||Thanks, David. I guess it's important for posters to tell us both version
and edition!
--
HTH
Kalen Delaney, SQL Server MVP
www.InsideSQLServer.com
http://sqlblog.com
"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
news:1181158271.703889.92780@.z28g2000prd.googlegroups.com...
> On 6 Jun, 20:18, "Kalen Delaney" <replies@.public_newsgroups.com>
> wrote:
>> Hi Charles
>> Please always let us know what version you are using.
>> The reason you are not finding a way to do this is that it is not
>> possible.
>> Where did you find that "Microsoft" shows ALTER COLUMN to support
>> changing a
>> column to IDENTITY? You can add a new column with the IDENTITY property,
>> but
>> you can't add this property to an existing column. If it's actually in
>> the
>> BOL, it is a bug.
> Kalen,
> It is in BOL but it's not a mistake, it's just that it applies to
> Compact Edition only. I'd guess that Charles is using some other
> edition and didn't notice the title at the top of the page:
> http://msdn2.microsoft.com/en-us/library/ms174123.aspx
> http://msdn2.microsoft.com/en-us/library/ms190273.aspx
> --
> 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
> --
>
>|||On 6 Jun, 20:31, "Charles A. Lackman" <Char...@.CreateItSoftware.net>
wrote:
> Hello,
> This is the link to the web site:http://msdn2.microsoft.com/en-us/library/ms174123.aspx
> I am using SQL Express 2005. I guess I could just drop the column and
> recreate it as an identity column. The data in the ID column can be
> dropped, providing it is replaced with unique numbers.
> If I drop the column and recreate it, it places the column at the end of the
> table. I know this might seem like a stupid question, but can I add the
> column and have it move to the first position?
>
You'd have to recreate the entire table to do that. At runtime the
column order should be defined by a SELECT statement not by the order
the columns were created in, so for most purposes it shouldn't be a
problem to put the column at the end. It may cause some inconvenience
during support and development however.
--
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
--

Identity

Hello,
I have done a lot of searching on the internet to change an "ID [int] No
t
Null" to an "ID [int] Identity (1,1) Not Null". Every example I have fo
und
does not work. I get an error at 'Identity'. I need to send a single query
against the table (Alter Table Customers) to alter the Column ID to an
identity. I am using VB.NET and a Command Object to send the query - If I
can get it to work in SQL Server Express Manager, I can get it to work in my
program.
This is what Microsoft says but does not work:
ALTER TABLE table_name
{ [ ALTER COLUMN column_name
{DROP DEFAULT
| SET DEFAULT constant_expression
| IDENTITY [ ( seed , increment ) ]
}
* ALTER TABLE Customers
ALTER COLUMN ID IDENTITY(1,1) Not Null
Any assisance will be greatly appreciated.
Thanks,
ChuckHi Charles
Please always let us know what version you are using.
The reason you are not finding a way to do this is that it is not possible.
Where did you find that "Microsoft" shows ALTER COLUMN to support changing a
column to IDENTITY? You can add a new column with the IDENTITY property, but
you can't add this property to an existing column. If it's actually in the
BOL, it is a bug.
If you could change a column to have the IDENTITY property, what would
expect SQL Server to do with the existing values in that column? Let us
know, and we can probably come up with a workaround, but probably not a
single statement workaround.
HTH
Kalen Delaney, SQL Server MVP
www.InsideSQLServer.com
http://sqlblog.com
"Charles A. Lackman" <Charles@.CreateItSoftware.net> wrote in message
news:u%23LgqyGqHHA.588@.TK2MSFTNGP06.phx.gbl...
> Hello,
> I have done a lot of searching on the internet to change an "ID [int]
Not
> Null" to an "ID [int] Identity (1,1) Not Null". Every example I have
> found
> does not work. I get an error at 'Identity'. I need to send a single
> query
> against the table (Alter Table Customers) to alter the Column ID to an
> identity. I am using VB.NET and a Command Object to send the query - If I
> can get it to work in SQL Server Express Manager, I can get it to work in
> my
> program.
> This is what Microsoft says but does not work:
> ALTER TABLE table_name
> { [ ALTER COLUMN column_name
> {DROP DEFAULT
> | SET DEFAULT constant_expression
> | IDENTITY [ ( seed , increment ) ]
> }
> * ALTER TABLE Customers
> ALTER COLUMN ID IDENTITY(1,1) Not Null
> Any assisance will be greatly appreciated.
> Thanks,
> Chuck
>|||Can you simply build a new table and move in the existing data? Use set
identity_insert ... on/off for this when inserting the data.
TheSQLGuru
President
Indicium Resources, Inc.
"Charles A. Lackman" <Charles@.CreateItSoftware.net> wrote in message
news:u%23LgqyGqHHA.588@.TK2MSFTNGP06.phx.gbl...
> Hello,
> I have done a lot of searching on the internet to change an "ID [int]
Not
> Null" to an "ID [int] Identity (1,1) Not Null". Every example I have
> found
> does not work. I get an error at 'Identity'. I need to send a single
> query
> against the table (Alter Table Customers) to alter the Column ID to an
> identity. I am using VB.NET and a Command Object to send the query - If I
> can get it to work in SQL Server Express Manager, I can get it to work in
> my
> program.
> This is what Microsoft says but does not work:
> ALTER TABLE table_name
> { [ ALTER COLUMN column_name
> {DROP DEFAULT
> | SET DEFAULT constant_expression
> | IDENTITY [ ( seed , increment ) ]
> }
> * ALTER TABLE Customers
> ALTER COLUMN ID IDENTITY(1,1) Not Null
> Any assisance will be greatly appreciated.
> Thanks,
> Chuck
>|||On 6 Jun, 20:18, "Kalen Delaney" <replies@.public_newsgroups.com>
wrote:
> Hi Charles
> Please always let us know what version you are using.
> The reason you are not finding a way to do this is that it is not possible
.
> Where did you find that "Microsoft" shows ALTER COLUMN to support changing
a
> column to IDENTITY? You can add a new column with the IDENTITY property, b
ut
> you can't add this property to an existing column. If it's actually in the
> BOL, it is a bug.
>
Kalen,
It is in BOL but it's not a mistake, it's just that it applies to
Compact Edition only. I'd guess that Charles is using some other
edition and didn't notice the title at the top of the page:
http://msdn2.microsoft.com/en-us/library/ms174123.aspx
http://msdn2.microsoft.com/en-us/library/ms190273.aspx
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
--|||Hello,
This is the link to the web site:
http://msdn2.microsoft.com/en-us/library/ms174123.aspx
I am using SQL Express 2005. I guess I could just drop the column and
recreate it as an identity column. The data in the ID column can be
dropped, providing it is replaced with unique numbers.
If I drop the column and recreate it, it places the column at the end of the
table. I know this might seem like a stupid question, but can I add the
column and have it move to the first position?
Thanks,
Chuck
"Charles A. Lackman" <Charles@.CreateItSoftware.net> wrote in message
news:u%23LgqyGqHHA.588@.TK2MSFTNGP06.phx.gbl...
Hello,
I have done a lot of searching on the internet to change an "ID [int] No
t
Null" to an "ID [int] Identity (1,1) Not Null". Every example I have fo
und
does not work. I get an error at 'Identity'. I need to send a single query
against the table (Alter Table Customers) to alter the Column ID to an
identity. I am using VB.NET and a Command Object to send the query - If I
can get it to work in SQL Server Express Manager, I can get it to work in my
program.
This is what Microsoft says but does not work:
ALTER TABLE table_name
{ [ ALTER COLUMN column_name
{DROP DEFAULT
| SET DEFAULT constant_expression
| IDENTITY [ ( seed , increment ) ]
}
* ALTER TABLE Customers
ALTER COLUMN ID IDENTITY(1,1) Not Null
Any assisance will be greatly appreciated.
Thanks,
Chuck|||> This is the link to the web site:
> http://msdn2.microsoft.com/en-us/library/ms174123.aspx
And as David pointed out, you are looking in the wrong edition of Books
Online. Notice at the top of the page:
SQL Server 2005 Compact Edition Books Online
SQL Server Compact Edition Programming
SQL Reference (SQL Server Compact Edition)
SQL Server 2005 Compact Edition Books Online
ALTER TABLE (SQL Server Compact Edition)
It's quite unfortunate that the syntax for ALTER TABLE is different, but
it's also unfortunate that it would be hard to make it even more clear that
this topic is meant for compact edition only. :-(

> If I drop the column and recreate it, it places the column at the end of
> the
> table. I know this might seem like a stupid question, but can I add the
> column and have it move to the first position?
Column order should not really matter. If you really want it that way, then
drop the table and re-create it. (You can create a new table with a new
name and an IDENTITY column, populate the other columns from the existing
data, drop the old table, and re-name the new table.)
Aaron Bertrand
SQL Server MVP
http://www.sqlblog.com/
http://www.aspfaq.com/5006|||Thanks, David. I guess it's important for posters to tell us both version
and edition!
HTH
Kalen Delaney, SQL Server MVP
www.InsideSQLServer.com
http://sqlblog.com
"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
news:1181158271.703889.92780@.z28g2000prd.googlegroups.com...
> On 6 Jun, 20:18, "Kalen Delaney" <replies@.public_newsgroups.com>
> wrote:
> Kalen,
> It is in BOL but it's not a mistake, it's just that it applies to
> Compact Edition only. I'd guess that Charles is using some other
> edition and didn't notice the title at the top of the page:
> http://msdn2.microsoft.com/en-us/library/ms174123.aspx
> http://msdn2.microsoft.com/en-us/library/ms190273.aspx
> --
> 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
> --
>
>|||On 6 Jun, 20:31, "Charles A. Lackman" <Char...@.CreateItSoftware.net>
wrote:
> Hello,
> This is the link to the web site:http://msdn2.microsoft.com/en-us/li...
23.aspx
> I am using SQL Express 2005. I guess I could just drop the column and
> recreate it as an identity column. The data in the ID column can be
> dropped, providing it is replaced with unique numbers.
> If I drop the column and recreate it, it places the column at the end of t
he
> table. I know this might seem like a stupid question, but can I add the
> column and have it move to the first position?
>
You'd have to recreate the entire table to do that. At runtime the
column order should be defined by a SELECT statement not by the order
the columns were created in, so for most purposes it shouldn't be a
problem to put the column at the end. It may cause some inconvenience
during support and development however.
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
--|||Hi,
I had the exact same problem, and was very glad to find an easy solution
with a working example here:
http://decipherinfosys.wordpress.co...a-regular-colum
n-to-be-an-identity-column-in-sql-server/
In short, you won't need to drop and recreate the entire table, only the
primary key constraint and then the column. By switching SET
IDENTITY_INSERT on and off, you can retain the original values of the
primary key column.
But just read the article, it makes everything quite clear.
Good luck!
Dennis
*** Sent via Developersdex http://www.codecomments.com ***