I have an MS-SQL Server which keeps track on a number of clients. If the birthday is known it is stored as a datetime attribute called dayOfBirth. Now I would like to have another attribute age, which keeps track of the current age of the client. Since age can change any day I figured a script might be the best idea. First thing I did, ...
In conjunction with the fn_split function, I'm returning a list of results from a table based on comma separated values. The Stored Procedure T-SQL is as follows: SELECT ProductCode, ProductDesc, ImageAsset, PriceEuros, PriceGBP, PriceDollars, replace([FileName],' ','\_') as [filename], ID as FileID, weight from Products LEFT O...
With SQL Server 2005 and above, how can I check when a user's password is going to expire using TSQL? Preferably this would be done using a connection for that same user. What permissions would be required for the SQL statement to be run? ...
Is it possible to find out runtime using t-sql if a type (e.g. nvarchar or int) is fixed-length or not by querying some system-table? The reason I need to do this is that I need to generate sql-code runtime and need to generate some declarations (DECLARE @foo SOMETYPE(LENGTH) or DECLARE @foo SOMETYPE) depending on the type of some colum...
declare @test varchar(20) set @test = 'VALUE' exec(' select '+@test+' ') This returns: Invalid column name 'VALUE'. Is there an alternate method to display the variable value on the select statement? ...
I have the following statement in a stored procedure. I am passing the name of the column as parameter and also the value to be checked in another variable. Is it possible to accomplish this in SQL server. Please let me know. SELECT CaseId FROM app_Case where @SearchCat=@keywords ORDER BY CreatedDate DESC ...
I have a TSQL SELECT that can return a null. I tried using ISNULL to replace it with 0 but for some reason it is not working. The table selecting from has the following columns: storeID --> int penaltyPercent --> decimal(9,2) penaltyDate --> dateTime SELECT ISNULL(penaltyPercent, 0.0) AS penaltyPercent FROM dbo.Penalty...
Can somebody help me figure out why the sql statement doesn't like the following line ' and ' + @SearchCat + 'like '%'+@Keywords+'%''. It has to do with the number of single quotes but I can't figure out. How do the quotes work. What's the logic? DECLARE @strStatement varchar(550) declare @state as varchar(50) declare @district as varc...
What is the ideal way to check if a database exists on a SQL Server using TSQL? It seems multiple approaches to implement this. ...
I have a real mystery with the T-SQL below. As it is, it works with either the DATAP.Private=1, or the cast as int on Right(CRS,1). That is, if I uncomment the DATAP.Private=1, I get the error Conversion failed when converting the varchar value 'M' to data type int, and if I then remove that cast, the query works again. With the cast ...
Where do I find a complete list of new T-SQL features in sql server 2005 comparing with 2000? A few ones that I know: Pivot, Output, Try..Catch. Anything else? Thank you. ...
I wrote this sql query to search in a table: Select * from TableName where Name Like '%spa%' The table contain these row for example: 1 Space Company. 2 Spa resort. 3 Spa hotel. 4 Spare Parts. 5 WithoutTheKeyword. I want to know how to edit this query so it return the results sorted like this: 2 Spa resort 3 Spa hotel 1 Space Compa...
How to use FREETEXTTABLE with the table that has composite primary key? E.G. Table(FirstID int, SecondID int, Text ntext) Primary Key(FirstID, SecondID) FREETEXTTABLE returns something that is similar to (or maybe is a) GUID and Rank. How to join this "rank" table with the original one? ...
This is related to Floor a date in SQL server, but I'm taking it one step further: I'm writing a query on a SQL Server table and I want to get all records for the current calendar year and the previous calendar year. So, right now, I'd want a query to return all records between January 1st, 2008 and today. But come January 1st, 2010, I ...
I have been using this method to filter my queries: Create PROCEDURE [dbo].[pGetTask] @showCompletedTasks bit = 1 ,@showInProgressTasks bit = 1 ,@taskID int = null ,@projectID int = null ,@applicationID int = null ,@clientID int = null ... Snip ... where a.clientID = isnull(@clientID, a.clientID) an...
I have several MS Access queries (in views and stored procedures) that I am converting to SQL Server 2000 (T-SQL). Due to Access's limitations regarding sub-queries, and or the limitations of the original developer, many views have been created that function only as sub-queries for other views. I don't have a clear business requirement...
In my table, I have a nullable bit column (legacy system...) and another developer recently made a change to a stored procedure to only show values where the bit column was not true (1). Because this is a nullable column, we noticed that if the column was NULL, the record was not being picked up. WHY is this? Both the other develope...
I have a table with 3 fields like this: ProfessorID StudentID Mark P1 S1 9 P1 S2 8 P1 S3 10 P2 S1 7 P2 S2 2 P2 S3 4 P3 S4 5 P4 S1 6 ...
I have created a user defined function to gain performance with queries containing 'WHERE col IN (...)' like this case: SELECT myCol1, myCol2 FROM myTable WHERE myCol3 IN (100, 200, 300, ..., 4900, 5000); The queries are generated from an web application and are in some cases much more complex. The function definition looks like this:...
I have two SqlDataSource controls on a page. One loads high level data and the other loads more details based on which high level item you choose. It is a part of a large search that has over 900,000 records and I am looking for ways to speed it up. Whether it is options I can add onto the SqlDataSource, things I can do to the sql que...