|
The first is better. Easier to maintain and look at the reason Colin said for #2 in his reply.
"Real programmers just throw a bunch of 1s and 0s at the computer to see what sticks" - Pete O'Hanlon
|
|
|
|
|
Hi all,
I'm currently doing a contract and supplier window form application.
I have this table (Contract) containing the following fields:
Contract_ID INT NOT NULL,
Vendor_ID INT NOT NULL,
DateStart datetime,
DateDue datetime,
PRIMARY KEY(Contract_ID),
FOREIGN KEY (Vendor_ID) REFERENCES Vendor(Vendor_ID)
I would like to do something like select contracts up for renewal in 3 months (my DateDue values range from 2004-04-16 to 2012-10-29).
I only know how to begin with the following:
SQL Select Statement:-
SELECT Contract_ID FROM Contracts WHERE DateDue... blahblahblah (can anyone guide me to continue?)
I am not sure that the above beginning statement is correct..
Any help is greatly appreciated. Thanks! 
telly
|
|
|
|
|
SELECT Contract_ID <br />
FROM Contracts <br />
WHERE DateDue BETWEEN GetDate() AND DateAdd(mm, 3, GetDate())
Regards
KP
|
|
|
|
|
Hey, KP!
I tried the code and it works! Thanks for the help.
|
|
|
|
|
anybody can help on the MS SQL Server 2000? i try to run the store procedure below with the parameter -@description which is chinese words. it is using for doing the search function and the results come out with a unrelated result. is it that not supported with chinese words?
CREATE PROCEDURE TG_testChiness
(
@description nvarchar(200)
)
AS
select * from acstk
where description like @description
GO
|
|
|
|
|
Can someone tell me what is wrong here? I know the answer is simple, but i'm tired,...and its Christmas... 
DECLARE @CatList VARCHAR(100)<br />
SET @CatList = ''''+ 'Hardware' + '''' + ',' + '''' + 'Custom' + ''''<br />
PRINT @CatList
Returns ‘Hardware’,’Custom’
SELECT p.ProductCode<br />
FROM Product p<br />
JOIN ProductCategory pc<br />
ON p.ProductCatID = pc.ProductCatID<br />
WHERE ProductCategory IN ('''' + @CatList + '''')
(Returns Nothing)
SELECT p.ProductCode<br />
FROM Product p<br />
JOIN ProductCategory pc<br />
ON p.ProductCatID = pc.ProductCatID<br />
WHERE ProductCategory IN ('Hardware','Custom')
(Returns
ProductCode
HM10WH2700
HM10WH3000
HM10WH3600
...)
Cheers for any advise,
The Cool Code goes inside the box
|
|
|
|
|
Your variable @CatList is being intrepreted as a literal string, so your query is looking for any ProductCategory values that match the string 'Hardware', 'Custom' as opposed to ProductCategory values that happen to be 'Hardware', or 'Custom'
Looks like you need to rethink how you are going to be querying your tables if you are trying to filter for those values.
|
|
|
|
|
try this ...
DECLARE @CatList VARCHAR(100)<br />
DECLARE @strSQL VARCHAR(500)<br />
<br />
SET @CatList = ''''+ 'Hardware' + '''' + ',' + '''' + 'Custom' + ''''<br />
PRINT @CatList<br />
<br />
SET @strSQL = 'SELECT p.ProductCode<br />
FROM Product p<br />
JOIN ProductCategory pc ON p.ProductCatID = pc.ProductCatID <br />
WHERE ProductCategory IN (' + @CatList + ')'<br />
<br />
PRINT @strsql<br />
EXECUTE (@strSQL)
Regards
KP
|
|
|
|
|
Krish - KP wrote: DECLARE @CatList VARCHAR(100)
DECLARE @strSQL VARCHAR(500)
SET @CatList = ''''+ 'Hardware' + '''' + ',' + '''' + 'Custom' + ''''
PRINT @CatList
SET @strSQL = 'SELECT p.ProductCode
FROM Product p
JOIN ProductCategory pc ON p.ProductCatID = pc.ProductCatID
WHERE ProductCategory IN (' + @CatList + ')'
PRINT @strsql
EXECUTE (@strSQL)
Thanks Krish - As I thought, simple. 
The Cool Code is Inside the Box
|
|
|
|
|
Hi friends,
I have a two columns as unitprice and quantity in salesreport table. I am constructing new column by multplying this two column. I gave the name of sales for this new column. I want to ge average of sales column.
I tried below code but program says the column name of sales is invalid.
select unitprice,quantity,unitprice*quantitiy as sales from salesreport
where sales>avg(sales)
|
|
|
|
|
Please don't cross post.
"Real programmers just throw a bunch of 1s and 0s at the computer to see what sticks" - Pete O'Hanlon
|
|
|
|
|
select unitprice, quantity, (unitprice*quantitiy) as sales<br />
from salesreport<br />
where (unitprice*quantitiy)> (select avg(unitprice*quantitiy) from salesreport)
|
|
|
|
|
Hi,
How can I can encrypt and decypt the data and stored in a databse. If anyone knows how to do it. Please tell me.
Rock Star
|
|
|
|
|
Hi,
If you are using VS 2005 then you can use classes of System.Security.Cryptography namespace. For eg. you can use TripleDESCryptoServiceProvider to encrypt and decrypt data programatically and store it in database.
Regards,
Ujjaval Modi
Manpower moves wrenches, horsepower moves cars, and the power of the mind moves the world.
|
|
|
|
|
Thanks
Do you hav any link for it so i can get some more information about it.
Rock Star
|
|
|
|
|
Use the Cryptography classes like Ujjaval said. Google should turn up quite a bit in terms of examples.
"Real programmers just throw a bunch of 1s and 0s at the computer to see what sticks" - Pete O'Hanlon
|
|
|
|
|
in the stored procedure , generally it is said that these are faster then the queries which we write directly into the code behind(asp.net code file).
i know stored procedure helps in preventing the SQL Query Injections.
Now my Question is how stored procedure are faster then the query which we write directly, I mean what's the background scene behind this.
Yesterday is a canceled check. Tomorrow is a promissory note. Today is the ready cash. USE IT.
|
|
|
|
|
Stored Procedure are complied during the creation of it. But your Queries that you write in the ASP page gets compiled at the time the page executes.there may chance of errors in the quries that u write dynamically.
But SP all the quries are compiled.
If U Get Errors U Will Learn
If U Don't Get Errors U Have Learnt
|
|
|
|
|
Sonia Gupta wrote: generally it is said that these are faster then the queries which we write directly into the code behind(asp.net code file).
It's not
Sonia Gupta wrote: i know stored procedure helps in preventing the SQL Query Injections.
NO. Parameterized queries prevents SQL Injection. Stored Procedures are not for this.
Sonia Gupta wrote: Now my Question is how stored procedure are faster then the query which we write directly, I mean what's the background scene behind this.
SP's are not faster that normal queries if it does only Insert/Delete/Update. Advantages I found with SP's are
1 - Clean separation of queries from code
2 - Easy maintainability
3 - Easy to write long transact SQL statements
4 - Security can be controlled
|
|
|
|
|
Sonia Gupta wrote: what's the background scene
Stored Procedures are checked for syntax errors, parsed, compiled and the execution plan is stored in the SQL Server's cache for subsequent execution. They are Precompiled collection of Transact-Sql Statements where as in the case inline-sql the statements are dynamically checked for syntax errors, then parsed, compiled and execution plan is prepared at the time of execution. These sqeuence of action happens every-time you execute a inline-sql where as in stored procedure it happens only once and the execution plan is Reused. This is the reason why Stored Procs are Faster than the inline-SQL's.
- Regards -
JON
Life is not measured by the amount of breaths we take, but by the moments that take our breath away.
|
|
|
|
|
John Prabhu (J O N) wrote: These sqeuence of action happens every-time you execute a inline-sql where as in stored procedure it happens only once and the execution plan is Reused. This is the reason why Stored Procs are Faster than the inline-SQL's.
All the queries executed will be cached and re-used. It's not only for SP's.
John Prabhu (J O N) wrote: compiled and the execution plan is stored in the SQL Server's cache for subsequent execution. They are Precompiled collection of Transact-Sql Statements where as in the case inline-sql the statements are dynamically checked for syntax errors, then parsed, compiled and execution plan is prepared at the time of execution.
SQL server won't keep the execution plan in cache when SP's are created. It will check for the syntactical errors, but it's not precompiled. For both AD_HOC and SP's, execution plan would be prepared at the time of execution. And SQL server can retain all execution plans for all queries, not just SP's.
John Prabhu (J O N) wrote: his is the reason why Stored Procs are Faster than the inline-SQL's.
Still I don't think they are faster that inline-SQL's. It depends on the scenario where you use them. If you are using SP's for just Insert/Update/Delete, I don't think there would be drastic performance boost.
|
|
|
|
|
N a v a n e e t h wrote: Still I don't think they are faster that inline-SQL's.
I really don't understand what you are saying. it's you who taught me about the SQL Server performance when using Stored Procedures. Do you remember the article discussion we had few months ago. Check This[^]. You suggested me to read some Database Journals on Sql Server performance and went through some...this [^].
- Regards -
JON
Life is not measured by the amount of breaths we take, but by the moments that take our breath away.
|
|
|
|
|
My apologies. Recently I found that my thinking was not correct. You may Check this[^].
|
|
|
|
|
Thanks for sharing.
- Regards -
JON
Life is not measured by the amount of breaths we take, but by the moments that take our breath away.
|
|
|
|
|
As Navaneeth says, SQL Server stored procedures are optimised no differently from other, ad-hoc queries. However, the resulting query plan is given more weight so it is more likely still to be in the cache when the procedure is next executed.
You must be a bit careful that you do not have massively divergent behaviour depending on different values for parameters. The reason for this is that SQL Server will use the previously compiled query plan regardless of the parameters supplied. If a query plan works well for some values of some parameters but not for others - perhaps an index which is very selective for some values but not for others, such as where a column mostly contains the same value but has a few exceptions - you can get better performance by asking SQL Server to recompile the query. Use WITH RECOMPILE with the EXEC statement to do this on a one-off basis, or specify WITH RECOMPILE in the CREATE PROCEDURE/ALTER PROCEDURE statement to force the plan to be recompiled every time.
DoEvents: Generating unexpected recursion since 1991
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
