tsql

SQL Conditional Order By

I am doing a join on two tables. One is a user's table, and the other a list of premium users. I need to have the premium members show up first in my query. However, just because they are in the premium user table doesn't mean they are still a premium member - there is an IsActive field that also needs to be checked. So basically I need...

Using Variables in SQL 2008 to return more than one record

I'm pretty sure this is not the right way to do this so I'm looking for some suggestions. I don't think my problem so much is that I'm trying to solve a spatial problem. I'm just not sure of a good way to take the latitude and longitude and return a geography data type in a select statement. I have successfully created a geography col...

How can I determine whether or not a stored procedure is recompiling every time?

I've got the a SQL Server stored procedure with the following T-SQL code contained within: insert into #results ([ID], [Action], [Success], [StartTime], [EndTime], [Process]) select 'ID' = aa.[ActionID], 'Action' = cast(aa.[Action] as int), 'Success' = aa.[Success], 'StartTime' = aa.[StartTime], 'EndTime' = aa.[EndTime], 'Process'...

Escaping Bracket [ in a CONTAINS() clause?

How can I escape a bracket in a full-text SQL Server contains() query? I've tried all the following, none of which work: CONTAINS(crev.RawText, 'arg[0]') CONTAINS(crev.RawText, 'arg[[0]]') CONTAINS(crev.RawText, 'arg\[0\]') Using double quotes does work, but it forces the entire search to be a phrase, which is a showstopper for multip...

datatype supplied by user for SQL field

I have a SQL table which has a number of fields ID | Value | Type A typical record may be :- 1000,10,[int] a second row may be:- 1001,foo,[string] a third row may be:- 1002,10/12/2008,[DateTime] I have been asked to look at this as at the moment, each time we wish to select from this table we have to cast the value to the type spe...

Reduce deadlock on PAGE level on update query on MS SQL

I have some funny deadlock caused by a stupid simple SQL UPDATE query, on a flat plain table, under default "READ COMMITED" transaction. UPDATE table SET column=@P1 WHERE PK=@P2; While PK varchar(11), has a clustered index on it. no trigger or table relation..etc on the table. I did some check and find that the deadlock happen ...

"select * from table" vs "select colA,colB,etc from table" interesting behaviour in SqlServer2005

Apology for a lengthy post but I needed to post some code to illustrate the problem. Inspired by the question What is the reason not to use select * ? posted a few minutes ago, I decided to point out some observations of the select * behaviour that I noticed some time ago. So let's the code speak for itself: IF EXISTS (SELECT * FROM...

Testing TRY...CATCH in SQL Server Management Studio

Solution: I've misinterpreted the example from SQL Books Online. Yes, that below the section Errors Unaffected by a TRY…CATCH Construct ... I'm so sorry :( I was just trying to execute this simple example in SQL Server Management Studio: USE AdventureWorks; GO BEGIN TRY -- Table does not exist; object name resolution --...

Best way to check for current date in where clause of sql query.

I'm trying to find out the most efficient (best performance) way to check date field for current date. Currently we are using: SELECT COUNT(Job) AS Jobs FROM dbo.Job WHERE (Received BETWEEN DATEADD(d, DATEDIFF(d, 0, GETDATE()), 0) AND DATEADD(d, DATEDIFF(d, 0, GETDATE()), 1)) ...

Table of SQL Errors & Stored Procedures..

I have this bit of code I found on the web at the end of each of my stored procedures: ROLLBACK TRANSACTION PRINT '-----START: ERROR DETAILS-----' PRINT ERROR_NUMBER() PRINT ERROR_SEVERITY() PRINT ERROR_STATE() PRINT ERROR_PROCEDURE() PRINT ERROR_LINE() PRINT ERROR_MESSAGE() PRINT '-----E...

Insert statement generated using SQLParameter and datetime.now()

Hi there I have a SQL Datetime field in sql 2000. I have a stored proc called addPresentation. From my .Net code I add a sqlparameter as:- new SqlParameter("Date", SqlDbType.DateTime).Value=DateTime.now When I call the stored proc from code and observe sql profiler it is attempting to perform the insert as :- exec addPresentation @Na...

T-SQL: How to make cell-values unique?

If I have a table where the cells in a column should not have the same values, how do I check this and update? (I know I can set constraints in the settings, but I don't want to do that.) Say the column name is called unique hash name and contains Peter Peter Peter Dave Dave and so on. I want that to transform to: Peter Peter1 Pe...

SQL Relationships and indexes

I have an MS SQL server application where I have defined my relationships and primary keys. However do I need to further define indexes on relationship fields which are sometimes not used in joins and just as part of a where clause? I am working on the assumption that defining a relationship creates an index, which the sql engine can r...

Create a DDL trigger in every database on a 2005 instance

I need to create a trigger in every database on my sql 2005 instance. I'm setting up some auditing ddl triggers. I create a cursor with all database names and try to execute a USE statement. This doesn't seem to change the database - the CREATE TRIGGER statement just fires in adventureworks repeatedly. The other option would be to pr...

"GRANT ALL TO role" in SQL Server

Please can someone explain what the following statement does in SQL Server 2005: GRANT ALL TO pax_writer pax_writer is a database role previously created using the statement CREATE ROLE pax_writer AUTHORIZATION dbo ...

TSQL: No value instead of Null

Hi all. Due to weird request, I can't put null in database if there is no value. I'm wondering what can i put in the store proc for nothing instead of null. for example: insert into blah (blah1) values (null). Is there something like nothing or empty for blah1 instead using 'null'. ...

Data not filtering before a join.

A puzzler from a coworker that I cannot figure out... update btd.dbo.tblpayroll set empname = ( select b.Legal_Name from ( SELECT Legal_Name, Employee_ID FROM Com.dbo.Workers WHERE isnumeric(Employ...

Need SQL help - How can I select rows to perform an insert?

I tried to make the title as clear as possible... here is my scenario: I have 2 tables (let's call them table A and table B) that have a similar schema. I would like write a stored procedure that would select specific columns of data out of table A, and insert this data as a new record in table B. Can someone point me in the write dire...

T-SQL varchar comparison question

I'm using MS Sql 2005. Why does this give me the correct results (returns 169 rows)... select * from [import_Data] where [import_Data].name not in ( select [import_Data].name from [import_Data] inner join [resource] on [import_Data].name = [resource].name where [import_Data].Provide...

Simple T-SQL Join question

I am trying to select a record out of the table1 by joining table2 and using table2 columns in the WHERE statement. Col1 and Col2 in table1 can be stored in col1 of table2. I need to join col1 of table2 with either the value of col1 or col2 in table1 here is the sql statement I created so (pseudo): SELECT t1.Col1, t1.Col2, t...