|
|
I have four tables:
Class- a "class"
PK IdClass
Instance- an instance
PK IdInstance
FK IdClass
PropertyDefinitions- definitions of properties of a concept
PK IdPropertyDef
FK IdClass
PropertyValues- values of properites of an instance
FK IdInstance
FK IdPropertyDef
A Class can have properties. Instance can be of a class. I want to save values of properties of each Instance, but I want to prohibit setting properties of an Instance, which are not defined in it's Class.
So, a cyclic dependence occurs. How to create a proper constraint in MS Sql Server to prevent a situation, when an instance has values of properties which are not defined in a class? Or maybe I could design a better DB schema?
Thanks.
EDIT: Here[^] you can find a applicaable part of a DB diagram.
Greetings - Jacek
|
|
|
|
|
i have a parameter named @State
my where clause is depend on this parameter
for example
if @State=1
<br />
select <br />
.<br />
.<br />
.<br />
From ...<br />
where Status=true AND Response=false<br />
if @State=2
<br />
select <br />
.<br />
.<br />
.<br />
From ...<br />
where Status=false AND UserId>@Number;<br />
i want some thing like switch case in my where clause
what should i do?
|
|
|
|
|
Perhaps this would help
(Status=true AND Response=false and @State=1) or (Status=false AND UserId>@Number and @State=2)
|
|
|
|
|
thanks it does work fine!
|
|
|
|
|
hi all
i need to have a query that will retrieve two rows from one table
for example :
one table has two foreign key from one table
ROWs of Tbl1
------
1) ID
2) UserId1
3) UserId2
what should i do?
select
[User].[FirstName],
[User].[LastName],
[User].[FirstName],
[User].[LastName]
From
Tbl1 Join User on [UserId1]=[User].[Id] or [UserId2]=[User].[Id];
please help me immediately
|
|
|
|
|
Join your User table 2 times 1 for UserID1 other for UserId2
Join User as b on [UserId1]=[b].[Id]
Join User as c on [UserId1]=[c].[Id]
|
|
|
|
|
Run profiler and see where the costs are!
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
|
Well you've not given us much to go on...
You might as well just google <your database=""> optimisation tips...
|
|
|
|
|
hello all
i am working on one of my assignment about sql win app. with stored procedure.and there is two different tables with ralation to PlotID so it's working fine. now only problem is this application must have functionality to add multiple customer names with related land plot.
At the moment i can add one name and one land plot
i mean if i add some details about land plot and name of customer then it works fine but now i need to create functionality to add multiple customer name with same land plot bueause one land plot may have multiple owners.
this is my insert sp
so one of my friend said you can use multiple parameters to add multiple names but what if i need to add 3 or 4 names
so this is what i have done according to my friend and this is also working fine.
ALTER PROCEDURE dbo.InsertNew
(
@FirstName nvarchar(25),
@LastName nvarchar(25),
@FirstName2 nvarchar(25),
@LastName2 nvarchar(25),
@Village nvarchar(25),
@District nvarchar(25)
)
AS
SET NOCOUNT ON;
DECLARE @PlotID int
INSERT INTO PlotDetails (Village,District)
VALUES (@Village, @District)
SELECT @PlotID=SCOPE_IDENTITY()
INSERT INTO CustomerDetails ([FirstName], [LastName],[PlotID])
(SELECT @FirstName, @LastName, @PlotID)
UNION ALL
(SELECT @FirstName2, @LastName2, @PlotID)
RETURN
and this is vb for insert record
Private Sub InsertNew()
Try
Comm = New SqlClient.SqlCommand("InsertNew", LetsGo.AndGetConnection)
Comm.CommandType = CommandType.StoredProcedure
Comm.CommandText = "InsertNew"
Comm.Parameters.Add("@Village", SqlDbType.NVarChar).Value = ComboBox1.SelectedItem.ToString
Comm.Parameters.Add("@District", SqlDbType.NVarChar).Value = ComboBox2.SelectedItem.ToString
Comm.Parameters.Add("@FirstName", SqlDbType.NVarChar).Value = TextBox1.Text
Comm.Parameters.Add("@LastName", SqlDbType.NVarChar).Value = TextBox2.Text
Comm.Parameters.Add("@FirstName2", SqlDbType.NVarChar).Value = FirstName2.Text
Comm.Parameters.Add("@LastName2", SqlDbType.NVarChar).Value = LastName2.Text
Comm.ExecuteNonQuery()
Conn.Close()
MsgBox("Item Saved")
Catch ex As Exception
MsgBox(ex.Message)
End Try
End Sub
so is there any other way to do this i mean loop in sql?
waiting for your kind help
thanks
|
|
|
|
|
Try this
Break your InsertNew into two SPs. One for PlotDetails with @Village and @District parameter and save into PlotDetails table. Create a second SP to save CustomerDetails as
ALTER PROCEDURE dbo.InsertNewCustomerDetails
(
@FirstName nvarchar(25),
@LastName nvarchar(25),
@PlotID int
)
AS
SET NOCOUNT ON;
IF @PlotID IS NULL BEGIN
SELECT @PlotID=SCOPE_IDENTITY()
END
INSERT INTO CustomerDetails ([FirstName], [LastName],[PlotID])
(SELECT @FirstName, @LastName, @PlotID)
SELECT @PlotID AS PLOTID
Now in your vb code send Null as plotid for the first time and use the same plotid generated in first time next.
|
|
|
|
|
hello
thanks for your rep.
ok but is this going to loop in sql quary?
for adding multiple names this is what i have done
Private Sub GetNewName()
AddName.Add(TextBox1.Text)
AddName.ToArray()
End Sub
so with this i can add one name or 100 names and i have debug this code and it's looks fine but only thing is how can i insert all this name from array to database
so in that case i have to loop while i am inserting
so this is i have done for insert
Comm = New SqlClient.SqlCommand("InsertNew", LetsGo.AndGetConnection)
Comm.CommandType = CommandType.StoredProcedure
Comm.CommandText = "InsertNew"
Comm.Parameters.Add("@Village", SqlDbType.NVarChar).Value = ComboBox1.SelectedItem.ToString
Comm.Parameters.Add("@District", SqlDbType.NVarChar).Value = ComboBox2.SelectedItem.ToString
For I As Integer = 0 To AddName.Count
Comm.Parameters.Add("@FirstName", SqlDbType.NVarChar).Value = AddName(I)
Next
Comm.ExecuteNonQuery()
Conn.Close()
MsgBox("Item Saved")
and it looping through all the name i have entered but at last it shows error
error "Index was out of range. must be non-negative and less then the size of the collection parameter name index"
waiting for your kind help
|
|
|
|
|
Not this way.
First Execute
Comm = New SqlClient.SqlCommand("InsertNewPlot", LetsGo.AndGetConnection)
Comm.CommandType = CommandType.StoredProcedure
Comm.Parameters.Add("@Village", SqlDbType.NVarChar).Value = ComboBox1.SelectedItem.ToString
Comm.Parameters.Add("@District", SqlDbType.NVarChar).Value = ComboBox2.SelectedItem.ToString
to Insert Plot where InsertNewPlot is the first SP to insert plot
then something like this
Dim iPlotID as integer
For I As Integer = 0 To AddName.Count
Comm = New SqlClient.SqlCommand("InsertNewCustomerDetails", LetsGo.AndGetConnection)
Comm.CommandType = CommandType.StoredProcedure
Comm.Parameters.Add("@FirstName", SqlDbType.NVarChar).Value = AddName(I)
Comm.Parameters.Add("@PlotId", SqlDbType.NVarChar).Value = iPlotID
Dim da As SqlDataAdapter = New SqlDataAdapter(Comm)
da.Fill(ds)
iPlotID = ds.Tables(0).Rows(0)("PLOTID")
Next
|
|
|
|
|
I am just sitting here running some manual queries against some DB tables and thought that it would be GREAT if we could get a token that somehow meant the reverse of what * means in a select query.
SELECT * FROM [TableName]
Gets us ALL the columns, so if we want to grab a majority, but just skim off a few we need to enter in all of them leaving off the few we don't want.
Why not allow us to do something like this?
SELECT * EXCLUDE FieldName1, ... FROM [TableName]
This seems much cleaner to me. Oh yeah, and it is less typing.
I know, I should be using a tool that allows me to do my queries in a GUI and then it would just be drag and drop to get the columns I want, but we don't always have that luxury. It seems to me that the intent would be much more explicit to the DB engine and thus provide better optimization also.
|
|
|
|
|
You know, you ain't getting that 
Generally speaking it is better to list all your columns instead of using SELECT *. It does not have to do unneccessery lookup and your contract is fixed. You know what you will be getting. So, one disadvantage of
SELECT * EXCLUDE col1, col2 FROM tableName
is that, it will be forced to do a lookup then remove, Again, much efficient to list all the columns.
|
|
|
|
|
|
Ray Cassick wrote: popped into my head
Go wash out you mind, preferably with beer is is starting to stray into fantasy land again.
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
|
Why not write a query that returns all fieldnames ex the ones you specify, and copy/paste the result into your script? 
(Second hint, you could automate that task if it's important enough to spend time on)
I are Troll 
|
|
|
|
|
Sheesh, sounds like work
yeah, yeah... I know... Just ramblings of a tired mind on a Friday afternoon.
|
|
|
|
|
Use Red-Gate sql prompt, type
ssf [tab] expands to
Select *
From
type the table name, press up arrow and then [tab], all column name are placed in a list, remove the offending column.
With the amount of time I spent in SSMS my fingers have this in memory.
[edit] bloody html markup [/edit]
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
Hello,
I heave tried to register new server in Sql Server 2008, using SQL Authentication. The registration works but I cannot start it, pause or stop. All the options are disabled. Neither testing for new server registration works.
Waiting for response,
Kujtim
|
|
|
|
|
I have a problem getting the right data from the database.
I have two tables named "Category" and "Items". Under one category, there can be multiple items.
table for category looks like:
catagory_ID | catagory_Name | catagory_Description
and the table for item looks like:
item_ID | cat_ID | item_Name | item_Description
Here category_ID and item_ID are unique keys and cat_ID is the foreign key which is the category_ID.
now i want the query which gives the list of data under typical category in the list format.
e.g if Book is the category Name and book_name1,book_name2,book_name3 are the items under category, it should look like
Book
book_name1
book_name2
book_name3
how to do that? Well i can get something like this:
Book book_name1
Book book_name2
Book book_name3
but I want the category just once in the output..what should be the query for that ??
suchita
|
|
|
|
|
You will need to use a report builder of some sort. Crystal reports comes to mind, but there are others.
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
