Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
prettypixie
Starting Member
8 Posts |
 Posted - 2007-04-21 : 14:18:31
|
| Hi All,I am very new to sql and I am not really sure of what I am doing. I have a bit of a complicated stored procedure that I am trying out. There is 3 parts to it to search for new articles,updates and discontinued articles for a particular Supplier. Right now I am just trying to get the first part working - I am trying to query another database that has 4 tables, 2 are joined ActiveArticle & ActiveArticlePrice and the other 2 are also joined UpdateArticle & UpdateArticlePrice. But all tables also hold data for ALL Suppliers, they are recognised by a GUID - SupplierGuid.I want to compare them to each other to see if there are any new articles added to the Update tables. This is what I have so far, I was hoping someone could tell me if they see some obvious mistakes(I'm sure theres many!). For the first select, I tried adding a couple of parameters Flag and Text to use so that I can order it all once i have the 3 parts working. But I don't know the syntax is right, because then I also want to select all fields in both tables?.CREATE PROCEDURE Portal_GetPriceListUpdates( @SupplierGUID uniqueidentifier)ASSELECT 'A' AS FLAG, 'New Product' AS TEXT, * FROM SupplierUnits.UpdateArticle UA, SupplierUnits.UpdateArticlePrice UAP WHERE EXISTS (SELECT * FROM UA, UAP WHERE UA.SupplierGuid=@SupplierGUID AND UA.SupplierArticleNumber NOT IN (SELECT SupplierArticleNumber FROM SupplierUnits.ActiveArticle.SupplierArticleNumber))GOI would appreciate any help!Thanks,pp |
|
|
herothecat
Starting Member
19 Posts |
Posted - 2007-04-21 : 19:10:07
|
| Can you post the DDL (the create scripts or the column names and datatypes) for the tables?Also, I'm assuming your database name is SupplierUnits?Are you on 2000 or 2005?Why push the envelope when you can just open it? |
 |
|
|
rmiao
Master Smack Fu Yak Hacker
7266 Posts |
Posted - 2007-04-21 : 20:20:00
|
| You should reference tables in other db with db_name.schema.obj_name, like SupplierUnits.schema.UpdateArticle or SupplierUnits..UpdateArticle if schema is dbo. |
|
|
|
|
|
|
|
|
querying other databases