tsql

Filter union result

Hi, Im making select with union. select * from table_1 union select * from table_2 ... Is it possible to filter query result by column values ? thanks for help ...

In SQL Server, intercept and change the value a column is set to

I'm trying to improve a legacy database. One problem is that it's missing a lot of foreign key relationships. In some of the columns involved, the associated application is setting fields to an empty string when it should be setting them to null. What I'd like to do is to intercept any attempt to set that column and replace empty strings...

Using same tables alternatively in IN clause returns different results - I was expecting same!!

I have read two excel sheets in two temp tables, #temp and #temp1 Replacing the tables in IN Clause returns different resutls. I was expecting the same. Can anyone explain? select * from #temp where name in (select comp_name from #temp1) Returns 473 records. select * from #temp1 where comp_name in (select name from #temp) Returns 1...

T-SQL Loop in a stored proc

how do I loop through a comma separated variable using tsql in a stored proc So for instance my list would look like this "1,2,3,4,5,6,7,8,9,10" and I would loop thought this list and made some necessary table insert based on this list ...

Stored Procedure Returning Duplicate Invalid Row

SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO ALTER Procedure [dbo].[spGetQualityReport] ( @providerKey INT ) AS -- declare @providerKey INT -- set @providerKey = 1; --Get Database providerId first DECLARE @realProvId INT; SET @realProvId = (SELECT TOP(1) providerId from providerKeyTranslation where keyVa...

Using double quotes in a T-SQL script fails in SQLCMD, but not SSMO

Executing the following script via sqlcmd fails. However, executing it via ssmo or SQL Server Management Studio works. sqlcmd -S . -d test -i input.sql input.sql: CREATE FUNCTION test() RETURNS @t TABLE ("ID" INT) AS BEGIN RETURN END Even when I put SQL Server Management Studio into sqlcmd mode, it still fails. This i...

What exactly does the T-SQL "LineNo" reserved word do?

I was writing a query against a table today on a SQL Server 2000 box, and while writing the query in Query Analyzer, to my surprise I noticed the word LineNo was converted to blue text. It appears to be a reserved word according to MSDN documentation, but I can find no information on it, just speculation that it might be a legacy rese...

c# How do i use a SqlParameters as table or column names?

How can i use a SQLParameter as as the table name and column name? SELECT * FROM @TableName Where @ColumName=@Value I get errors when i try to do something like that. ...

SQL BETWEEN Operator

Why am I getting '2009' data? What am i doing wrong with the WHERE Clause? SELECT CONVERT(varchar, EventDate, 101) AS EVENTDATE, CONVERT(varchar, ClosedDate, 101) AS CLOSEDDATED, DATEDIFF(Day,EventDate,ClosedDate) AS DiffDate, FROM mytable WHERE (CONVERT(varchar, EventDate, 101) BETWEEN '04/01/2010' AND '04/30/2010')...

SQL - Most performant manner to conditionally exlude a join? (if it's even possible)

If I have the following table structure... Table 1: BlogPost PostId | Name | Text Table 2: Tags TagId | Tag Table 3: BlogPostTag PostId | TagId And the following stored procedure... CREATE PROCEDURE SearchBlogPosts @tagstring nvarchar(max), AS BEGIN DECLARE @searchTags TABLE (Tag varchar(50)); IF @tagst...

Duplicate results returned from query when distinct is used

On a current project at I am needing to do some pagination of results returned from SQL. I have hit a corner case in which the query can accept identifiers as part of the where clause, normally this isn't an issue but in one case we have a single identifier being passed up that has a one to many relationship with one of the tables that t...

SQL CASE statement

Hi Friends, I have the following queries and I want to put them in an sql CASE statement so I would have only one query but I don't know how to do that. Can somebody help me? IF (SELECT EtlLoadId FROM ssislogs.audit.processcontrol WHERE SubjectArea = 'UaqaFacetsImp') > 0 SELECT pc.SrcFileName + ' - '+ CONVERT(VARCHAR(10), pc.[Reco...

Help creating the necessary TSQL for this basic requirement.

I've gotten really rusty with TSQL I'm ashamed to say. Using Entity Framework has made me forget what little TSQL I even knew. And I haven't exactly made very complex queries with Entity Framework either, just never had the need to sad to say. This is one exercise our professor gave us this Friday: "Monica buys a gallon of milk from th...

T-SQL speed comparison between LEFT() vs. LIKE operator

I'm creating result paging based on first letter of certain nvarchar column and not the usual one, that usually pages on number of results. And I'm not faced with a challenge whether to filter results using LIKE operator or equality (=) operator. select * from table where name like @firstletter + '%' vs. select * from table where le...

Performing String parsing with TEXT DataType

Using TEXT datatype in SQL Working on SQL 2000, I would like to perform the following Select @String = SUBSTRING(@String, @idx + DATALENGTH(@firstDelimiter)/ 2,LEN(@String)) NOTE: @String is a type TEXT , @FirstDelimiter is of type Varchar(4). Why am I unable to run the above code? How do I perform the above instead? Is the a...

Adding a reference data to the table column from different table line

I have an event table with following columns: sequence (int) DeviceID (varchar(8)) time_start (datetime) DeviceState (smallint) time_end (datetime) All columns except time_end are populated with the data (my current time_end column is NULL through out the table). What I'd need to do is to populate the time_end column with the event c...

T-SQL for deletions based on tiered datetime

I have a table that essentially stores revisions of certain datasets (i.e. edits back through time if you like). The date of the revision is unimaginatively stored as a datetime field named revision. Each new revision is taken daily, at midnight. If this table were left to populate, there would be a set of rows for every single day, wh...

SQL server simple query

How to select value from a table if it value is not null,and select a default value if not?.I.E: select coalesce(username,'its null') from tb_user where int_id_user = 0 I try to run this query but its not working,i expect to get 'its null' since int_id_user is 0,but it returns NULL. ...

SQL Server 2005 - Incorrect syntax near '/'.

Here's a very easy question for someone :) Trying to update an SQL column with the following: UPDATE [NameOfTable] SET [HtmlContent] = 'a href="/sell-your-boat/"' WHERE HtmlID = 123456 But am getting the following error message: Incorrect syntax near '/'. I know it's because I need to escape the / character but hitting my ...