SQL Server :: Setting Up The CASE Keyword In SQL From Web App?
Mar 7, 2011
I was wondering how I would go about setting up the CASE keyword in SQL from my Web app. I have a table where I need to update anywhere between 20 and 300 rows with different values. My table looks like this
ID EventID SeatType SeatCount Status Comments
On my page, these rows are listed in a table with a textarea to add comments. So when I click a button I need to update All the comments for all the users of a certain "EventID".I think my SQL Statement should go something Like this:
UPDATE Events
SET Comments =
CASE [code]....
Is that correct? I would be getting the ID and comment from each row of the table. Also (using C#) what would be the most efficient way to get this information into the query? I was thinking to use a for statement but I hear many string concatenations requires lots of processing?
I would like to set up a keyword search with and/or wildcards which searches across a rather large tables with around 10,000 rows and growing.What would be the best to set this up ? I heard a lot about indexing etc, im using a SQL2005 DB.And I do not wish to purchase 3rd party software.
This is a yellow page which is not user friendly and we didn't expect . I'm wondering setting customeError in webconfig doesn't support this type of address or not ? How can i prevent users seeing this yellow page . Edit : solution you mentioned are about configuring IIS ,But as i mentioned earlier , my site has been published on shared hosting provider . I don't have those access at IIS , What should i do at this situation ?
I have my project set up with the virtual path "/MyVirtualPath", create the virtual directory in IIS 6 (W2003) and everything works fine. Then to work better with Google Analytics I change the virtual path "/myvirtualpath" and change all redicecciones and links to lowercase. also applies the class "lowercase route urls in aspnet mvc" and works perfectly.
The problem I had to modify the virtual directory in IIS, delete virtual path "/MyVirtualPath" and I created the new "/myvirtualpath", but in all cases I use tilde "~" or where I make a "RedirecToAction" (which should take "LowercaseRoute"), continues to maintain the virtual path "/MyVirtualPath". For example, if I see the HTML source code in the browser, see "/MyVirtualPath/Content/Site.css" instead of "/myvirtualpath/Content/Site.css. "
I am searching for products out of my database: AND (CONTAINS(P.name, @contains_key) OR CONTAINS(P.description, @contains_key) OR P.sku like '%' + @Keywords + '%')
As you can see I'm using the CONTAINS statement and searching against the name and description fields.I would like to give the results a weight (or score) based on how often the word shows up and in what field.Example:if the word shows up in the name field -- give it 2 points every time the words appears.if the word shows up in the description field -- give it 1 point every appearance.Then ORDER the query based on the Score. And, perhaps add a where clause that the Score > 1 or some value.I realize, I will probably have to re-write my query and this "point" system may not be possible, but I know there is a way to weight results, but can't find any help online. I would also assume, this would be done in temp tables and select statements and directly in the where clause.
Now, I want to select all unique locationid's from a single username, its important that the results can be PAGED and SORTED, I now have:
SELECT * FROM (select ROW_NUMBER() OVER (ORDER BY createdate) as RowNum, distinct(locationid), l.* FROM location_views lv INNER JOIN locations l on l.id=lv.locationid where lv.username='myname@hotmail.com' ) as info WHERE RowNum > 0 AND RowNum <= 100
But this throws the error: "Incorrect syntax near the keyword 'distinct'."
I have been working on a stored procedure to calculate likert scales for course evaluations. I have the stored procedure done but I ran into an interesting but frustrating situation.
I used a case statement along with a select query to count the number of responses of a given value. Likert scales are usually 5 point scales 5 being the highest and 1 being the lowest. The value that gave me the trouble was null values. In my evaluation page the instert query puts a null value in the field instead of leaving the response blank. These are the two queries I used both are syntactically correct but one works and the other doesn't. #1 Null query that works
[Code]....
Can anyone explain the differences and why one works but the other doesn't? Can it be as simple as switching the WHEN and the column name and if it is would it be advisable the other ones around?
I have below sql statement wich don't work. It don't come up with an error but votepercentage is 0 and when I calculate it manually it should come up with
Dog = 5/9*100 = 55,55 Cat: 4/9*100 = 44,44 in votepercentage
My code:
[Code]....
The result for above code:
Dog 5 0 Cat 4 0
I triple checked that questionAnswersCount is 9 Anyone who can tell me what is wrong with this line:
(CASE WHEN pa.answersCount = 0 THEN 0 ELSE Convert(decimal, ((pa.answersCount/pq.questionAnswersCount) * 100)) END) as votepercentage
I have a [Permission] and [Unit] table (permissions types are to view, update, add, ... records). One of the [Unit]'s is "1" for "ALL Units". What I want to do is use either a CASE or IF statement in my view which will pair every [UnitId] (except "1" ofcourse) if/when the [UnitId] is set to "1".
I am having trouble with my ORDY By Case Statement. It doesn't seem to be ording correctly. To see it in action, please go to [URL] and click on "latin" and it should change the sort order and when you click back on "common" it should change it back but it doesn't. Here's my code:
I created a function and am trying to access it through a case statement, but I am getting the error:
Cannot find either column "core" or the user-defined function or aggregate "core.f_FiscalYear_Open_Days", or the name is ambiguous.
The case statement is below:
CASE WHEN c.program_id = 5 and CAST(minutes) As float)/60 >= @MiddleSyTargetHrs /core.f_FiscalYear_Open_Days(c.program_id,Org_Site_ID_Contract,'7-01-2009','7-31-2009') Then 1 Else 0 End As mon_1_hrs
I am working on an Asp.net webiste.The response time(in case of a postback) of the pages in the website is ok on my local machine.But when I uploaded the same website to the internal development server and tried to access the server pages from my local machine,its taking bit long(in case of a postback) as compared to my local machine.I do not understand why the same webiste runs slowly on the dev server..
Following is the configuration of dev server.
Microsoft Windows Server 2003 R2 Enterprise Edition service pack 2 Intel(R) Xeon(R) CPU E5450 @ 3.00GHZ 3.23 GHZ,2.00 GB of RAM Physical Address extension
Following is the configuration of my local system
Microsoft Windows XP Professional Version 2002 service pack 2 Intel(R) Pentium(R) Dual CPU E2180 @ 2.00 GHZ 2.00GHZ,1.99 GB of RAM.
I'm working with ASP.Net web services and am having a problem with a long-running process that takes about 5 minutes to complete, and it's timing out. To fix this, I was able to set the executionTimeout on the server's web.config to 10 minutes, and then set the .Timeout property on the Web Service object to approximately 9 minutes. Now, I'm worried that this may possibly cause some other web service calls to sit there for 10 minutes before they time out rather than the previous 90-100 seconds. I know the default on the client side is 100 seconds, but wasn't sure if updating the server's timeout setting would affect this.
Bottom line is - Is it safe to update the server's timeout setting to a long amount like 10 minutes, and rely on the default timeout on the client, or could this end up causing some problems?
i am writing storedprocedure for creating salary table ,but getting error while executing sp.
USE [Crystal.HRM] GO /****** Object: StoredProcedure [dbo].[Hrm_Salary] Script Date: 01/19/2011 13:11:02 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO
CREATE PROCEDURE [dbo].[Hrm_Salary]( @Pay_Basic char(1), @Pay_Hra char(1) ) AS BEGIN declare @fields nvarchar(max) set @fields='Hrm_Emp_Id [int] not null' if(@Pay_Basic='Y') Begin set @fields=@fields+', Pay_Basic [int] NOT NULL CONSTRAINT [DF_Hrm_Emp_Salary_Pay_Basic] DEFAULT ((0)),' end if(@Pay_Hra='Y') Begin set @fields=@fields+', Pay_Hra [smallint] NOT NULL CONSTRAINT [DF_Hrm_Emp_Salary_Pay_Hra] DEFAULT ((0)),' End set @fields=substring(@fields,0,len(@fields)-2) declare @sql nvarchar(max) set @sql='CREATE TABLE [dbo].[Salary]('+ @fields+' CONSTRAINT [PK_Payroll] PRIMARY KEY CLUSTERED ( [Pay_Emp_Id] ASC )WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY] ) ON [PRIMARY] GO USE [Crystal.HRM] GO ALTER TABLE [dbo].[Hrm_Emp_Salary] WITH CHECK ADD CONSTRAINT [FK_Hrm_Emp_Salary_Hrm_Emp_Tbl] FOREIGN KEY([Pay_Emp_Id]) REFERENCES [dbo].[Hrm_Emp_Tbl] ([Emp_Id])' exec sp_ExecuteSql @sql END
i am getting error 'Incorrect syntax near the keyword 'FETCH'. ' while creating trigger.pls give solution for this error
SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO CREATE TRIGGER Hrm_Emp_Leave_Insert_Data ON Hrm_Leave_Lookup AFTER INSERT AS BEGIN DECLARE @EMP_ID int DECLARE @LEAVE_ID int DECLARE @LEAVE_TOTAL tinyint DECLARE @LEAVE_CARRY_DAYS tinyint select @LEAVE_ID=Leave_Id, @LEAVE_TOTAL=Leave_Total, @LEAVE_CARRY_DAYS=Leave_Carry_Days from inserted DECLARE SelectCursor CURSOR FOR select Emp_Id from Hrm_Emp_Tbl where Emp_Status='A' OPEN SelectCursor FETCH NEXT from SelectCursor into @EMP_ID while @@FETCH_STATUS=0 BEGIN insert into hrm_emp_leave (@EMP_ID, @LEAVE_ID, @LEAVE_TOTAL, @LEAVE_CARRY_DAYS) FETCH NEXT from SelectCursor into @EMP_ID END CLOSE SelectCursor DEALLOCATE SelectCursor END
i have 3 tables to inner join to get the values. employee_id, employee_name from employee table emplyee_assessment, manager_assessment from Appraisal table. status from objective table
set ANSI_NULLS ON set QUOTED_IDENTIFIER ON go ALTER PROCEDURE [dbo].[sp_selectemployeeapprisaldetails] @employee_name Varchar(30) =null, @years Varchar(30) =null, @designation Varchar(30) =null, @status Varchar(30) =null AS BEGIN
--Set NULL when variable is NULL or length is zero if @employee_name is not null and len(@employee_name)=0 set @employee_name = null if @years is not null and len(@years)=0 set @years = null if @designation is not null and len(@designation)=0 set @designation = null If @status = 'Select' Set @status = null select employee.employee_id, employee.employee_name, Appraisal.years, Appraisal.emplyee_assessment, Appraisal.manager_assessment, objective.status from employee INNER JOIN ON (objective INNER JOIN ON Appraisal ON objective.objective_id = Appraisal.objective_id) ON employee.employeeid = Apprasial.employee_id WHERE employee.emplyee_id = Appraisal.employee_id and Appraisal.objective_id = objective.objective_id and objective.status = 'Active' END
but i am getting errors like 1) Incorrect syntax near the keyword 'ON'.
