|
Sorry actually already given space its not showing that error...,
its giving error in ">" symbol space...,
Magi
|
|
|
|
|
Magh_M wrote: else
((datediff(Month,dbo.tblEmployeeMaster.joinDate,@BDate))>=12 )
Chnage the above line to below
when ((datediff(Month,dbo.tblEmployeeMaster.joinDate,@BDate))>=12 )<br />
then
You have missed 'when' keyword as you are checking a condition
Hope you got it..
Harini
|
|
|
|
|
then its showing error:156 incorrect syntax near when like....,
Magi
|
|
|
|
|
Hi
I dont understand what could be the problem... update it in your code project and let me try again ...
Harini
|
|
|
|
|
Hi
I have modified and pasted here.. Let me know if there is any error:
<br />
CREATE PROCEDURE Bonus<br />
(<br />
@projectid as int,<br />
@BDate as smalldatetime,<br />
@BMonths as tinyint<br />
)<br />
As<br />
declare<br />
@days1 as tinyint<br />
begin<br />
<br />
<br />
SELECT dbo.tblEmployeeMaster.empId, dbo.tblEmployeeMaster.empName, dbo.tblEmployeeMaster.joinDate, <br />
(dbo.tblsalarydetails.BasSalary)+(dbo.tblsalarydetails.Hra)+(dbo.tblsalarydetails.Ca)+(dbo.tblsalarydetails.Ota)+<br />
(dbo.tblsalarydetails.Sha)+(dbo.tblsalarydetails.Allow1)+(dbo.tblsalarydetails.Allow2)+(dbo.tblsalarydetails.Allow3)+<br />
(dbo.tblsalarydetails.Allow4)+(dbo.tblsalarydetails.Allow5) as GTotal,<br />
datediff(Month,dbo.tblEmployeeMaster.joinDate,@BDate) as WMonths,<br />
(<br />
case when ( ((datediff(Month,dbo.tblEmployeeMaster.joinDate,@BDate))>=@BMonths) and <br />
((datediff(Month,dbo.tblEmployeeMaster.joinDate,@BDate))<12 ) <br />
)then<br />
(<br />
(<br />
(dbo.tblsalarydetails.BasSalary)+(dbo.tblsalarydetails.Hra)+(dbo.tblsalarydetails.Ca)+<br />
(dbo.tblsalarydetails.Ota)+(dbo.tblsalarydetails.Sha)+(dbo.tblsalarydetails.Allow1)+<br />
(dbo.tblsalarydetails.Allow2)+(dbo.tblsalarydetails.Allow3)+(dbo.tblsalarydetails.Allow4)+<br />
(dbo.tblsalarydetails.Allow5)<br />
)*datediff(Month,dbo.tblEmployeeMaster.joinDate,@BDate)<br />
)/12 <br />
when<br />
((datediff(Month,dbo.tblEmployeeMaster.joinDate,@BDate))>=12 )<br />
then <br />
<br />
(dbo.tblsalarydetails.BasSalary)+(dbo.tblsalarydetails.Hra)+(dbo.tblsalarydetails.Ca)+<br />
(dbo.tblsalarydetails.Ota)+(dbo.tblsalarydetails.Sha)+(dbo.tblsalarydetails.Allow1)+<br />
(dbo.tblsalarydetails.Allow2)+(dbo.tblsalarydetails.Allow3)+(dbo.tblsalarydetails.Allow4)+<br />
(dbo.tblsalarydetails.Allow5)<br />
else<br />
0 end<br />
) as Bonus FROM dbo.tblEmployeeMaster INNER JOIN dbo.tblsalarydetails ON <br />
dbo.tblEmployeeMaster.empId = dbo.tblsalarydetails.EmpId WHERE (dbo.tblEmployeeMaster.projectId = @projectid)<br />
<br />
end<br />
GO
Harini
|
|
|
|
|
ya its working nicely....,
But i also did same given what u said "when and then"...,
But in that time its shown syntax error near "when"...,
Anyway now its working good...,
Thanks lot...,
Keep in touch.........,
Magi
|
|
|
|
|
|
Hi
Iam using xp_smtp_sendmail procedure to send mails in SQL 2000. Now I want to know how to receive an acknowledgement when the recipient receive the mail.
Thanks
|
|
|
|
|
You don't. Unless the recpient answers "Yes" to the little question about sending a receipt back to your mailbox, you'll never know they got the message.
The return receipt will go back to the mailbox in the "From" field you set when you sent the message. SQL Server doesn't have a POP3 client, so you'll have to check your mailboxd from an email app, or write your own code to check the mailbox.
|
|
|
|
|
I use a lot of comments in my stored procedures to document my code for future developers that might have to modify / debug it.
However, when I view my stored procedure script or pick the "modify" option in SQL Server Management Studio the code comes up with blank lines where my comments are supposed to be.
I see other stored procedures that have retained thier comments (although they are much smaller in size) using the same "--" single line comment that I use.
Anyone know why that is happening?
Thanks,
Ron
|
|
|
|
|
Nevermind, I figured it out.
The query tool I am using ( WinSQL - very handy tool ) has an option to "Parse Comments Locally" which was turned on - so it was removing the comments *before* sending the to SQL Server to save.
Ron
|
|
|
|
|
Hello!
I am developing a .NET 2.0 solution (C#) using ADO.NET
I have a question regarding the escape sequences in SQL. Consider the following query
SELECT * FROM ClauseTranslations WHERE Clause LIKE 'trainee's cost'
Now when executed, this query generates an exception. Very well. The problem with with the
single quote symbol. so what I do, I write like this:
SELECT * FROM ClauseTranslations WHERE Clause LIKE 'trainee\'s cost'
But this still gives error... What the way out?
Regards,
Mohsin
Polite Programmer
More Object Oriented then C#
|
|
|
|
|
replace with
-SELECT * FROM ClauseTranslations WHERE Clause LIKE 'trainee''s cost'
the error occured because of 's
so,u better replace ''s instead of 's
|
|
|
|
|
when executing a query from c# application it is better to use parametrized query and there won't be any problems with escape characters.
|
|
|
|
|
|
Hi Mohsin,
I don't think SQL syntax supports escape characters like that. To use an embedded single quote in a query like your example, you put two single quotes in the string:
SELECT * FROM ClauseTranslations WHERE Clause LIKE 'trainee''s cost'
Ron
|
|
|
|
|
That way SQL Injection Attacks lie....
Rather than escape characters, use parameterised queries.
|
|
|
|
|
Good advice, but it doesn't answer his question.
*If* he is accepting user input to build the query, then SQL Injection attacks are an issue.
If the entire query as written is part of his internal code, there is no danger.
Ron
|
|
|
|
|
Ron Savage wrote: If the entire query as written is part of his internal code, there is no danger.
That's untrue.
There is the possibility of a Second Order Attack. This is where supposedly clensed data that is already sitting in the database can be used to form an attack. All the data used is internal to the system at the time the SQL is formed, but the threat is just as real.
|
|
|
|
|
Colin Angus Mackay wrote: There is the possibility of a Second Order Attack. This is where supposedly clensed data that is already sitting in the database can be used to form an attack. All the data used is internal to the system at the time the SQL is formed, but the threat is just as real.
Another excellent bit of advice, yet again completely outside the context of the discussion.
Neither the original question nor my response involved building a query from any external data. Without the inclusion of external text that may at some point have been entered by an end user of the program - SQL Injection attacks are not an issue.
Ron
|
|
|
|
|
Ron Savage wrote: Another excellent bit of advice, yet again completely outside the context of the discussion.
Is it?!
Ron Savage wrote: Neither the original question nor my response involved building a query from any external data.
Who's to say where the data came from? The OP certainly didn't. So any discussion on the matter is pure speculation without a definitive answer from the OP. As such, it becomes important that the issue of SQL Injection is raised... just in case.
Ron Savage wrote: Without the inclusion of external text that may at some point have been entered by an end user of the program - SQL Injection attacks are not an issue.
Hardcoding fully formed SQL statements into one's program is hardly common. The more common scenario is that the statement contains some variable data. As such, in case the OP is giving a simplified example (which is common) it "yet again" becomes important to raise the issue of SQL Injection Attacks.
|
|
|
|
|
How we can store the backup in SQL ?
Pramod
|
|
|
|
|
pramodprakash2005 wrote: How we can store the backup in SQL ?
I don't understand your question
Do you want to store the back up of your SQL Server database somewhere?
Do you want to store the back up of something else in SQL Server?
|
|
|
|
|
Hi guy, I've heard of many different ways of connecting to database like config file, text file and ... I'd like to know which of them all is the best and safest !
TX
Mr.K
|
|
|
|
|
It depends on the situation.
I generally put the connection string in the config file - And I use a trusted connection, so the user name and password are not exposed.
|
|
|
|