|
you can not execute a t-sql query in a function but in stored procedure you can.
|
|
|
|
|
functions return only one value.........sp can used to return more than one value....
function need to be complied each time u use....but sp ones complied u can use it...till if cant change any thing
|
|
|
|
|
This came up in a job interview (I probably didn't get  ) and was wondering what solution was?
If i have the tables (IIRC):
CD:
id, title, serialnumber, cdinfo
Artist:
id, name, artistinfo
Track:
id, title, length
CDTrack (Associates tracks to cd's ?)
id, cdid, trackid
ArtistTrack (Associates artists to tracks ?)
id, artistid, trackid
How do I query based on, say CD name or id and get the rest of the CD data + tracklist complete with the list of artists that performed the each track?
My sql skills are limited to basic selects updates and inserts and this seems to require a lot of joining (I assume) which I'm not too hot on.
Thanks in advance.
|
|
|
|
|
Hi,
okay let's have a try.
I would say this query will compute the desired output:
<br />
select cd.title, cd.serialnumber, cd.cdinfo, track.title, track.length, artist.name, artist.artistinfo<br />
from <br />
cd inner join cdtrack on cd.id = cdtrack.cdid <br />
inner join track on track.id = cdtrack.trackid<br />
inner join artisttrack on artisttrack.trackid = cdtrack.trackid<br />
inner join artist on artisttrack.artistid = artist.id<br />
Don't know if this is completly correct but should go in the right direction.
Regards,
Sebastian
P.S. Wish you the best while searching a job.
|
|
|
|
|
To retrieve data from multiple tables in a single query, you join the tables. To do this, you use the JOIN keyword:
SELECT *
FROM CD
JOIN CDTrack ON CD.id = CDTrack.cdid In the ON clause, you give a boolean expression; the database engine conceptually evaluates every combination of rows from both tables, and if this expression evaluates to True for a given combination, the row is included in the results (unless further constrained by a WHERE or HAVING clause). If the boolean expression uses only equality expressions it's called an equijoin.
There are a number of types of joins. The most common, and the default if you don't specify a type of join, is an inner join. Here, only rows where the ON expression evaluates to True are included. There are also left (outer) joins, where if there are no rows on the right-hand side where the expression is true for a given row on the left-hand side, the output contains the data from the row on the left, while the fields that should contain the data from the right-hand side are set to NULL. A right (outer) join is the same as a left outer join, but with the roles of the left and right tables reversed. There's also a full outer join where rows from both tables with no match from the other table are output. Finally there's a cross join which simply outputs every row from the left-hand table combined with every row from the right-hand table - this uses no ON clause.
The outer joins are generally useful when you're joining a live table to a reference lookup table and can't guarantee that you have the corresponding data in the reference table, but it's imperative that you return all rows from the live table.
You can chain together join operations into a larger query, so for example to get a complete listing of every track on every CD you would write:
SELECT *
FROM CD
JOIN CDTrack ON CD.id = CDTrack.cdid
JOIN Track ON CDTrack.trackid = Track.id
JOIN ArtistTrack ON CDTrack.trackid = ArtistTrack.trackid
JOIN Artist ON ArtistTrack.artistid = Artist.id To know which columns to include in an ON clause requires you to know the schema and what each column represents. In that example we could have joined ArtistTrack either using CDTrack.trackid or Track.id since they have the same value. I'm not sure whether it makes any difference to the query optimizer - probably not, so whichever is clearer.
Mentioning the query optimizer - the optimizer will normally reorder the joins into whatever order it thinks will execute most quickly, taking into account any filter expressions in a WHERE clause. You can force it to execute in the order you specify (might be useful for avoiding deadlocks) by adding OPTION (FORCE ORDER) to the end of the query (on Microsoft SQL Server).
Once you have joined tables together, you may have some ambiguous column names - for example, here all tables have a column named 'id'. You must qualify an ambiguous column name with the table name (e.g. 'Track.id'). Best practice is to always specify the table name, so that your query is robust if columns are later added to one of the tables which clash with a column name from one of the other tables. To avoid having to type out the full name of a table with a long name, you can add an alias for a table to the query, which goes after the table name (optionally after the AS keyword) in the FROM/JOIN clause.
|
|
|
|
|
Brillaint! thank you so much 
|
|
|
|
|
I'm desiging a form that has related information from several tables: parts, manufacturing methods, jobs, raw material, customer po's, etc. To minimize network traffic, I start with a dataset populated with part numbers and another dataset populated with a table of part processes (i.e. a part may have processA, process B, etc.)
Should I use one main dataset to store all the different tables generated by different stored procedures as well as one main SqlDataAdapter to communicate between sql database and windows form? If I do, then when pushing changes back to database via adapter, won't I have to re-set the "select", "update", and "delete" commands for the adapter based on the appropriate table when updating?
The alternative is to have several adapters for the form with their own specific "select" statements, initialized one time, and ready to be used whenever a change is pushed.
Does this sound correct and if so, which is the preferred design method?
Thanks in advance!
John
|
|
|
|
|
The most efficient and maintainable method would be to have a DataSet that represents all the 'related' tables required for your form. This DataSet should contain all the tables and each table should have a relationship between Primary and Foreign keys. You then need a DataAdapter for each table in your DataSet. When you close the form, clear the tables and DataSet.
Alternatively, you can have have a main DataSet that represents your entire database and extend it (add tables and relations) as you open each form. You can then clear all tables and the DataSet when you close the application. This is difficult to maintain and the least efficient since it consumes more resources on the client.
With either method, you could re-use one global DataSet object as required.
I usually create another DataSet to hold any temporary tables required.
Steve
|
|
|
|
|
Thanks Steve. Your "most efficient" method is what I was thinking, but wasn't quite sure about the DataAdapters. Your suggestion makes perfect sense.
|
|
|
|
|
i have two text files namely test1.txt and test2.txt
by using DTS how can we transform dynamically
Regards
Dayakar D.N
|
|
|
|
|
How can i use the project(Windows Application) made by vb.net(use Ado.Net) depend on SQL Server2000 in client pc without install SQL Server2000 in the client pc?
In other words I want ask if there is any way in which we will not install SQL Server2000 on client pc and the program will run properly?
|
|
|
|
|
FriendlySoluations wrote: In other words I want ask if there is any way in which we will not install SQL Server2000 on client pc and the program will run properly?
So long as there is a network path to the SQL Server that you want to use you can run the client application anywhere. SQL Server is designed to run as a central server with many client processes using it across a network.
|
|
|
|
|
I'm not sure if I understand your question correctly, but have you heard of the Micrsofoft SQL Desktop Engine (MSDE)?
Steve
|
|
|
|
|
Dear friends,
I want to traverse the metadata information of a database. For example, i've a database and i want to list all the user tables in that databse. Also, for each table, i want to list all the columns of the table. Similarly, for each column, i want to determine whether it is a primary key or a foreign key. I also want to determine constraints information.
What is the best method to do so in .NET ?
Imtiaz
|
|
|
|
|
--1fst sp
CREATE PROCEDURE Ks_Ins_Media_Item
@Desc nvarchar(200),
@added_date datetime,
@Added_by nvarchar(100),
@item int output
As
insert into Ks_Media_Item ([Description],Added_Date,Added_By)
values(@Desc,@added_date,@Added_by)
set @item = @@identity
============================
--2sd sp
CREATE PROCEDURE Ks_Ins_Media_Item_content
@Item_No int,
@content ntext,
@textlength int
As
insert into Ks_Media_Item_content(Item_no,content,text_length)
values(@item_no,@content,@textlength)
========================================
--3rd sp
CREATE procedure ks_media_item_full_data
@Desc nvarchar(200),
@added_date datetime,
@Added_by nvarchar(100),
@content ntext,
@textlength int
as
declare @ItemNo int
execute dbo.Ks_Ins_Media_Item @item output,@Desc, @added_date, @Added_by
execute dbo.Ks_Ins_Media_Item_content @ItemNo output,@content, @textlength
===================================
can you help me
i want to pass yhe output parameter to 3rd sp
becuse 1st the Item_no is identity yes
and in 2sd sp identity false
how can i do it
pls
|
|
|
|
|
CREATE PROCEDURE Ks_Ins_Media_Item
@Desc nvarchar(200),
@added_date datetime,
@Added_by nvarchar(100),
@item int output
As
insert into Ks_Media_Item ([Description],Added_Date,Added_By)
values(@Desc,@added_date,@Added_by)
set @item = @@identity
CREATE PROCEDURE Ks_Ins_Media_Item_content
@Item_No int,
@content ntext,
@textlength int
As
insert into Ks_Media_Item_content(Item_no,content,text_length)
values(@item_no,@content,@textlength)
CREATE procedure ks_media_item_full_data
@Desc nvarchar(200),
@added_date datetime,
@Added_by nvarchar(100),
@content ntext,
@textlength int
as
declare @ItemNo int
execute dbo.Ks_Ins_Media_Item @Desc, @added_date, @Added_by, @ItemNo output
execute dbo.Ks_Ins_Media_Item_content @ItemNo,@content, @textlength
--EricDV Sig---------
Some problems are so complex that you have to be highly intelligent and well informed just to be undecided about them.
- Laurence J. Peters
|
|
|
|
|
i have procedure with output parametr
i have to pass it to another procedure
in execution what is the syntax of it
pls
|
|
|
|
|
procedure1(procedure2(proc2Param))
|
|
|
|
|
it's cannat run can i pass the code to you yo see
the error
thank you
|
|
|
|
|
Paste ur code here and we will solve problem
|
|
|
|
|
Hi,
I need to modify a MS Access database:
- Rename tables
- Rename columns
- Add new columns and set field properties
(Can't seem to set "Allow Zero Length" with Access SQL)
I'm finding that SQL for Access is sometimes lacking when I need to update the database as my application grows. I see from my on-line research that some people turn to DAO or ADOX. If I'm working with an Access db, which should I use and why?
PS - Why would MS Access have a field property "Allow Zero Length" but no SQL statement to set/change it?
thanks,
Ron
|
|
|
|
|
First of all, only Text, Memo and Hyperlink fields can accept zero length strings and therefore have that property.
You may want to look into using DDL to manage your table properties.
Steve
|
|
|
|
|
Thanks Steve,
I knew that only Text, Memo and Hyperlink fields only have that property, but as far as my research, I can't use SQL to change this property... if there is, please let me know.
Thanks for the tip on DDL, I'll do some research.
Ron
|
|
|
|
|
I always do such edits in Access. I have never tried to alter table structures dynamically from VB.
You may want to try posting your question in MDBMakers.com.
This forum has Access MVP's and many knowledgeable users who may be able to help you.
Steve
|
|
|
|
|
hi,
i'm working on mysql database i want to select from table last 10 rows inserted where id=5
Always Thx for you help
Regards,
Charbel Asmar
|
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
