(I don't think I have titled this question correctly - but I don't know how to describe it) Here is what I am trying to do: Let's say I have a Person table that has a PersonID field. And let's say that a Person can belong to many Groups. So there is a Group table with a GroupID field and a GroupMembership table that is a many-to-many...
I have a stored procedure with a number of parameters. I would like to write my query so that it joins with certain tables but only if a particular parameter has a value. Take the following example: I have a Person table. There is also an Address table which holds Person Addresses and a Groups table that holds Person Groups. Both are...
Is it possible, without parsing source, to select a list of all sproc names that insert, update, or delete records? I need to create a TSQL utility script that will do this. Efficiency is not an issue because it will be run only a few times a year (Curse'rs I mean Cursors are ok). Ideally this script would not include updates to temp or ...
I have tried solving this problem by posting other related questions here that focused on parts of the query. However I might as well post the entire thing and see if anyone can help. I have the following tables with the following fields: tblPerson - PersonID, PersonName tblGroup - GroupID, Name tblGroupMembership - PersonID, GroupID ...
This SQL seems complex, is there an easier way to get FirstName, LastName when one or both of the fields can be NULL? SELECT COALESCE(LastName,'')+ CASE WHEN LastName+FirstName IS NOT NULL THEN ', ' END+ COALESCE(FirstName,'') AS Name FROM Person ...
Assume a table definition in SQL Server as follows: CREATE TABLE MyTable ( Id UNIQUEIDENTIFIER NULL, Info VARCHAR(MAX) ) And a query: DECLARE @id UNIQUEIDENTIFIER DECLARE @info VARCHAR(MAX) IF @id IS NOT NULL BEGIN SELECT @info = Info FROM MyTable WHERE Id = @id END In that case, the Visual Studio static code analyz...
I am trying to accomplish the following: SELECT col1, col2 FROM table1 UNION SELECT col2, col3 FROM table2 With the result: col1, col2, col3 1 , 1 , NULL NULL, 1 , 1 The union of the columns and the rows is returned. You could think of it as the UNION equivalent of a FULL OUTER JOIN. The simple answer to this question is: S...
Hello all, Just a general question: Is there a query/command I can pass to SQL Server not to use cache when executing a particularly query? I am looking for a query/command that I can set rather than a configuration setting. Is there no need to do this? ...
Hi, Please let me know how can I find if it is Sunday on given Date? EDIT: From answers another thing comes to my mind: How to find what is first day of week? ...
Can I run a dynamic sql in a transaction and roll back using EXEC: exec('SELECT * FROM TableA; SELECT * FROM TableB;'); Put this in a Transaction and use the @@error after the exec statement to do rollbacks. eg. Code BEGIN TRANSACTION exec('SELECT * FROM TableA; SELECT * FROM TableB;'); IF @@ERROR != 0 BEGIN ROLL...
hi guys, I have following query. declare @Prm_CourseId int declare @Prm_SpecializationId int set @Prm_CourseId=5 set @Prm_SpecializationId=0 declare @WhrStr varchar(500) set @WhrStr = case @Prm_CourseId when 0 then 'e.CourseId is null or e.CourseId is not null' when -1 then 'e.CourseId is null o...
I have a number of type Decimal(8, 2) and have been using Substring to get fractional value. E.g.) declare @val decimal(8, 2), @strVal varchar(10) set @val = 15.80 set @strVal = cast(@val as varchar) select @val, substring(@strVal, charindex('.', @strVal), len(@strVal)) Is there a better way to simply get fractional value, .80 fro...
I need to convert a filetime field value to datetime format through t-sql in SQL Server I have applied following formula to do so which works absolutely fine for positive values (>0). But somehow this formula does not work for negative values and it return null value. SELECT LastModifiedTime, CASE WHEN LastModifiedTime <= 1 THEN...
Is there a way to parse a Google search string to a table variable in T-SQL? By Google search string I mean, including the plus sign (require), minus sign (exclude), and exact phrase (double quotes) operators. For example the following search string: one -two +three "four five" -"six seven" +"eight nine" "ten eleven twelve" Would be ...
Hi all, I want to initialize a DateTime entity to the minimum possible date in TSQL. (as you all know its 1/1/1753) Is there a standard way to represent this date in ASP.NET ? I mean doing something like DateTime.SQLMinValue (this doesn't exist), instead of doing new DateTime(year, month, date) ? If so, please post it, Thanks! ...
Hi, I am trying to write a SQL Server query but have had no luck and was wondering if anyone may have any ideas on how to achieve my query. What i'm trying to do: I have a table with several columns naming the ones that i am dealing with TaskID, StatusCode, Timestamp. Now this table just holds tasks for one of our systems that run ...
How can i exit in the middle of a stored procedure? i have a stored procedure where i want to bail out early (while trying to debug it). i've tried calling RETURN and RAISERROR, and the sp keeps on running: CREATE PROCEDURE dbo.Archive_Session @SessionGUID uniqueidentifier AS print 'before raiserror' raiserror('this is a raised error'...
trying to run the following statement in sql mgmt studio declare @rick as decimal(13,3) @rick = -.5 select bob = abs(@rick) any ideas why this won't work? ...
Is there a way to retrieve all the keys of the newly inserted records when using an INSERT INTO ... SELECT FROM query? ...
If I join table A to table B like this... select A.* from A left outer join B on A.Id = B.aId and @param = 'someValue' and @param does not equal 'someValue', does SQL Server even attempt to match records from table B or is it smart enough to know the condition will never be true? ...