|
I have select query and I am getting results like
2
4
6
If I Pass 6 as Parameter then i need to get previous number of 6 i,e 4. If 9 then it has to give 0.
How can I write the query for this in Sql Server 2005
|
|
|
|
|
DECLARE
@ID INT
SET @ID = 4
SELECT TOP 1 ID
FROM tablename
WHERE ID < @ID
ORDER BY ID DESC
Test the result for null and replace with 0
OR
Put the results into a variable in the procedure and test the variable and return the results.
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
Hello there...
I have a query which spends a lot of time calculating my CASE WHEN -statements.
There are around 16 CASE WHEN statements, which takes a huge time to calculate.
Is there any way to rewrite query?
My query looks like this
SELECT DISTINCT Product_Master.Product_Kid, Product_Master.Product_Name, convert nvarchar,Product_Master.Product_RegDate,103) as Product_RegDate, Product_Master.Product_RegDate as RegDate,
CASE WHEN (Product_Master.Product_Code = '' OR Product_Master.Product_Code IS NULL) THEN '-' ELSE Product_Master.Product_Code END AS Product_Code,
CASE WHEN (Product_Master.Product_ModelNo = '' OR Product_Master.Product_ModelNo IS NULL) THEN '-' ELSE Product_Master.Product_ModelNo END AS Product_ModelNo,
CASE WHEN (Product_Master.Product_Image = '' OR Product_Master.Product_Image IS NULL) THEN 'N' ELSE 'Y' END AS Product_Image,
CASE WHEN (Product_Master.Product_BrandId = '' OR Product_Master.Product_BrandId IS NULL) THEN 'N' ELSE 'Y' END AS Brand_Name FROM Product_Master ORDER BY RegDate DESC
|
|
|
|
|
case when (isnull(Product_Master.Product_Code, '') = '' then '-' else Product_Master.Product_Code end as Product_Code
In Word you can only store 2 bytes. That is why I use Writer.
|
|
|
|
|
Personally, I find it hard to believe that the CASE statements are what's taking time since they don't look complex. How do you know that the CASE statements are what take the most time? Have you tried the same query without some of the CASE statements, just returning the actual column data instead, and comparing the performance? Is the DISTINCT required? Is RegDate indexed?
Scott
|
|
|
|
|
Thanks for reply Scott,
Yes, I have tried the same query without CASE WHEN statements, and it works faster comperatively.
Moreover, The query is not this much only, there are many more CASE WHEN and Left Joins(which are necessary).
Left Joins are essential for the result, so I can't move it. The only options is to find alternate for CASE WHEN statements.
Prachi
|
|
|
|
|
Then we'd probably have to see the whole query since the little we've seen doesn't indicate a problem. It would help to know the table structures and rough row counts.
Scott
|
|
|
|
|
Scott,
here is the whole query.
and roughly, records for each supplier is minimum 400(rows)
declare @a as char(1)
declare @b as varchar(35)
set @a='Y'
set @b='<span style=''color:Red''>N</span>'
SELECT DISTINCT
Product_Master.Product_Kid, Product_Master.Product_Name, convert(nvarchar,Product_Master.Product_RegDate,103) as Product_RegDate, Product_Master.Product_RegDate as RegDate,
Isnull(Product_Master.Product_Code,'-') AS Product_Code,
Isnull(Product_Master.Product_ModelNo,'-') AS Product_ModelNo,
CASE WHEN (Product_Master.Product_Image IS NULL) THEN @b ELSE @a END AS Product_Image,
CASE WHEN (Product_Master.Product_BrandId IS NULL) THEN @b ELSE @a END AS Brand_Name,
CASE WHEN (Product_Master.Product_MarketPrice IS NULL) THEN @b ELSE @a END AS Product_MarketPrice,
CASE WHEN (Product_Master.Product_PackagingCharge IS NULL) THEN @b ELSE @a END AS Product_PackagingCharge,
CASE WHEN (Product_Master.Product_MinOrderQty IS NULL) THEN @b ELSE @a END AS Product_MinOrderQty,
CASE WHEN (Product_Master.Product_ShippingQty IS NULL) THEN @b ELSE @a END AS Product_ShippingQty,
CASE WHEN (Product_Master.Product_Weight IS NULL) THEN @b ELSE @a END AS Product_Weight,
CASE WHEN (Product_Master.Product_Dimension IS NULL) THEN @b ELSE @a END AS Product_Dimension,
CASE WHEN (Product_Master.Product_DeliveryPeriod IS NULL) THEN @b ELSE @a END AS Product_DeliveryPeriod,
CASE WHEN (Product_Master.Product_Description Is NULL) THEN @b ELSE @a END AS Product_Description,
CASE WHEN (Product_Master.Product_PcsPerKg IS NULL) THEN @b ELSE @a END AS Product_PcsPerKg,
CASE WHEN (ProductApplication.ProdApp_Code IS NULL) THEN @b ELSE @a END AS ProdApp_Code,
CASE WHEN (ProductFeatures.ProdFeatures_Code IS NULL) THEN @b ELSE @a END AS ProdFeatures_Code,
CASE WHEN (ProductTechnicalSpecification.TechSpec_code IS NULL) THEN @b ELSE @a END AS TechSpec_code,
CASE WHEN (ProductSpecialNotesInstruction.ProdSpecInstr_Code IS NULL) THEN @b ELSE @a END AS ProdSpecInstr_Code,
CASE WHEN (MaterialSafetyDataSheet.MSDS_Code IS NULL) THEN @b ELSE @a END AS MSDS_Code,
CASE WHEN (ProductStandardApproval.ProdStdApproval_Code IS NULL) THEN @b ELSE @a END AS ProdStdApproval_Code,
CASE WHEN (InstallationManual.InstCommManual_Code IS NULL) THEN @b ELSE @a END AS InstCommManual_Code,
CASE WHEN (ProductPackaging.ProdPackaging_Code IS NULL) THEN @b ELSE @a END AS Product_PackagingCode,
CASE WHEN (ProductInstallationCommissioning.InstallationComm_Code IS NULL) THEN @b ELSE @a END AS InstallationComm_Code,
CASE WHEN (Catalog.Catalog_code IS NULL) THEN @b ELSE @a END AS Catalog_code,
CASE WHEN (Product_GuarantyWarrantydetails.gwd_Code IS NULL) THEN @b ELSE @a END AS gwd_Code,
CASE WHEN (OtherDetail_SpareParts.ODTSP_code IS NULL) THEN @b ELSE @a END AS Spares_Code,
CASE WHEN (OtherDetail_IncludedAccessories.ODTIA_code IS NULL) THEN @b ELSE @a END AS AccessoriesIncluded_Code,
CASE WHEN (OtherDetail_OptionalAccessories.ODTOA_code IS NULL) THEN @b ELSE @a END AS AccessoriesOptional_Code,
CASE WHEN (WearAndTearParts.WearAndTearPart_Code IS NULL) THEN @b ELSE @a END AS WearAndTearPart_Code,
CASE WHEN (Consumables.c_code IS NULL) THEN @b ELSE @a END AS C_Code
FROM Product_Master LEFT JOIN
WearAndTearParts ON Product_Master.Product_Kid = WearAndTearParts.WearAndTearPart_ProductId LEFT JOIN
OtherDetail_OptionalAccessories ON Product_Master.Product_Kid = OtherDetail_OptionalAccessories.ODTOA_ProductId LEFT JOIN
OtherDetail_IncludedAccessories ON Product_Master.Product_Kid = OtherDetail_IncludedAccessories.ODTIA_ProductId LEFT JOIN
OtherDetail_SpareParts ON Product_Master.Product_Kid = OtherDetail_SpareParts.ODTSP_ProductId LEFT JOIN
ProductInstallationCommissioning ON Product_Master.Product_Kid = ProductInstallationCommissioning.InstallationComm_ProductId LEFT JOIN
ProductPackaging ON Product_Master.Product_Kid = ProductPackaging.ProdPackaging_ProductId LEFT JOIN
InstallationManual ON Product_Master.Product_Kid = InstallationManual.InstCommManual_ProductId LEFT JOIN
ProductStandardApproval ON Product_Master.Product_Kid = ProductStandardApproval.ProdStdApproval_ProductId LEFT JOIN
MaterialSafetyDataSheet ON Product_Master.Product_Kid = MaterialSafetyDataSheet.MSDS_ProductId LEFT JOIN
ProductSpecialNotesInstruction ON Product_Master.Product_Kid = ProductSpecialNotesInstruction.ProdSpecInstr_ProductId LEFT JOIN
ProductTechnicalSpecification ON Product_Master.Product_Kid = ProductTechnicalSpecification.TechSpec_ProductId LEFT JOIN
Product_GuarantyWarrantydetails ON Product_Master.Product_Kid = Product_GuarantyWarrantydetails.gwd_ProductId LEFT JOIN
ProductFeatures ON Product_Master.Product_Kid = ProductFeatures.ProdFeatures_ProductId LEFT JOIN
ProductApplication ON Product_Master.Product_Kid = ProductApplication.ProdApp_ProductId LEFT JOIN
[Catalog] ON Product_Master.Product_Kid = Catalog.Catalog_productid LEFT JOIN
Consumables ON Product_Master.Product_Kid = Consumables.c_productid
WHERE Product_Master.Product_Isdeleted = '0'
AND Product_Master.Product_SupplierId = '00000273V'
ORDER BY RegDate DESC
select * from supplier_master
|
|
|
|
|
Left joins.....
yup that's costly
In Word you can only store 2 bytes. That is why I use Writer.
|
|
|
|
|
Yeah, what they say. Plus why convert (and transmit) the textual RegDate? Perform that outside the database.
|
|
|
|
|
I suppose that the nulls are there because of the left joins, you refer to in your answer to Scott, and therefore is nothing you can do anything about.
But how about the zero length strings, do they serve a purpose?
If not, you can add a check to the columns that disallows zero length strings at inserts and then you can remove that check in the query, which should speed it up a bit.
Alternatively swap to Oracle where you can use a Function Based Index which would speed up things immensely.
Like: Create index PM_PRODUCTCODE_EXIST_IDX ON PRODUCT_MASTER(Product_Kid,CASE WHEN (Product_Code = '' OR Product_Code IS NULL) THEN '-' ELSE Product_Master.Product_Code END);
"When did ignorance become a point of view" - Dilbert
|
|
|
|
|
|
I'm sorry I have no answer to your question. However, I don't know that you will get one seeing as how you violated #8 in the "How to get an answer to your question" sticky thread. :-P
8. Be courteous and DON'T SHOUT. Everyone here helps because they enjoy helping others, not because it's their job.
|
|
|
|
|
Hello bapu2889, i agree to Matt U.
if you want to get fast and good answers, don't SHOUT.
To your problem.
You're calling a stored procedure which has 5 parameters (in). When you use more than one pair of names, your code creates a sqlcommand with 7 parameters, 9 parameters and so on.
When i try your code, i am getting an sqlexception because there are too many parameters. The exception you are getting is an conversion error in the catch-clause. Sorry, but i can't find this out because i haven't installed vb.net.
Try this:
Insert the location data (change your sp, return the id of the new location as out parameter).
Insert each of the userdata with the id of the new location (execute second sp in a loop, set parameters).
hth
stoffy
|
|
|
|
|
hello sir
thanks for ur rep.
and i dont know that i have shouted on any body
this is what i have done so far but no luck.
this is 2 different sp
ALTER PROCEDURE dbo.NewInsertCommand
(
@City nvarchar(50),
@State nvarchar(50)
)
AS
SET NOCOUNT ON;
DECLARE @LocationID int;
INSERT INTO [Location] (City,State)
VALUES (@City, @State)
SELECT @LocationID = SCOPE_IDENTITY()
this is the second one
ALTER PROCEDURE dbo.InsertUser
@FirstName varchar(25),
@LastName varchar(25),
@LocationID int
AS
INSERT INTO [UserData] (LocationID, FirstName, LastName)
(SELECT @LocationID, @FirstName, @LastName)
RETURN
and this is the vb
Private Sub AddNewNames()
FirstNameArray.Add(FirstName.Text)
FirstNameArray.ToArray()
LastNameArray.Add(LastName.Text)
LastNameArray.ToArray()
End Sub
Private Sub InsertData()
Try
Dim Flag As Boolean = False
'GetDBConnection()
Dim cmd As New SqlCommand("NewInsertCommand", LetsGo.GetDBConnection)
cmd.CommandType = CommandType.StoredProcedure
cmd.Parameters.Add("@City", SqlDbType.NVarChar).Value = ComboBox1.SelectedItem
cmd.Parameters.Add("@State", SqlDbType.NVarChar).Value = ComboBox2.SelectedItem
cmd.ExecuteNonQuery()
cmd = New SqlCommand("InsertUser", LetsGo.GetDBConnection)
cmd.CommandType = CommandType.StoredProcedure
For I As Integer = 0 To FirstNameArray.Count - 1
cmd.Parameters.Add("@FirstName", SqlDbType.NVarChar).Value = FirstNameArray(I).ToString
cmd.Parameters.Add("@LastName", SqlDbType.NVarChar).Value = LastNameArray(I).ToString
cmd.ExecuteNonQuery()
Next
MsgBox("Data Saved Successfully !", MsgBoxStyle.Information)
and one more thing i dont know how to get @LocationID from one sp to another sp as well
so would you mind to guide me please
waiting for your kind help
thanks
|
|
|
|
|
Don't add the parameters in the for loop -- add them before it and then set them inside the loop.
|
|
|
|
|
The first stored procedure:
CREATE PROCEDURE [dbo].[InsertLocation]
(
@City nvarchar(50),
@State nvarchar(50),
@LocationID int out
)
AS
INSERT INTO [Location] (City,State)
VALUES (@City, @State)
SELECT @LocationID = SCOPE_IDENTITY()
GO
and the second:
CREATE PROCEDURE [dbo].[InsertUserData]
(
@LocationID int,
@FirstName nvarchar(25),
@LastName nvarchar(25)
)
AS
SET NOCOUNT ON;
INSERT INTO [UserData] (LocationID, FirstName, LastName)
(SELECT @LocationID, @FirstName, @LastName)
GO
My code, in c#:
Assuming that the array of names is filled with items
private static void insertData()
{
try
{
SqlConnectionStringBuilder sb = new SqlConnectionStringBuilder();
sb.DataSource = ".\\SQLEXPRESS";
sb.InitialCatalog = "Database";
sb.IntegratedSecurity = true;
using (SqlConnection cn = new SqlConnection(sb.ToString()))
{
cn.Open();
SqlCommand insertLocationCommand = new SqlCommand("InsertLocation", cn);
insertLocationCommand.CommandType = CommandType.StoredProcedure;
insertLocationCommand.Parameters.Add("@City", SqlDbType.NVarChar).Value = "City1";
insertLocationCommand.Parameters.Add("@State", SqlDbType.NVarChar).Value = "State1";
insertLocationCommand.Parameters.Add("@LocationID", SqlDbType.Int).Value = 0;
insertLocationCommand.Parameters["@LocationID"].Direction = ParameterDirection.Output;
insertLocationCommand.ExecuteNonQuery();
int locationID = (int)insertLocationCommand.Parameters["@LocationID"].Value;
SqlCommand insertUserDataCommand = new SqlCommand("InsertUserData", cn);
insertUserDataCommand.CommandType = CommandType.StoredProcedure;
insertUserDataCommand.Parameters.Add("@LocationID", SqlDbType.Int).Value = locationID;
insertUserDataCommand.Parameters.Add("@FirstName", SqlDbType.NVarChar);
insertUserDataCommand.Parameters.Add("@LastName", SqlDbType.NVarChar);
for (int i = 0; i < FirstNameArray.Count; i++)
{
insertUserDataCommand.Parameters["@FirstName"].Value = FirstNameArray[i].ToString();
insertUserDataCommand.Parameters["@LastName"].Value = LastNameArray[i].ToString();
insertUserDataCommand.ExecuteNonQuery();
}
Console.WriteLine("Data Saved Successfully !");
}
}
catch (SqlException ex)
{
Console.WriteLine(ex.ToString());
}
finally
{
FirstNameArray.Clear();
LastNameArray.Clear();
}
}
If you can't understand the code in c# let me know, i'll try to convert it but i am not able to test it...
|
|
|
|
|
HELLO SIR
THANK YOU VERY MUCH IT'S WORKS NICE NOW
I WAS LOOPING IN MY SP AND VB BOTH SO NOW I HAVE MUCH BETTER IDEA ABOUT THIS SO
AND I HAVE CONVERTED TO VB. I CAN UNDERSTAND C# BUT I AM NOT VERY GOOD WITH IT BUT ANY WAY YOUR HELP AND UR SUPPORT WAS VERY GOOD
THANKS AGAIN
|
|
|
|
|
from the forum guidelines:
6. Do not remove or empty a message if others have replied. Keep the thread intact and available for others to search and read. If your problem was answered then edit your message and add "[Solved]" to the subject line of the original post, and cast an approval vote to the one or several answers that really helped you.
8. Be courteous and DON'T SHOUT. Everyone here helps because they enjoy helping others, not because it's their job.
|
|
|
|
|
Hi,
I have an application in C# that use SQL Server 2008 R2 Express.
When i install the package i need to choose option and to install:
1. Microsoft .Net Framework 3.5 SP1.
2. Windows Installer 4.5.
3. Windows PowerShell 1.0
I have an SQL Script that will build my DB at SQL 2008 R2 Express and will add the user configuration that i want.
I want to make install package that will include the SQL 2008 R2 Express (including "Microsoft .Net Framework 3.5 SP1.",
,"Windows Installer 4.5" and "Windows PowerShell 1.0 ").
Will run the script and install my application.
I don't want to let the user to choose configuration for the DB in the installation process.
How can i do that?
Thanks,
Shai.
|
|
|
|
|
Please do not post in more than one forum; you already posted this question under .NET and C#. Pick one forum and stick to it.
It's time for a new signature.
|
|
|
|
|
|
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)
|
|
|
|
|