|
declare
@Exists int
Select @Exists = count(*) from tblStaff where StaffID = @StaffID
If @Exists = 0
Begin
add record
end
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
This query has a better performance:
if not exists(select * from tblStafff where StaffID = @StaffID)
Begin
-- Add record
End
Wout Louwers
|
|
|
|
|
i got half way through the reply and started adding additional bits, should have started again  .
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
first , i'm very thank for your help ... but can you tell me more detail ? can you give me some sample...i'm a new learner.
|
|
|
|
|
All 3 of us supplied sample code - use Wout's it is the best.
As a learner you should have a book to work through the examples! You need some basic knowledge to begin with...
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
thank 4 your advice...now i'm checking my book other sample in my class
|
|
|
|
|
Here is the Stored Procedure
ALTER PROCEDURE SP_CHECKDUPLICATEID
-- Add the parameters for the stored procedure here
(@ID INT)
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
-- Insert statements for procedure here
-- VARIABLE DECLARATIONS
IF ( (SELECT COUNT(*) FROM TBLSTAFF WHERE ID=@ID) = 0 )
BEGIN
INSERT INTO TBLSTAFF(ID) VALUES(@ID)
END
END
GO
hope it helps
Niladri Biswas
|
|
|
|
|
I have got two queries both of which generate the same execution plan:
query 1:
SELECT TOP 10 *
FROM news
CROSS APPLY (SELECT TOP 1 NetworkID FROM ItemNetwork WHERE ItemID = news.ID) itemNet
query 2:
SELECT TOP 10 *
FROM news
CROSS APPLY (SELECT TOP 1 NetworkID FROM ItemNetwork WHERE ItemID = news.ID AND ItemType = 0) itemNet
ItemNetwork table has 4 columns:
[ID] [bigint] IDENTITY(1,1) NOT NULL,
[ItemID] [bigint] NOT NULL,
[ItemType] [tinyint] NOT NULL,
[NetworkID] [int] NOT NULL
I have also created a non-clustered index on ItemNetwork table:
CREATE NONCLUSTERED INDEX [IX_ItemNetwork_ItemID_ItemType__NetworkID] ON ItemNetwork
(
[ItemID] ASC,
[ItemType] ASC
)
INCLUDE ( [NetworkID])
The first query takes one second to execute, while it takes 2 minutes for the second one to execute. The execution plan for both queries is the same. You can see the execution plan for the first query here[^] and for the second query here[^].
The only difference that can be seen between the two execution plans is the amount of data that comes out of news table. For the second query, we see a very big arrow coming out of news table. That is because the actual number of rows coming out of this table is 1534672 rows while for the first query, this number is 877 rows. For both queries, the estimated number of rows is 10 (because of top 10 clause). Look at the actual number of rows for both queries here[^] and here[^].
The only difference between the two queries is this condition:
ItemType = 0
I also updated the statistics for all the tables involved, but it didn't make any difference.
Could somebody please tell me how I can make the second query execute as fast as the first one?
p.s. the total number of rows in News table is 1576612 rows, in Network table 1820 rows and in ItemNetwork table 42164 rows
modified on Thursday, June 25, 2009 3:19 AM
|
|
|
|
|
Is it possible that the index could be fragmented? Not that it should make such a significant difference but might be worth checking.
|
|
|
|
|
I looked at it. The fragmentation is 0%
|
|
|
|
|
How to code connection string for retriving database when is online?
How to code dynamic connection string?
Phumlani Sandile Kunene
Country South Afica
Province KwaZulu Natal
City Vryheid
Township Emvuyane, eS'mashwini
Room number 540
|
|
|
|
|
Phumlani Kunene wrote: How to code connection string for retriving database when is online?
http://www.connectionstrings.com/[^]
Phumlani Kunene wrote: How to code dynamic connection string?
Maybe just create the connection string according to your "dynamic" requirements.
|
|
|
|
|
(Q1) How to code connection string for retriving database when is online?
Hi Man, first of all it seems to me that the question is not very clear. Because, in general, we access the connection string from the Frontend programming language. So which language you are using is nowhere mentioned.
However, I am presenting the solution in C#.net.
In the App.config file(Windows environment) or Web.config file(Web development), specify the connection string like this
<?xml version="1.0" encoding="utf-8" ?>
<configuration>
<configSections>
</configSections>
<connectionStrings>
<add name="dbName" connectionString="Data Source=DBServer\SQLEXPRESS;Initial Catalog=DBHM;Integrated Security=True"
providerName="System.Data.SqlClient" User Name="Niladri" Pwd="password"/>
</connectionStrings>
</configuration>
The key factors to note here are
a)name="dbName" ;
It is a string which we will access from the class file
b)Data Source</b>=DBServer\SQLEXPRESS;;
The database server name
c)Initial Catalog</b>=DBHM;
The database name
Next access this connection string from any class file like this
System.Configuration.ConfigurationManager.ConnectionStrings["dbName"].ConnectionString;
(Q2)How to code dynamic connection string?
As far as what I understood, you want to change your connection string based on certain condition.
If that is the case, add serveral connection strings in your .config file the way I showed above but only change the Names and the Initial Catalogs(because if you change the connection string means, you are looking for a different database) like the following
<add name="dbName1" connectionString="Data Source=DBServer\SQLEXPRESS;Initial Catalog=DBHM1;Integrated Security=True"
providerName="System.Data.SqlClient" />
<add name="dbName2" connectionString="Data Source=DBServer\SQLEXPRESS;Initial Catalog=DBHM2;Integrated Security=True"
providerName="System.Data.SqlClient" />
<add name="dbName3" connectionString="Data Source=DBServer\SQLEXPRESS;Initial Catalog=DBHM3;Integrated Security=True"
providerName="System.Data.SqlClient" />
<add name="dbName4" connectionString="Data Source=DBServer\SQLEXPRESS;Initial Catalog=DBHM4;Integrated Security=True"
providerName="System.Data.SqlClient" />
Once done, based on some logical condition , you can access the connection strings from your class files
e.g.(Some pseudo code)
SqlConnection Conn = new SqlConnection();
switch(condition)
{
case 1: Conn.ConnectionString = System.Configuration.ConfigurationManager.ConnectionStrings["dbName1"].ConnectionString;
break;
case 2: Conn.ConnectionString = System.Configuration.ConfigurationManager.ConnectionStrings["dbName2"].ConnectionString;
break;
case 3: Conn.ConnectionString = System.Configuration.ConfigurationManager.ConnectionStrings["dbName3"].ConnectionString;
break;
case 4: Conn.ConnectionString = System.Configuration.ConfigurationManager.ConnectionStrings["dbName4"].ConnectionString;
break;
}
Conn.Open();
Hope this helps.
Niladri Biswas
|
|
|
|
|
What is the code for Log_In in ASP using C#, the username and the password will be checked from SQL database to check whether that person exist or not.
Phumlani Sandile Kunene
Country South Afica
Province KwaZulu Natal
City Vryheid
Township Emvuyane, eS'mashwini
Room number 540
|
|
|
|
|
There are literally thousands of examples of this on various websites including this one. If you are unable to find one, may I suggest you're taking the wrong course or in the wrong job.
|
|
|
|
|
Would you like us to write the code for you? I think J4 is chomping at the bit to do this for you.
"My interest is in the future because I'm going to spend the rest of my life there." - Charles F. Kettering
|
|
|
|
|
The stored procedure will be like this
ALTER PROCEDURE [dbo].[sp_ValidateUser]
-- Add the parameters for the stored procedure here
(
@UserName VarChar(50), @Password VarChar(50)
)
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
SELECT count(*) AS Num_of_User FROM tblUser
WHERE (((tblUser.U_Name) = (@UserName)) AND ((tblUser.U_Password) = (@Password)))
END
From the code behind(C#), write the following code snippet to access the connection string specified in the web.config
public SqlConnection OpenConnection()
{
SqlConnection Conn = new SqlConnection();
Conn.ConnectionString = System.Configuration.ConfigurationManager.ConnectionStrings["dbName"].ConnectionString;
Conn.Open();
return Conn;
}
public void CloseConnection(SqlConnection Conn)
{
Conn.Close();
}
public SqlCommand GetStoredProcCommand(string CommandText)
{
SqlCommand CmdStProc = new SqlCommand();
try
{
CmdStProc.Connection = OpenConnection();
CmdStProc.CommandText = CommandText;
CmdStProc.CommandType = CommandType.StoredProcedure;
}
catch (Exception ex)
{
string msg = ex.Message;
}
return CmdStProc;
}
The web.config file is as under
<?xml version="1.0" encoding="utf-8" ?>
<configuration>
<configSections>
</configSections>
<connectionStrings>
<add name="dbName" connectionString="Data Source=yourdatasource;Initial Catalog=DatabaseName;Integrated Security=True"
providerName="System.Data.SqlClient" />
</connectionStrings>
</configuration>
Next in a class file(say ValidateUser.cs) write the following code snippet
public DataTable UserValidation(string UserName, string Password)
{
DBConnection ObjData = new DBConnection();
SqlCommand SelectCmd = new SqlCommand();
SqlDataAdapter Adpt = new SqlDataAdapter();
DataTable DtUserInfo = new DataTable();
try
{
SelectCmd = ObjData.GetStoredProcCommand("sp_ValidateUser");
SelectCmd.Parameters.Add("@UserName", SqlDbType.Int);
SelectCmd.Parameters.Add("@Password", SqlDbType.VarChar,1);
SelectCmd.Parameters[0].Value = UserName;
SelectCmd.Parameters[1].Value = Password;
Adpt.SelectCommand = SelectCmd;
Adpt.Fill(DtUserInfo);
}
catch (Exception ex)
{
string error = ex.Message;
}
Adpt = null;
SelectCmd = null;
ObjData = null;
return DtUserInfo;
}
Create a form with User Name (txtUserName) and Password(txtPassword) and A lable(lblMessage) for holding the user status.
From the code behind write,
ValidateUser objValidateUser = new ValidateUser();
DataTable dt = objValidateUser .UserValidation(txtUserName.Text,txtPassword.Text);
if(dt!=null)
{
if(dt.Rows.Count > 0)
{
if(Convert.Toint32(dt.Rows[0][0].ToString()) > 0)
lblMessage.Text = "Valid User";
else
lblMessage.Text = "InValid User";
}
}
Hope this helps
Niladri Biswas
|
|
|
|
|
hi all
i want to prepare a query which returns the result on the basis of combination of character.
In my MSAccess Database, table have two fields Id and Name
Scenario is following
1 have 3 buttons with title "abc" ,"def","ghi"
1.if we first click on "abc" button then query must return the result for which name field begins with 'a' or 'b' or 'c' OR it contains " a"(space before a) or " b"(space before b) or " c"(space before c).
2. Now if we click on button "def" (remember that in previous step we click on "abc" button) then it must return the record that start with ('ad','bd','cd','ae','be','ce','af','bf','cf') OR it contains (' ad',' bd',' cd',' ae',' be',' ce',' af',' bf',' cf') means space before text
and so on..
Please suggest me how can i build query for this type of condition.
|
|
|
|
|
Hi,
A complex problem, but perhaps this[^] thread might be useful.
Regards,
Syed Mehroz Alam.
My Blog
My Articles
Computers are incredibly fast, accurate, and stupid; humans are incredibly slow, inaccurate and brilliant; together they are powerful beyond imagination. - Albert Einstein
|
|
|
|
|
Thanks syed,
but it does not solve my problem
|
|
|
|
|
Dear Rupesh,
As I don't have MS Access in my system.
So I was forced to solve the problem in SQL Server 2005. But the syntax will remain same in both the cases(or may be minor changes which I hope you can do).
I have created a table[TBLSEARCH] with the following values
a
b
c
a
b
c
AC
AD
AD
AE
AE
AF
AF
AFGTHY
BE
BE
And my Stored Proc goes like this
ALTER PROCEDURE sp_Search
-- Add the parameters for the stored procedure here
(@SEARCHSTRING VARCHAR(50))
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
-- SEARCH WITH ABC COMBINATION
IF(UPPER(@SEARCHSTRING) = 'ABC')
BEGIN
SELECT *
FROM TBLSEARCH WHERE RTRIM(LTRIM(NAME)) LIKE '[A-C]%'
END
-- SEARCH WITH DEF COMBINATION
IF(UPPER(@SEARCHSTRING) = 'DEF')
BEGIN
SELECT *
FROM TBLSEARCH WHERE
RTRIM(LTRIM(NAME)) LIKE 'A[DEF]%' OR
RTRIM(LTRIM(NAME)) LIKE 'B[DEF]%' OR
RTRIM(LTRIM(NAME)) LIKE 'C[DEF]%'
END
-- SEARCH WITH GHI COMBINATION
IF(UPPER(@SEARCHSTRING) = 'GHI')
BEGIN
SELECT *
FROM TBLSEARCH WHERE
RTRIM(LTRIM(NAME)) LIKE 'A[DEF][GHI]%' OR
RTRIM(LTRIM(NAME)) LIKE 'B[DEF][GHI]%' OR
RTRIM(LTRIM(NAME)) LIKE 'C[DEF][GHI]%'
END
END
GO
N.B.~ The @SEARCHSTRING can assume values either 'ABC' or 'DEF' or 'GHI' [ As your button names ]
In my system I am getting the correct output.
Please check it and let me know if it is working as per your expectation or not.
Regards,
Niladri Biswas
Niladri Biswas
modified on Wednesday, June 24, 2009 8:40 AM
|
|
|
|
|
You can do letter groupings in a LIKE clause using SQL2005? cool!
|
|
|
|
|
|
first of all many many thanks. 5 from me
and sorry for late reply since i am busy with some other project.so i do not reply you
This query works well.
one more thing
whether you have any idea of SQLite. since i just check and run this query in sqlite. but i don't know the syntax which is similar to above syntax (as you suggest)
|
|
|
|
|
Hello,
we have huge SQL database on remote server which slow down the application performance because of low connection bandwidth and heavy traffic.
I want to create proxy server on local SOL server. I want that sync to happen automatically between servers and no user interaction required to do so.
Is it possible and how?
Thanks a lot.
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
