How to make tables & stored procedures in MSDE?

Hello, I am a beginner using MSDE & Visual Basic.NET standard version (not Visual Studio.NET) . MSDE was downloaded from microsoft.com yesterday, which is sql2ksp3.exe.

The problem is I don't know where to input data to set up tables or stored procedures after configured the MSDE. The MS Desktop Engine pops me "configuration sucessfully", then I can just open a small window with the title of "SQL Server Service Manager", telling me SQL Server is running. But I can't find any SQL application in the Start->All Programs-> for me to run.

What's the human interface of MSDE? Can anybody tell me how you deal with MSDE?

And in my VB.NET's Server Explorer, I find there is only "Data connections" icon which allows me to connect to existing database, and no "Servers" icon as showed in many Visual Studio instruction books, which make me no chance to create new database.

My Windows system is XP professional (SP2) with IIS, .NET framework 1.1 and Server Extensions Adminstrator. What's my problem? Did I configure wrong somewhere? I want to make my computer as a local server. And does the combination of VB.NET + MSDE work?

Thanks a lot for your help! Have a good weekend!

Henry Li

There is no program that comes with msde for managing and creating databases. You can use visual studio 2003's server explorer for that. Here is an example of how to create a database with vb.net

http://www.vb-tips.com/default.aspx?ID=73eab21d-db5f-46b2-8eea-6680e677e994

|||Hello Ken,
Thanks a lot for your quick response, it's the first time for me to join this forum, and first time to get response.

Do you mean I can only use codes to generate database tables and stored procedures? Are all these tables located under the MYPC.master.dbo icon in the Server Explorer? Is there any simpler way that I just right-click something then it gives me an option to "New tables" or "New Database"? (But I don't have the Servers icon in the Server Explorer, did I configure wrong for my VB.NET package?)

And does the MSDE have interface window like Access that can give you a straight-forward and nice looking window to input table data? Can MSDE work independantly without VS.NET's help? I really have no idea how MSDE itself look like. By the way, does SQL Server Express have windows and menus to manipulate data?

I hope my questions are not too many. Thanks a lot in advance for your help!

Best wishes,
Henry Li|||Hi,
You can create databases, tables, etc in visual studio 2003 server explorer also. Right click on databases and select new database. The Sql Server Express is the free 2005 version of msde. You can download a management console for the express edition.
Ken|||

Dear Ken,
You don't really understand my problems yet.
I am using VB.NET package, not VS.NET. The interface and menu are similar, but when I did a help search of "Server Explorer" within it, it told me VB.NET doesn't have the Server node within the Server Explorer, only VS.NET package has it. I just have the first node, which is "Data Connection".

I ran your code in a new project "test1". I pasted it on the WebForms1.aspx.vb, then built it. It generated errors as below:

Build started: Project: test1, Configuration: Debug .NET

Preparing resources...
Updating references...
Performing main compilation...
c:\inetpub\wwwroot\test1\WebForm1.aspx.vb(24) : error BC30002: Type 'SqlConnection' is not defined.
c:\inetpub\wwwroot\test1\WebForm1.aspx.vb(26) : error BC30182: Type expected.
c:\inetpub\wwwroot\test1\WebForm1.aspx.vb(27) : error BC30691: 'EventArgs' is a type in 'System' and cannot be used as an expression.
c:\inetpub\wwwroot\test1\WebForm1.aspx.vb(27) : error BC30800: Method arguments must be enclosed in parentheses.
c:\inetpub\wwwroot\test1\WebForm1.aspx.vb(27) : error BC30205: End of statement expected.
c:\inetpub\wwwroot\test1\WebForm1.aspx.vb(33) : error BC30002: Type 'SqlConnection' is not defined.
c:\inetpub\wwwroot\test1\WebForm1.aspx.vb(48) : error BC30002: Type 'SqlCommand' is not defined.
c:\inetpub\wwwroot\test1\WebForm1.aspx.vb(54) : error BC30451: Name 'MessageBox' is not declared.
c:\inetpub\wwwroot\test1\WebForm1.aspx.vb(61) : error BC30456: 'Text' is not a member of 'test1.WebForm1'.
c:\inetpub\wwwroot\test1\WebForm1.aspx.vb(88) : error BC30002: Type 'SqlCommand' is not defined.
c:\inetpub\wwwroot\test1\WebForm1.aspx.vb(92) : error BC30002: Type 'SqlException' is not defined.
c:\inetpub\wwwroot\test1\WebForm1.aspx.vb(93) : error BC30451: Name 'MessageBox' is not declared.
c:\inetpub\wwwroot\test1\WebForm1.aspx.vb(113) : error BC30002: Type 'SqlCommand' is not defined.
c:\inetpub\wwwroot\test1\WebForm1.aspx.vb(123) : error BC30002: Type 'SqlException' is not defined.
c:\inetpub\wwwroot\test1\WebForm1.aspx.vb(124) : error BC30451: Name 'MessageBox' is not declared.
Building satellite assemblies...
Satellite assemblies could not be built because the main project output is missing.

- Done -

Build: 0 succeeded, 1 failed, 0 skipped
I am so dissapointed with VB.NET standard edition, as it seems it can't generate new database, it can just connect to existing database that are already built up.
So I am curious whether MSDE can work independently to let me set up new database, but you ignored my question. I really feel depressed about this combination of VB.NET + MSDE.
You may search on your VS.NET help function for "Server Explorer", it will tell you VB.NET package doesn't have server node.

Thanks & Best regards,
Henry Li

|||Hello Ken,
Visual Basic.NET Standard Edition package is cheaper than VS.NET package. It has less functions than VS.NET package, and cost me $114.
On its package box it mentions it can't build device-based applications and solutions, can't build/debug/deploy powerful server software while VS.NET Professional can.

You said SQL Server Express 2005 is a new version of MSDE, I am just wondering whether it can work like Oracle Plus or Access which can let you have some windows to key in some data?

Thanks a lot!
Henry|||You need a imports system.data.sqlclient at the top of the code file.|||Sorry I thought vb.net standard had the server explorer. Here is a link to download the free sql server 2005 express.

http://msdn.microsoft.com/vstudio/express/sql/

Here is a link to the free management studio for sql server 2005 express

http://www.microsoft.com/downloads/details.aspx?FamilyId=82AFBD59-57A4-455E-A2D6-1D4C98D40F6E&displaylang=en|||Hello Ken,
Thanks a lot! This "imports system.data.sqlclient" really helps!
Now it reduces to only 2 errors left, which are:
--
c:\inetpub\wwwroot\test1\WebForm1.aspx.vb(54) : error BC30451: Name 'MessageBox' is not declared. (under the sub createDataBase()->Try/Catch)
c:\inetpub\wwwroot\test1\WebForm1.aspx.vb(61) : error BC30456: 'Text' is not a member of 'test1.WebForm1'. (under the sub CreateClientsTable() ->me.Text)
--

I think I might need another line of "imports something"?
Sorry I am a beginner of programming in sql. And want to try your code to see if it can pass through to connect to my MSDE.

By the way, I've downloaded the SQL Server Express and the management console but haven't installed them, and still downloading the 440MB Studio Web Develop Express under the Visual Studio 2005 Express which was issued for free download just 2 weeks ago. I hope this can take place of my VB.NET package.

Thanks a lot again!

Best regards,
Henry|||I wrote that as windows forms application. Messagebox.show is not supported in web application's neither is me.text. Try changing messagebox.show to response.write and me.text = to response.write("The string" & "<br>")|||Hi, I am new one in this forum. About question: "How to make tables & stored procedures in MSDE?", try with command line tool - osql. If you need more help, I am here. Or search MS site about it.

Stevan
|||Hello Ken,
I've downloaded the Visual Web Developer 2005 Express which includes the SQL Server Express, so I installed them both.
The SQL Server 2005 Express is similar to the MSDE, although it has a configuration console, it still can't handle database.
Then I downloaded the SQL Management Studio Express, it's really good! It gives me free choices to deal with security, log-in, creation of new databases!

Then I created a database in the SQLEXPRESS via the management studio console, then in the Visual Web Dev Express I created connection with that new database, it connects!

Now I can creat tables and stored procedures either directly in the SQLEXPRESS's database (via the mgt studio), or indirectly in the Visual Web Dev Express within the database explorer window.

Now I don't use the VB.NET package anymore except if I need to create some windows exe files.

Thank you for your kindness!

Best wishes,
Henry Li|||Thank you Stevan,
I found my problem was some basic things that maybe many people took it for granted while I didn't know.
Actually I need to make a database in the MSDE first, and MSDE has no interface to let me set it up. I need the management software such as the SQL Management Studio 2005 Express.
Now I found this Mgt Studio Express is very useful, it can handle MSDE and SQLEXPRESS. I created some databases with the help of Mgt Studio Express, then I can create tables or procedures!

Best wishes,
Henry|||

Hi,

You can generate tables with osql command, i just need to indicate the command as following:

osql -U user -S server\instancename

then

1> create database test

2> GO

1>use test

2>GO

then you're inside of test database and you are ready to generate your table according you're needed

How to make tables & stored procedures in MSDE?

Hello, I am a beginner using MSDE & Visual Basic.NET standard version (not Visual Studio.NET) . MSDE was downloaded from microsoft.com yesterday, which is sql2ksp3.exe.

The problem is I don't know where to input data to set up tables or stored procedures after configured the MSDE. The MS Desktop Engine pops me "configuration sucessfully", then I can just open a small window with the title of "SQL Server Service Manager", telling me SQL Server is running. But I can't find any SQL application in the Start->All Programs-> for me to run.

What's the human interface of MSDE? Can anybody tell me how you deal with MSDE?

And in my VB.NET's Server Explorer, I find there is only "Data connections" icon which allows me to connect to existing database, and no "Servers" icon as showed in many Visual Studio instruction books, which make me no chance to create new database.

My Windows system is XP professional (SP2) with IIS, .NET framework 1.1 and Server Extensions Adminstrator. What's my problem? Did I configure wrong somewhere? I want to make my computer as a local server. And does the combination of VB.NET + MSDE work?

Thanks a lot for your help! Have a good weekend!

Henry Li

There is no program that comes with msde for managing and creating databases. You can use visual studio 2003's server explorer for that. Here is an example of how to create a database with vb.net

http://www.vb-tips.com/default.aspx?ID=73eab21d-db5f-46b2-8eea-6680e677e994

|||Hello Ken,
Thanks a lot for your quick response, it's the first time for me to join this forum, and first time to get response.

Do you mean I can only use codes to generate database tables and stored procedures? Are all these tables located under the MYPC.master.dbo icon in the Server Explorer? Is there any simpler way that I just right-click something then it gives me an option to "New tables" or "New Database"? (But I don't have the Servers icon in the Server Explorer, did I configure wrong for my VB.NET package?)

And does the MSDE have interface window like Access that can give you a straight-forward and nice looking window to input table data? Can MSDE work independantly without VS.NET's help? I really have no idea how MSDE itself look like. By the way, does SQL Server Express have windows and menus to manipulate data?

I hope my questions are not too many. Thanks a lot in advance for your help!

Best wishes,
Henry Li|||Hi,
You can create databases, tables, etc in visual studio 2003 server explorer also. Right click on databases and select new database. The Sql Server Express is the free 2005 version of msde. You can download a management console for the express edition.
Ken|||

Dear Ken,
You don't really understand my problems yet.
I am using VB.NET package, not VS.NET. The interface and menu are similar, but when I did a help search of "Server Explorer" within it, it told me VB.NET doesn't have the Server node within the Server Explorer, only VS.NET package has it. I just have the first node, which is "Data Connection".

I ran your code in a new project "test1". I pasted it on the WebForms1.aspx.vb, then built it. It generated errors as below:

Build started: Project: test1, Configuration: Debug .NET

Preparing resources...
Updating references...
Performing main compilation...
c:\inetpub\wwwroot\test1\WebForm1.aspx.vb(24) : error BC30002: Type 'SqlConnection' is not defined.
c:\inetpub\wwwroot\test1\WebForm1.aspx.vb(26) : error BC30182: Type expected.
c:\inetpub\wwwroot\test1\WebForm1.aspx.vb(27) : error BC30691: 'EventArgs' is a type in 'System' and cannot be used as an expression.
c:\inetpub\wwwroot\test1\WebForm1.aspx.vb(27) : error BC30800: Method arguments must be enclosed in parentheses.
c:\inetpub\wwwroot\test1\WebForm1.aspx.vb(27) : error BC30205: End of statement expected.
c:\inetpub\wwwroot\test1\WebForm1.aspx.vb(33) : error BC30002: Type 'SqlConnection' is not defined.
c:\inetpub\wwwroot\test1\WebForm1.aspx.vb(48) : error BC30002: Type 'SqlCommand' is not defined.
c:\inetpub\wwwroot\test1\WebForm1.aspx.vb(54) : error BC30451: Name 'MessageBox' is not declared.
c:\inetpub\wwwroot\test1\WebForm1.aspx.vb(61) : error BC30456: 'Text' is not a member of 'test1.WebForm1'.
c:\inetpub\wwwroot\test1\WebForm1.aspx.vb(88) : error BC30002: Type 'SqlCommand' is not defined.
c:\inetpub\wwwroot\test1\WebForm1.aspx.vb(92) : error BC30002: Type 'SqlException' is not defined.
c:\inetpub\wwwroot\test1\WebForm1.aspx.vb(93) : error BC30451: Name 'MessageBox' is not declared.
c:\inetpub\wwwroot\test1\WebForm1.aspx.vb(113) : error BC30002: Type 'SqlCommand' is not defined.
c:\inetpub\wwwroot\test1\WebForm1.aspx.vb(123) : error BC30002: Type 'SqlException' is not defined.
c:\inetpub\wwwroot\test1\WebForm1.aspx.vb(124) : error BC30451: Name 'MessageBox' is not declared.
Building satellite assemblies...
Satellite assemblies could not be built because the main project output is missing.

- Done -

Build: 0 succeeded, 1 failed, 0 skipped
I am so dissapointed with VB.NET standard edition, as it seems it can't generate new database, it can just connect to existing database that are already built up.
So I am curious whether MSDE can work independently to let me set up new database, but you ignored my question. I really feel depressed about this combination of VB.NET + MSDE.
You may search on your VS.NET help function for "Server Explorer", it will tell you VB.NET package doesn't have server node.

Thanks & Best regards,
Henry Li

|||Hello Ken,
Visual Basic.NET Standard Edition package is cheaper than VS.NET package. It has less functions than VS.NET package, and cost me $114.
On its package box it mentions it can't build device-based applications and solutions, can't build/debug/deploy powerful server software while VS.NET Professional can.

You said SQL Server Express 2005 is a new version of MSDE, I am just wondering whether it can work like Oracle Plus or Access which can let you have some windows to key in some data?

Thanks a lot!
Henry|||You need a imports system.data.sqlclient at the top of the code file.|||Sorry I thought vb.net standard had the server explorer. Here is a link to download the free sql server 2005 express.

http://msdn.microsoft.com/vstudio/express/sql/

Here is a link to the free management studio for sql server 2005 express

http://www.microsoft.com/downloads/details.aspx?FamilyId=82AFBD59-57A4-455E-A2D6-1D4C98D40F6E&displaylang=en|||Hello Ken,
Thanks a lot! This "imports system.data.sqlclient" really helps!
Now it reduces to only 2 errors left, which are:
--
c:\inetpub\wwwroot\test1\WebForm1.aspx.vb(54) : error BC30451: Name 'MessageBox' is not declared. (under the sub createDataBase()->Try/Catch)
c:\inetpub\wwwroot\test1\WebForm1.aspx.vb(61) : error BC30456: 'Text' is not a member of 'test1.WebForm1'. (under the sub CreateClientsTable() ->me.Text)
--

I think I might need another line of "imports something"?
Sorry I am a beginner of programming in sql. And want to try your code to see if it can pass through to connect to my MSDE.

By the way, I've downloaded the SQL Server Express and the management console but haven't installed them, and still downloading the 440MB Studio Web Develop Express under the Visual Studio 2005 Express which was issued for free download just 2 weeks ago. I hope this can take place of my VB.NET package.

Thanks a lot again!

Best regards,
Henry|||I wrote that as windows forms application. Messagebox.show is not supported in web application's neither is me.text. Try changing messagebox.show to response.write and me.text = to response.write("The string" & "<br>")|||Hi, I am new one in this forum. About question: "How to make tables & stored procedures in MSDE?", try with command line tool - osql. If you need more help, I am here. Or search MS site about it.

Stevan|||Hello Ken,
I've downloaded the Visual Web Developer 2005 Express which includes the SQL Server Express, so I installed them both.
The SQL Server 2005 Express is similar to the MSDE, although it has a configuration console, it still can't handle database.
Then I downloaded the SQL Management Studio Express, it's really good! It gives me free choices to deal with security, log-in, creation of new databases!

Then I created a database in the SQLEXPRESS via the management studio console, then in the Visual Web Dev Express I created connection with that new database, it connects!

Now I can creat tables and stored procedures either directly in the SQLEXPRESS's database (via the mgt studio), or indirectly in the Visual Web Dev Express within the database explorer window.

Now I don't use the VB.NET package anymore except if I need to create some windows exe files.

Thank you for your kindness!

Best wishes,
Henry Li|||Thank you Stevan,
I found my problem was some basic things that maybe many people took it for granted while I didn't know.
Actually I need to make a database in the MSDE first, and MSDE has no interface to let me set it up. I need the management software such as the SQL Management Studio 2005 Express.
Now I found this Mgt Studio Express is very useful, it can handle MSDE and SQLEXPRESS. I created some databases with the help of Mgt Studio Express, then I can create tables or procedures!

Best wishes,
Henry|||

Hi,

You can generate tables with osql command, i just need to indicate the command as following:

osql -U user -S server\instancename

then

1> create database test

2> GO

1>use test

2>GO

then you're inside of test database and you are ready to generate your table according you're needed

how to make an alias here ?

Hi experts,
i am working with SQL-Server 2000 and have a special problem in one of my
stored procedures. To be as fast as possible i use a temp table defined as
variable. And i want to calculate a value in this table depending on values
in the same table. But i can't make it work. Here my problem:
-- can be copied into QueryAnalizer --
declare @.tbl table (
id1 int,
id2 int,
w1 int,
w2 int )
insert into @.tbl values (1, 2, 12, null)
insert into @.tbl values (2, 3, 10, null)
insert into @.tbl values (3, null, 7, null)
-- this is my wish. It doesn't work, because alias t1 is not valid, but i
think you can see what i want to do:
-- update @.tbl t1 set t1.w2 = t1.w1 - IsNull((select IsNull(t2.w1,0) from
@.tbl t2 where t2.id1 = t1.id2),0)
-- and this i all i can do, but with wrong result:
update @.tbl set w2 = w1 - IsNull((select IsNull(w1,0) from @.tbl t2 where
t2.id1 = id2),0)
select * from @.tbl
----
gives me:
1 2 12 12
2 3 10 10
3 NULL 7 7
and this is what it want to have:
1 2 12 2
2 3 10 3
3 NULL 7 7
I think, the problem is because in ... where t2.id1 = id2) the value of id2
is not the value of the atually updating record, it stays on id2 of the
last insert, which means, id2 is always NULL. And i can't use an alias (why
is an alias here not possible? This is not clear to me). What can i do?
thanks,
Helmuthelmut woess wrote:
> Hi experts,
> i am working with SQL-Server 2000 and have a special problem in one
> of my stored procedures. To be as fast as possible i use a temp table
> defined as variable. And i want to calculate a value in this table
> depending on values in the same table. But i can't make it work. Here
> my problem:
> -- can be copied into QueryAnalizer --
> declare @.tbl table (
> id1 int,
> id2 int,
> w1 int,
> w2 int )
> insert into @.tbl values (1, 2, 12, null)
> insert into @.tbl values (2, 3, 10, null)
> insert into @.tbl values (3, null, 7, null)
> -- this is my wish. It doesn't work, because alias t1 is not valid,
> but i think you can see what i want to do:
> -- update @.tbl t1 set t1.w2 = t1.w1 - IsNull((select IsNull(t2.w1,0)
> from @.tbl t2 where t2.id1 = t1.id2),0)
>
update t1 set t1.w2 = t1.w1 - IsNull((select IsNull(t2.w1,0)
from @.tbl t2 where t2.id1 = t1.id2),0)
from @.tbl t1
Microsoft MVP -- ASP/ASP.NET
Please reply to the newsgroup. The email account listed in my From
header is my spam trap, so I don't check it very often. You will get a
quicker response by posting to the newsgroup.|||Am Thu, 15 Dec 2005 09:16:21 -0500 schrieb Bob Barrows [MVP]:

> helmut woess wrote:
> update t1 set t1.w2 = t1.w1 - IsNull((select IsNull(t2.w1,0)
> from @.tbl t2 where t2.id1 = t1.id2),0)
> from @.tbl t1
Incredible, how fast you came up with the solution!
thousand thanks, Bob, you saved me a lot of time!
Helmutsql

How to Make a sql procedure for this requirement

Hi,
I am a new comer to sql server. I could write simple procedures. I have
a table like this
Field1 Field2 Field3
1 one name-1
2 two name-2
1 one name-3
I have to write a procedure to return the values of the records for all the
records with Field1 equal to 1 in the format:
1,one,name-1,name-3
As in the above case two records match so I return total four values with
two name values. If three records would have matched I would have returned
three name values in the response, so in that case the total values to be
returned would have been 5.
I just wonder how I would write this procedure some thing like
create procedure dbo.getvalues input int, output1 int OUTPUT, output2
char(10) OUTPUT....
as
select Field1,Field2,Field3 ...
How can I declare a variable limit to output fileld ? I mean the output from
this procedure could be 4 fields 5 fields or 6 fileds or more ....
Thanks for any help in this matter.
JSJS
I'd strongly recommend you doing such reports on the client side. This
solution is not reliable.
CREATE TABLE #Test
(
col1 INT NOT NULL,
col2 CHAR(1) NOT NULL,
col3 CHAR(2) NOT NULL
)
INSERT INTO #Test VALUES (1,'A','BB')
INSERT INTO #Test VALUES (2,'G','DD')
INSERT INTO #Test VALUES (1,'A','CC')
DECLARE @.st VARCHAR(50)
SET @.st=''
SELECT @.st=@.st+ col2+','+col3 FROM #Test WHERE col1=1
SELECT @.st
"JS" <JS@.discussions.microsoft.com> wrote in message
news:A122B6DE-DD0D-49D6-99A1-A5D7F63933F0@.microsoft.com...
> Hi,
> I am a new comer to sql server. I could write simple procedures. I
> have
> a table like this
> Field1 Field2 Field3
> 1 one name-1
> 2 two name-2
> 1 one name-3
>
> I have to write a procedure to return the values of the records for all
> the
> records with Field1 equal to 1 in the format:
> 1,one,name-1,name-3
>
> As in the above case two records match so I return total four values with
> two name values. If three records would have matched I would have returned
> three name values in the response, so in that case the total values to be
> returned would have been 5.
> I just wonder how I would write this procedure some thing like
> create procedure dbo.getvalues input int, output1 int OUTPUT, output2
> char(10) OUTPUT....
> as
> select Field1,Field2,Field3 ...
> How can I declare a variable limit to output fileld ? I mean the output
> from
> this procedure could be 4 fields 5 fields or 6 fileds or more ....
> Thanks for any help in this matter.
> JS
>|||Maybe this might help:
http://milambda.blogspot.com/2005/0...s-as-array.html
And I strongly agree with Uri - this belongs on the presentation layer, not
the data layer.
MLsql

How to list (Export to txt) the Built-In and User Store Procedures

Good afternoon,
How can I export a list of Built-In and User Made Stored Procedures on an
SQL-2005 server ?
Regards,
Luis
Hi
How about querying sys.objects? If you want to do this manually save the
output from SSMS or use output option for SQLCMD, or use a query with BCP!
John
"gatox" wrote:

> Good afternoon,
> How can I export a list of Built-In and User Made Stored Procedures on an
> SQL-2005 server ?
> Regards,
> Luis

How to list (Export to txt) the Built-In and User Store Procedures

Good afternoon,
How can I export a list of Built-In and User Made Stored Procedures on an
SQL-2005 server ?
Regards,
LuisHi
How about querying sys.objects? If you want to do this manually save the
output from SSMS or use output option for SQLCMD, or use a query with BCP!
John
"gatox" wrote:
> Good afternoon,
> How can I export a list of Built-In and User Made Stored Procedures on an
> SQL-2005 server ?
> Regards,
> Luis

How to list (Export to txt) the Built-In and User Store Procedures

Good afternoon,
How can I export a list of Built-In and User Made Stored Procedures on an
SQL-2005 server ?
Regards,
LuisHi
How about querying sys.objects? If you want to do this manually save the
output from SSMS or use output option for SQLCMD, or use a query with BCP!
John
"gatox" wrote:

> Good afternoon,
> How can I export a list of Built-In and User Made Stored Procedures on an
> SQL-2005 server ?
> Regards,
> Luis

How to let a SP be shorter?

Hi,
The following is one of our stored procedures,
is it possible to make it shorter?
Thanks for help.
Jason
ALTER PROCEDURE UpdateCustomerBtoRoot
AS
SET nocount on
UPDATE Customer SET
CustCName = (SELECT CustCName FROM CustomerB WHERE
CustomerB.CustNo=Customer.CustNo),
CustEName =(SELECT CustEName FROM CustomerB WHERE
CustomerB.CustNo=Customer.CustNo),
AbbrName=(SELECT AbbrName FROM CustomerB WHERE
CustomerB.CustNo=Customer.CustNo),
ZipCode =(SELECT ZipCode FROM CustomerB WHERE
CustomerB.CustNo=Customer.CustNo),
Addr = (SELECT Addr FROM CustomerB WHERE
CustomerB.CustNo=Customer.CustNo),
EZipCode = (SELECT EZipCode FROM CustomerB WHERE
CustomerB.CustNo=Customer.CustNo),
BillZipCode = (SELECT BillZipCode FROM CustomerB WHERE
CustomerB.CustNo=Customer.CustNo),
BillAddr = (SELECT BillAddr FROM CustomerB WHERE
CustomerB.CustNo=Customer.CustNo),
Phone = (SELECT Phone FROM CustomerB WHERE
CustomerB.CustNo=Customer.CustNo),
Fax = (SELECT Fax FROM CustomerB WHERE
CustomerB.CustNo=Customer.CustNo),
Owner = (SELECT Owner FROM CustomerB WHERE
CustomerB.CustNo=Customer.CustNo),
OwnerTitle = (SELECT OwnerTitle FROM CustomerB WHERE
CustomerB.CustNo=Customer.CustNo),
HomePage = (SELECT HomePage FROM CustomerB WHERE
CustomerB.CustNo=Customer.CustNo),
FirstDate = (SELECT FirstDate FROM CustomerB WHERE
CustomerB.CustNo=Customer.CustNo),
DateUpDate = (SELECT DateUpDate FROM CustomerB WHERE
CustomerB.CustNo=Customer.CustNo),
Area = (SELECT Area FROM CustomerB WHERE
CustomerB.CustNo=Customer.CustNo),
WeightMeasure =(SELECT WeightMeasure FROM CustomerB WHERE
CustomerB.CustNo=Customer.CustNo)
WHERE EXISTS (SELECT * FROM
CustomerB WHERE CustomerB.CustNo=Customer.CustNo)
GO
Hi, Jason
Use something like this:
UPDATE Customer
SET CustCName=B.CustCName,
CustEName=B.CustEName,
AbbrName=B.AbbrName,
[...]
FROM Customer as C INNER JOIN CustomerB as B
ON C.CustNo=B.CustNo
In order to get the expected results, make sure that the CustNo is a
unique key or a primary key in both tables. Although easier to read,
this syntax is less portable than the original statement (the original
syntax was ANSI standard, but this is proprietary to Microsoft SQL
Server).
Razvan

How to let a SP be shorter?

Hi,
The following is one of our stored procedures,
is it possible to make it shorter?
Thanks for help.
Jason
ALTER PROCEDURE UpdateCustomerBtoRoot
AS
SET nocount on
UPDATE Customer SET
CustCName = (SELECT CustCName FROM CustomerB WHERE
CustomerB.CustNo=Customer.CustNo),
CustEName =(SELECT CustEName FROM CustomerB WHERE
CustomerB.CustNo=Customer.CustNo),
AbbrName=(SELECT AbbrName FROM CustomerB WHERE
CustomerB.CustNo=Customer.CustNo),
ZipCode =(SELECT ZipCode FROM CustomerB WHERE
CustomerB.CustNo=Customer.CustNo),
Addr = (SELECT Addr FROM CustomerB WHERE
CustomerB.CustNo=Customer.CustNo),
EZipCode = (SELECT EZipCode FROM CustomerB WHERE
CustomerB.CustNo=Customer.CustNo),
BillZipCode = (SELECT BillZipCode FROM CustomerB WHERE
CustomerB.CustNo=Customer.CustNo),
BillAddr = (SELECT BillAddr FROM CustomerB WHERE
CustomerB.CustNo=Customer.CustNo),
Phone = (SELECT Phone FROM CustomerB WHERE
CustomerB.CustNo=Customer.CustNo),
Fax = (SELECT Fax FROM CustomerB WHERE
CustomerB.CustNo=Customer.CustNo),
Owner = (SELECT Owner FROM CustomerB WHERE
CustomerB.CustNo=Customer.CustNo),
OwnerTitle = (SELECT OwnerTitle FROM CustomerB WHERE
CustomerB.CustNo=Customer.CustNo),
HomePage = (SELECT HomePage FROM CustomerB WHERE
CustomerB.CustNo=Customer.CustNo),
FirstDate = (SELECT FirstDate FROM CustomerB WHERE
CustomerB.CustNo=Customer.CustNo),
DateUpDate = (SELECT DateUpDate FROM CustomerB WHERE
CustomerB.CustNo=Customer.CustNo),
Area = (SELECT Area FROM CustomerB WHERE
CustomerB.CustNo=Customer.CustNo),
WeightMeasure =(SELECT WeightMeasure FROM CustomerB WHERE
CustomerB.CustNo=Customer.CustNo)
WHERE EXISTS (SELECT * FROM
CustomerB WHERE CustomerB.CustNo=Customer.CustNo)
GOHi, Jason
Use something like this:
UPDATE Customer
SET CustCName=B.CustCName,
CustEName=B.CustEName,
AbbrName=B.AbbrName,
[...]
FROM Customer as C INNER JOIN CustomerB as B
ON C.CustNo=B.CustNo
In order to get the expected results, make sure that the CustNo is a
unique key or a primary key in both tables. Although easier to read,
this syntax is less portable than the original statement (the original
syntax was ANSI standard, but this is proprietary to Microsoft SQL
Server).
Razvan

How to let a SP be shorter?

Hi,
The following is one of our stored procedures,
is it possible to make it shorter?
Thanks for help.
Jason
ALTER PROCEDURE UpdateCustomerBtoRoot
AS
SET nocount on
UPDATE Customer SET
CustCName = (SELECT CustCName FROM CustomerB WHERE
CustomerB.CustNo=Customer.CustNo),
CustEName =(SELECT CustEName FROM CustomerB WHERE
CustomerB.CustNo=Customer.CustNo),
AbbrName=(SELECT AbbrName FROM CustomerB WHERE
CustomerB.CustNo=Customer.CustNo),
ZipCode =(SELECT ZipCode FROM CustomerB WHERE
CustomerB.CustNo=Customer.CustNo),
Addr = (SELECT Addr FROM CustomerB WHERE
CustomerB.CustNo=Customer.CustNo),
EZipCode = (SELECT EZipCode FROM CustomerB WHERE
CustomerB.CustNo=Customer.CustNo),
BillZipCode = (SELECT BillZipCode FROM CustomerB WHERE
CustomerB.CustNo=Customer.CustNo),
BillAddr = (SELECT BillAddr FROM CustomerB WHERE
CustomerB.CustNo=Customer.CustNo),
Phone = (SELECT Phone FROM CustomerB WHERE
CustomerB.CustNo=Customer.CustNo),
Fax = (SELECT Fax FROM CustomerB WHERE
CustomerB.CustNo=Customer.CustNo),
Owner = (SELECT Owner FROM CustomerB WHERE
CustomerB.CustNo=Customer.CustNo),
OwnerTitle = (SELECT OwnerTitle FROM CustomerB WHERE
CustomerB.CustNo=Customer.CustNo),
HomePage = (SELECT HomePage FROM CustomerB WHERE
CustomerB.CustNo=Customer.CustNo),
FirstDate = (SELECT FirstDate FROM CustomerB WHERE
CustomerB.CustNo=Customer.CustNo),
DateUpDate = (SELECT DateUpDate FROM CustomerB WHERE
CustomerB.CustNo=Customer.CustNo),
Area = (SELECT Area FROM CustomerB WHERE
CustomerB.CustNo=Customer.CustNo),
WeightMeasure =(SELECT WeightMeasure FROM CustomerB WHERE
CustomerB.CustNo=Customer.CustNo)
WHERE EXISTS (SELECT * FROM
CustomerB WHERE CustomerB.CustNo=Customer.CustNo)
GOHi, Jason
Use something like this:
UPDATE Customer
SET CustCName=B.CustCName,
CustEName=B.CustEName,
AbbrName=B.AbbrName,
[...]
FROM Customer as C INNER JOIN CustomerB as B
ON C.CustNo=B.CustNo
In order to get the expected results, make sure that the CustNo is a
unique key or a primary key in both tables. Although easier to read,
this syntax is less portable than the original statement (the original
syntax was ANSI standard, but this is proprietary to Microsoft SQL
Server).
Razvan