SQL SPs With Conditionals Based on Parameters - Database Discussion Boards

Venkatraman11-Aug-03 2:38

11-Aug-03 2:38

Hey

How can i run a query which can be an insert/update query from one database referring another database.

For example

i need to insert a row in my database1.table1 from database2

thanks

Cheers,
Venkatraman Kalyanam
Bangalore - India

"Being Excellent is not a skill, it is an attitude"

Reality is an illusion caused by caffeine deficiency(one Microsoft Research scholor)

Re: How do i access different databases

Mike Dimmick11-Aug-03 3:59

Mike Dimmick

11-Aug-03 3:59

Is this

Two SQL Server databases on the same server instance
Two SQL Server databases on two different server instances
Not SQL Server at all?

If the first or second:

You can reference any object in SQL Server (table, view, function) by a full name: server.database.owner.object. The first three can be omitted when referencing an object in the same database as you're currently accessing; owner is typically only used to disambiguate where two users have an object with the same name, although functions require the owner part.

The simplest syntax for referring to an object in another database on the same server - say you wanted to access the titles table in the pubs database is pubs..titles (note the two dots, signifying that the owner of titles has been omitted).

If you want to refer to an object on another server, you need to set up a linked server, which you can do in Enterprise Manager, under Security > Linked Servers.

I believe Access has the concept of foreign tables.

Deleted RowState and the XmlDataDocument...

b33rdy11-Aug-03 0:31

b33rdy

11-Aug-03 0:31

Hi,

I'm retrieving the changes from a dataset and creating an XmlDataDocument
for transport to a web service, however any rows with a RowState of Deleted
don't get propagated to the XmlDataDocument...is there something that I'm missing?...

Any help would be appreciated as I'm slowly going out of my mind Confused | :confused:

.

Regards
Kieron

autonumbering index

Jerome Conus10-Aug-03 19:44

Jerome Conus

10-Aug-03 19:44

Hi !!

does anyone know how to do an autonumbering index when creating a new table with SQL ?

Thanks !
Jerome

Re: autonumbering index

ZoogieZork10-Aug-03 19:54

ZoogieZork

10-Aug-03 19:54

What SQL implementation are you using?

- Mike

Re: autonumbering index

Jerome Conus10-Aug-03 19:56

Jerome Conus

10-Aug-03 19:56

It will be crossdatabase : Access, MySQL, SQL Server and Oracle (and maybe some others)....

Re: autonumbering index

ZoogieZork10-Aug-03 20:15

ZoogieZork

10-Aug-03 20:15

As far as I know, every SQL implementation has a different way of making autoincrement columns, e.g., MySQL uses the AUTO_INCREMENT modifer, Oracle uses sequence objects, PostgreSQL uses the SERIAL type, SQL Server uses IDENTITY, etc.

- Mike

SQL SPs With Conditionals Based on Parameters

kcs9-Aug-03 17:44

kcs

9-Aug-03 17:44

These may be a stupid questions, but I have not been able to figure it out except for having a SPs that are slightly different.

Is it possible in a MS-SQL Server T-SQL Stored Procedure to do something like this pseudo code:

SELECT SUM(Amount) FROM Sales
if @StoreID > 0
then WHERE StoreID = @StoreID
else WHERE StoreID > 0

And also something like this, where @StoreID is a comma delimited list of StoreIDs

SELECT SUM(Amount) FROM Sales
if CAST(@StoreID as integer) > 0
then WHERE StoreID = @StoreID
else WHERE StoreID IN (@StoreID)

Note: My SELECT statements are much (much) more complicated than this. These where minimized for clarity.

Maybe I guess the real question is "even if I can do this, should I"? I am trying to minimize the business logic and the number of different SPs I have.

Thanks in advance,

Konstantine

Re: SQL SPs With Conditionals Based on Parameters

Mike Dimmick11-Aug-03 4:52

Mike Dimmick

11-Aug-03 4:52

The first can be done in a rather roundabout way:

SELECT SUM(Amount) 
FROM Sales
WHERE 
   StoreID > 0 AND 
   StoreID = 
      CASE
         WHEN @StoreID > 0 THEN @StoreID
         ELSE StoreID
      END

This basically works by comparing the StoreID in the table against itself when the variable @StoreID is not greater than 0 (always TRUE, unless you've got any NULLs in your data), as an extra filter clause. It's sometimes possible to exploit these conditions, but you should check the impact on the execution plan in Query Analyzer (it might cause an index to be rejected, leading SQL Server to read every row, killing your performance Big Grin | :-D

).

For the second one, I refer you to http://www.algonet.se/~sommar/arrays-in-sql.html[^] (link found from http://sqljunkies.com/weblog/[^]).

Re: SQL SPs With Conditionals Based on Parameters

kcs12-Aug-03 9:02

kcs

12-Aug-03 9:02

Mike,

Thanks for your reply.

That is a nice trick. I will give it a try and see how good/bad the performance works out.

Thanks for the links too. I search for a long time and never found anything nearly that useful.

Konstantine

SQL SUM()

Anonymous9-Aug-03 8:42

Anonymous

9-Aug-03 8:42

I am wondering if this is possible with one query?

I want to sum some values based off different customer id values,
for instance:

select sum(value1) as value1_total where customer = '1111'

but also sum(value1) where it equals a customer of '9999'

Thanks,

Chris

Re: SQL SUM()

Not Active9-Aug-03 13:28

Not Active

9-Aug-03 13:28

CREATE PROC up_MyTotal
@id INT
AS
select sum(value1) as value1_total where customer = @id

The call teh stored proc twice

Re: SQL SUM()

Arjan Einbu10-Aug-03 10:10

Arjan Einbu

10-Aug-03 10:10

SELECT customer, sum(value1)<br />
FROM my_table<br />
GROUP BY customer

SELECT customer, sum(value1)<br />
FROM my_table<br />
WHERE customer IN ('1111', '9999')<br />
GROUP BY customer

DataView

Jubal7-Aug-03 8:13

Jubal

7-Aug-03 8:13

I use a DataView to sort and filter information. How do I get specfic cell out of the DataView? For example I use the DataRowView to display the entire Row. The row appears like this 1 5 10 7 9. Each number represents a different column. I need to get each number indivdually and pass it to a local variable I have created.
myId = 1.
myOtherId = 5.
Any ideas.

thanks

Linking One Server To Another

qmuffs7-Aug-03 7:02

qmuffs

7-Aug-03 7:02

I want to select some information from one server and join it to another servers information in a query. Is this Possible?

QMuffs

Re: Linking One Server To Another

Alexander Kojevnikov7-Aug-03 23:58

Alexander Kojevnikov

7-Aug-03 23:58

Yes it's possible:
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/acdata/ac_8_qd_12_9ooj.asp[^]

Alexandre Kojevnikov
MCAD charter member
Leuven, Belgium

Re: Linking One Server To Another

qmuffs8-Aug-03 8:46

qmuffs

8-Aug-03 8:46

Worked great. Thank you for your time.

QMuffs

Need help with "order by", "group by"

lnong7-Aug-03 1:44

lnong

7-Aug-03 1:44

I need help constructing a query for my database application. Please use notepad to view the text file below for the problem's description. Thanks.

http://www.nghiep.com/sqlhelp.txt[^]

Re: Need help with "order by", "group by"

Arjan Einbu7-Aug-03 8:11

Arjan Einbu

7-Aug-03 8:11

Just a small modification (in bold) to your second query:

<br />
Select e.Event_ID, e.Event_Date, p.Prod_Name from EVENT e, PRODUCT p, EVENTS_PRODUCTS eps<br />
where e.Event_ID = eps.Event_ID and eps.Prod_ID = p.Prod_ID<br />
order by e.Event_ID p.Prod_Name DESC, e.Event_Date DESC<br />

Then the grouping of data can be done in the code that renders the HTML. (Iterate through the results, and create a "group-seperator" only when Event_ID changes...)

Re: Need help with "order by", "group by"

lnong7-Aug-03 8:41

lnong

7-Aug-03 8:41

Thanks for trying help...but I think your example would only sort the Prod_Names WITHIN each Event_ID group. But still, at the highest level, the table is sorted by Event_ID, not Prod_Name. I do not want that. I need the table to be sort by Product names.

Re: Need help with "order by", "group by"

Arjan Einbu7-Aug-03 9:29

Arjan Einbu

7-Aug-03 9:29

Ah... So I need to actually READ your text... Blush | :O

Sorry, my fault...

Perl script to sql server2000 conversion

puneet trehan6-Aug-03 22:15

puneet trehan

6-Aug-03 22:15

i have a perl script file and i have to convert it into the sqlserver2000.;)

puneet

Joining tables

totig6-Aug-03 12:32

totig

6-Aug-03 12:32

Hi, please forgive my absolute stupidity. I have been using Access databases for ages, and started using SQL recently.

What is the best way to join tables. I have one table for user details, one of the fields is a bit. Depending on true or false, this will then use one of two other tables. How do you link these? Would I create a uniqueId in the users table, and then use something like a nvarchar to set up a relationship. Bearing in mind, these additional two tables, could hold multiple records of the same user. However, a user cannot appear in both these additional tables.

If I did it the way mentioned above, should i also have an additional uniqueId specific to a record?

Re: Joining tables

Rein Hillmann6-Aug-03 21:55

Rein Hillmann

6-Aug-03 21:55

Here's an example:

create table parent(id int primary key, somecolumnnameA varchar(200))<br />
create table child1(id int primary key, parentid int foreign key references parent(id), somecolumnnameB varchar(200))

Generally, you need to have the same datatypes in both the parent and the child tables if you're going to create relationships between them. The parent will need a primary key, and the child will need a foreign key that references the parent's primary key.

Howto:? obtain all unique values in a column from a .net datatable

pg82906-Aug-03 10:50

pg8290

6-Aug-03 10:50

I have a custom filter with a checked listbox and i need to fill it with all the unique values from a specified column in System.Data.DataTable. What is the most efficent way to do this?

TIA,

Peter

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.