tsql

t-sql : Error converting data type varchar to numeric in then statement

Hi, I cannot understand the error because I'm not trying to convert to numeric the variable @FQTROQ. declare @FQTROQ varchar(30) declare @FQNUTQ decimal(6,0) set @FQTROQ = 'R-354' set @FQNUTQ = 100 SELECT ( CASE WHEN (@FQTROQ is not null and @FQTROQ <> '') THEN ( @FQTROQ ) ELSE ( @FQNUTQ ) END ) AS Numero_Troquel Any help? thanks ...

Simple SQL Problem

I have a SQL query that I cant wrap my mind around. I do not have a large amount of sql experience. So I need some help I have a table XXX: Social Security No (SSN). Name. organisation. (Finance/IT) In english what I want is: To select all SSNs and Names in "Finance" where there is a different name for that SSN in "IT". My not work...

Find "missing" records in a bridge/data-intersect table

I'm generating images from an original and storing them in a table. I want a query that will check for gaps. The image sizes are kept in a 3rd table, and each original should have 1 generated image for each record in the type table. Where's what I've got so far: SELECT oi.OriginalImageID, it.ImageTypeID FROM dbo.OriginalImages AS...

Creating SQL Statements to work with both SSMS and SSRS.

I need to set up this question a moment. Let's say you've created a database called ParameterTesting and created a table and populated it like so: IF EXISTS (SELECT 1 FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = 'StarWars' AND TABLE_SCHEMA = 'dbo') BEGIN DROP TABLE dbo.StarWars PRINT 'Dropped Star...

Is there a way to get the Nth index of a specified char in T-SQL? (Without Substring!)

Hello, I am trying to get the index of the Nth character inside a varchar(MAX). Right now I am cutting off the front end of the varchar so I only have to check for the first but With extremely large varchars the process is getting way too slow. I would like to parse through the varchar(max) and just keep track of what index I am at and g...

Is there a way to bind a variable type to column data type?

In Oracle PL/SQL, you can always bind your code variables' type to a specific table.column type like this: myVar TABLE.COLUMN%TYPE So, if you change the column's type, you don't have to go over your code to re-define related var types (of course you'd still need to check you are properly using the variable). Is there a similar way to...

T-SQL creating a dynamic where statement?

I know this is not possible, but is there something that would work? Basically I want the where statement to be dynamic, allowing me to pass it any string, which it will be able to search upon. Declare @search varchar(80) set @search = 'RegionID' Select * from TBL_TripDetails Where @search = '1' Thanks for your answers. After reading...

Use varbinary(max) to store HMTL? SQL Server 2008

Hi All, I am trying to figureout the best solution to store html data in some of my tables and track the history of changes made to that HTML. I read that text, ntext, etc are no longer supported for AFTER Triggers so I was thinking of using the varbinary(max) instead. Has anyone used varbinary to store HTML? I was planning on tracki...

SQL - Only select records from weekends

I need to write a SQL Select statement to select all the records where DateCreated falls on a weekend for the last 4 weeks. I know I can do this by hard-coding the select like the one below. But I was wondering if there was an easier way using T-sql date functions. SELECT * FROM audit_table WHERE DateCreated BETWEEN '07-31-2010 00:00...

sql query performance

I am with a performance problem in my query I'm need some tips for gain performance i tried with the UNION ALL but only won one seconds S ELECT GE.Id, V.Gid, V.EventOn, V.[Type], GE.SiteId, S.[$Refex] SiteRefex, V.Quantity, GE.IsIgnored FROM GoodsEvent GE INNER JOIN (/* 2. SI que gerem Quantity < 0...

how to return multiple rows of a single column separated by a ',' or html break in TSQL?

the sql query : SELECT coloumn1 FROM tablex WHERE id=10 and state='COL' lets assume the above query retuns the following coloumn1 --------- abc def geh ijk i need the result to be like this coloumn1 --------- abc, def, geh, ijk OR coloumn1 --------- abc<br/>def<br/>geh<br/>ijk how can achive a result set like the above? PLZ!h...

SQL Server / 2 select in the same Stored procedure

Here's what I'm trying do to in a single SQL Server procedure: @ID1 int select ID2 from TableA where ID1 = @ID1 (for each selected @ID2) select * from TableB where ID2 = @ID2 Any ideas? ...

SQL Statement Question - How to retrieve records of a table where the primary key does not exist in another table as a foreign key.

I have a table (TableA) where the primary key exists as a foreign key on another table (TableB). I am trying to write a SQL query (I am using SQL Server 2005) that will find all of the records in TableA where the primary key does not exist in TableB as a foreign key (we'll say joining on TableAID). I suspect it will use an outer join, ...

T-SQL CTE Error: Types don't match between the anchor and the recursive part

I get the following error when I try to execute a particular recursive CTE: Msg 240, Level 16, State 1, Line 8 Types don't match between the anchor and the recursive part in column "data_list" of recursive query "CTE". This is nonsense. Each field is explicitly cast to VARCHAR(MAX). Please help me. I've read many answers to this prob...

Find and replace all links in database.

I want to find and replace in all db fields which contains a link in my database?how is that possible? I have more tables but i don't know which tables :),i need to search programmatically. I need to a method,example,GetContainsaLink(dbname) it should returns me tables and which field contains a link, I have already a dictionary map o...

SQL Server Full-Text-Search FREETEXTTABLE search multiple columns

I'm using the below query to return results from a table using Full-Text-Search. In SQL2000 it was only possible to search one or all columns in a table. Is it possible in SQL 2008? I would like to search two tables, Problem and Solution (Both indexed and in the same table): DECLARE @topRank int set @topRank=(SELECT MAX(RANK) FROM FRE...

Store XML in XML type field or using FILESTREAM

Hi All, I am trying to figure out what would be the best method for storing xml documents in the db...if we should use an XML type field or if we should use the newly introduced FILESTREAM? We would have a need to shred those documents for queries and possibly use the FULL TEXT SEARCH to allow for user defined searches on those document...

how to display database records even when cell is null

hi, i'm here again to ask help regarding my problem with the view that i created. On my tblEvents there 8 records but i created the view it only displays 3 records. I'am suspecting that the view doesn't read a null value from my tblEvents. How do i fix this? SELECT dbo.tblUsers.UserID, dbo.tblUsers.UserFirstName + ' UserL...

How to convert second select statement result to CSV String in main select statement ?

How to convert second select statement result to CSV String in main select statement ? assume following SQL statement: SELECT A1,A2, (SELECT aCol FROM Table2) as A3 FROM Table1 Now, I want to convert result of SELECT aCol FROM Table2 to CSV string that show at A3 field. such as : A1Value | A2Value | A3Value1, A3Value2, A3Value3 th...

SQL function to determine the most accurate result

If I have a table like this... create table #words ( id int identity, word varchar(1024) ) insert into #words (word) values ('dock') insert into #words (word) values ('dockable') and i do a LIKE query select id, word from #words where word like '%dock%' Is there a way to tell which result would be the most accurate? ...