SQL Server Performance Forum – Threads Archive
Creating a Query Designer
Hello. Does anyone know if there is a Query or a Stored Procedurethat gives the results of the "FROM" part of a Query?
I mean if you use the query edition and you drag tables the a string
is created on the "From" clause (p.e. Authors INNER JOIN
Contition ON Authors.AuthorContition = Contition.ContNum INNER JOIN etcetc).
Is there a way that i can get this query by applying the tables on a procedure or i must create the script by myself ([V] ) or is there a
tool that can give me this reluts?
Thanks.
Of course you can have the "parts" of your query statements stored in a table. You then concatenate the complete statement in a string variable (like @SQL VARCHAR(MAX)) after which you can do EXEC (@SQL). The idea of compiling the exact query statement as you go along is called "dynamic SQL".
I’m not sure i understand.
First of all the exec(@sql), i don’t understand this (not the exec, the other part).
I can have the parts in a dataset(i’m using .net) and i can join them(i use pktable to get the relations) but the real problem is when i have to use "AND" in the "From" clause. It’s a real mess then. Also the problem is to know the exact relations of the tables thus the INNER JOIN’s (p.e. you can have a relation on "Authors" and "Contitions" but a table can have a relation only with "Contitions", this table does not see the "Authors" table but you must use INNER JOIN cuz "Authors" have a relation with "Contitions" that has a relation with the other table etc. I don’t know, it seems like a mess. If i could only put the tables and get at least the INNER JOIN relations….
Seems like you’re jumping in at the deep end. Learn a little about T-SQL programming first. One of the semi-advanced subjects is dynamic SQL, which is the source of your troubles. First get a grip on how you can use dynamic SQL to handle query parts in script. Then see how you can take that to the next level by storing query parts in tables. Try not to focus too much on details – you need to get the broad picture first.
www.Sommarskog.se/Dynamic_sql.html Madhivanan Failing to plan is Planning to fail
Madhivanan the link does not work.
Adriaan you are right i’m not very familiar with this stuff but it’s job that has to be done and it has to be done fast so i admit i’m a little slopy but i don’t have much time.
So i appreciate if you can help.
Here is what i do for the time.
I use:
USE QueryMIS;
GO
EXEC sp_fkeys @pktable_name = N’Help’,
@pktable_owner = N’dbo’ so i get the relations from the tables i want. The problem is to combine the tables. I must go in each table and combine them together.
Anyway i’m downloading some T-SQL books right now but it would be nice if you got any tip to share. I’m using vb .net but i suppose i could store a query part on an SQL table but what is the use of that? Can you enlighten me?
Thanks.
sp_fkeys will give you the relevant relationships one column at a time: note the KEY_SEQ column, in relation to the FK_NAME column. I’m assuming you’ve already created the temp table in which to insert the results of sp_fkeys. INSERT INTO #temp EXEC sp_fkeys ………. You then use a cursor to read each FK name, and for each FK you concatenate the column names that you read from the same temp table, creating the ON part of the JOIN. If you don’t know JOIN syntax, then you really ought to start learning basic SQL. Perhaps as a developer you have been used to just selecting data from a single table? Then you’re about to learn how to do it properly, database-style.
<<
Madhivanan the link does not work.
>> That seems Case Sensitive site
http://www.sommarskog.se/dynamic_sql.html Madhivanan Failing to plan is Planning to fail
Ok it works now.
Any specific issues i should study for my proplem?
I plan to study at weekend and start developing after.
I would appreciate some issues to read for my problem.
Thanks.
]]>