What datatype is the best to store html content and documents like pdf's, excel files, and word documents. I am currently using ntext, but I am not sure if this is the best datatype to store html content. Also, I currently use FCKEditor to save html content to an ntext field and it stores the markup along with the styles. If I happen ...
Actually, I have the task of finding the employees based on the salary rank. So I used Dense_Rank() and got the answer. Initially I was told to solve in SQL SERVER 2005. Later on they changed the requirement and is saying that the query should run in SQL SERVER 2000 also. I solved that using a while loop. But they are saying that...
When I do this: select col1,case when [pivot1]=1 then '-' else '' end [pivot1], case when [pivot2]=1 then '-' else '' end [pivot2] from (select col1,col2,col3 from tbl) as c pivot (sum(col3) for col2 in ([pivot1],[pivot2]))as pvt Everything works fine. When I do this: select col1,[pivot1],[pivot2] from (select col1,col2,col3 from tb...
Hello everyone, How can I return a table from an UDF that executes a custom query string? Eg: CREATE FUNCTION fx_AdvancedSearch ( @keywords varchar(255), (..... other params ....) ) RETURNS TABLE AS BEGIN DECLARE @SqlQuery varchar(1000) ..... SET @SqlQuery = 'my custom select string previously generated' EX...
I wonder if anyone can help me. I need a tsql function to split a given value such as: 1) 00 Not specified 3) 01-05 Global WM&BB | Operations 2) 02-05-01 Global WM&BB | Operations | Operations n/a I need to get a result like this: cat1 cat1descr cat2 cat2descr cat3 cat3descr ---------------------------------...
I have the following problem: Suppose I have a table with the following fields: [ID] [Start Date] [Status Date] [Status Description] [ID] is not unique, and so I may have: ID Start Date Status Date Status 123 01/01/2009 01/01/2009 Start 123 01/01/2009 01/02/2009 Change 123 01/01/2009 01/03/2009 Change 123 01/01/2009...
I am using SQL Server 2000. I am writing a trigger that is executed when a field Applicant.AppStatusRowID Table Applicant is linked to table Location, table Company & table AppStatus. My issue is creating the joins in my query. When Applicant.AppStatusRowID is updated, I want to get the values from Applicant.AppStatusRowID, Applicant....
What is the difference between: CREATE TABLE #temp ( [ID] INT) INSERT INTO #temp SELECT ... and DECLARE @temp TABLE ( [ID] INT) INSERT @temp SELECT ... in SQL Server 2008? ...
I used to write my EXISTS checks like this: IF EXISTS (SELECT * FROM TABLE WHERE Columns=@Filters) BEGIN UPDATE TABLE SET Columns=VALUES WHERE Columns=@Filters END One of the DBA's in a previous life told me that when I do an EXISTS clause, use SELECT 1 instead of SELECT * IF EXISTS (SELECT 1 FROM TABLE WHERE Columns=@Filters) BEG...
I am reading a book on SQL and it gives me scripts to run to create a database. But I have only SQL Express 10, which doesn't look like has tools to run SQL commands. Is there any way to run the scripts? ...
I have the following example code: create table Details( name varchar(20), age int, weight int, recordDate Datetime) --insert data ..query: SELECT a.name, a.age, a.recordDate, a.weight - (SELECT b.weight FROM Details WHERE b.recordDate = dateadd(dd, -1, a.recordDa...
Commisions (commisionID INT, EmployeeID, amount INT, created Datetime) Sales (saleID INT, EmployeeID, amount INT, created datetime) The summary table: Employee (employeeID, totalCommisions INT, totalSales INT, created DateTime) There can be 0 or more rows per employee in both Commissions and Sales tables. Query#1 The query is to u...
In C# we can write switch(num) { case 0: case 1: // do something; break; case 2: ............ ........... case n: // do something break; default: //do something; break; } How can I achieve the similar kind of stuff in SQL SERVER ? I am not talking about the simple way of writing CASE in SQL SERVER. I am talking about if...
The stored proc below does not display any results with SQL Server 2005. I can take the same instructions and run it as a query and I get results, What am I missing. ALTER PROCEDURE [dbo].[usp_SubtractStops] @p NVARCHAR(1024) = '209 208 207 206 205 204 203 113 297 19 7 12 11 6 232 233 234 235 236 237 273 271 272 210 211 212 213 214 21...
Hi all, I have a storedproc which has multiple select statement. When it is executed in sql server , it returns multiple tables. I need a query which will select a particular table from the storedproc e.g. sp_help. Please help. ...
I have my business-logic in ~7000 lines of T-SQL stored procedures, and most of them has next JOIN syntax: SELECT A.A, B.B, C.C FROM aaa AS A, bbb AS B, ccc AS C WHERE A.B = B.ID AND B.C = C.ID AND C.ID = @param Will I get performance growth if I will replace such query with this: SELECT A.A, B.B, C.C FROM aaa AS A JOIN bbb AS B ...
Hi, I have the following SQL that creates a table and inserts the first row of data without a problem. The default DateTime values are also inserted as expected. CREATE TABLE Jobs ( [Id] int PRIMARY KEY IDENTITY(1,1), [JobName] nvarchar(256) Default 'SomeName', [CreateDate] DateTime DEFAULT GETDATE(), [ModifyDate] ...
Hello, can anybody explain me why this query takes 13 seconds: SELECT Table1.Location, Table2.SID, Table2.CID, Table1.VID, COUNT(*) FROM Table1 INNER JOIN Table2 AS ON Table1.TID = Table2.TID WHERE Table1.Last = Table2.Last GROUP BY Table1.Location, Table2.SID, Table2.CID, Table1.VID And this one only 1 second: D...
I need help in writing query to find the time slot availability for a hall booking system.. details are given below I have a Hall table which stores the hall details and HallBooking table which stores the start and from time of the bookings done.. Hall - HallId - Name HallBooking - HallBookingId - HallId - BookingPersonName - StartDat...
using tsql, sqlserver 2005. I would like insert records from table table2 into an existing table table1 as easily as I could enter it into a new table table1 using: select facilabbr, unitname, sortnum into table1 from table2 Any ideas? ...