tsql

Extending a stored procedure (adding a single part to it) by using a parameter in SQL Server

I have a 200 line long stored procedure, which gets a parameter 'prmtr', What I want to do is add an "sql part" to my stored procedure, according to my parameter. example: SELECT A.* FROM ( SELECT * FROM table1 ) A IF (my parameter) = a LEFT JOIN ( SELECT * FROM table2 ) B ON A.ID= B.ID ...

How to write this T-SQL WHERE condition?

I've got two tables: TableA Col1 Col2 TableB Col3 Col4 I want to join them together: SELECT * from TableA join TableB ON (...) Now, in place of ... I need to write an expression that evaluates to: If Col3 is not null, then true iif Col1==Col3; otherwise If Col3 is null, then true iif Col2==Col4 What would be the...

TSQL SELECT then UPDATE in one transaction, then return SELECT

I am really having trouble with a query in my ColdFusion application (backended to MS SQL 2008). I keep getting DB deadlock errors on this transaction: <code> <cftransaction> <cfquery name="selectQuery"> SELECT TOP 20 item_id, field2, field3 FROM Table1 WHERE subject_id = #subject_ID# AND lock_field IS NULL AND ...

Update table on matching text type of data

I have following table I want to update its TownId from following table on matching TownId. The desired result of Table 1 is ...

Is there a simple way to create a unique integer key from a two-integer composite key?

For various reasons that aren't too germane to the question, I've got a table with a composite key made out of two integers and I want to create a single unique key out of those two numbers. My initial thought was to just concatenate them, but I ran into a problem quickly when I realized that a composite key of (51,1) would result in the...

sql simple query

hey yesterday my friend ask me question select * from user where 1=1 I said this is wrong query but he told me this is true i dont understand it how is it true please explain me how this condition works where 1 = 1? ...

What is wrong with this SQL?

The SQL is: execute ('delete from HttpRequests where Date < ''2009-08-' + convert(nvarchar(max), 0) + '''') The error is Msg 156, Level 15, State 1, Line 1 Incorrect syntax near the keyword 'convert'. Commenting out the convert part removes the error. Whats wrong? ...

Error storing the result of select for xml into an xml variable (TSQL)

I am having trouble storing the result of a "select for xml" into an xml variable. I am trying to do something like this: declare @m xml select @m=(select value from MyTable for xml auto) select @m as m If MyTable contains only few records then anything is fine but, when MyTable contains more records (Ex:4700) the result is empty. It'...

Referring to column values directly without using variables in T-SQL

Is there a way in T-SQL (SQL Server 2005) to assign a whole record to a record variable and then refer to the particular values using column names? I mean, instead of: select @var1 = col1, @var2 = col2 from mytable where ID = 1; and referring to them as @var1 and @var2, something like @record = select col1, col2 from mytable...

Pagination in SQL Server

How do i limit the result of a query (in my case about 60K rows) and select only from the X row to the Y row? If I use ROW_NUMBER() I don't like my query because it involves 2 select queries .. one to return the rows and one to select the portion I need Update: Here's the query I use now: SELECT * FROM ( SELECT row_numb...

Maintain ordering of characters if there is no id (SQL Server 2005)

I have the following Chars A C W B J M How can I insert some sequential numbers so that after insertion of the numbers the order of characters will not change? I mean if I use row_number(), the output Character order is changing like select ROW_NUMBER() over(order by chars) as id, t.* from @t t Output: id chars 1 A 2 B ...

any way to simplify this LIKE wildcard expression in T-SQL, without resorting to CLR?

I have a column of database names like so: testdb_20091118_124925 testdb_20091119_144925 testdb_20091119_145925 ect... Is there a more elegant way of returning only similar records then using this like expression: select * from sys.databases where name LIKE 'testdb[_][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][_][0-...

SQL Server: One Table with 400 Columns or 40 Tables with 10 Columns?

I am using SQL Server 2005 Express and Visual Studio 2008. I have a database which has a table with 400 Columns. Things were (just about manageable) until I had to perform bi-directional sync between several databases. I am wondering what arguments are for and against using 400 column database or 40 table database are? The table in...

select query in sql server 2008

I have two tables create table tblchildinfo (id int, name varchar(50), pickuppointid int, dropdownpointid int) and create table tblpoint(pointid int, PointName varchar(50)) So I have primary table tblpoint and child table as tblchildinfo and i want to write a statement such that i will get child id, child name, pickuppointID...

simplest way to do recursive t-sql for multiple selects

Hi all, I am developing a Bill Of Materials cost calculator program and I am struggling to fathom a simple solution to some recursive selects I want. I am using SQL Server 2005 for this part of the application. Say I have Product A, which contains assembly B, and Part C. Assembly B will contain parts D and E, but, here is where I s...

Convert number to varchar in SQL with formatting

Is there a way in T-SQL to convert a TINYINT to VARCHAR with custom number formatting? For instance, my TINYINT has a value of 3 and I want to convert it to a VARCH of 03, so that it always shows a 2 digit number. I don't see this ability in the CONVERT function. ...

Getting rowcount of a common table expression CTE into a parameter for paging

My friend here is coding a web page with a datagrid that has paging. We were able to get the total pagecount into a column as a window function but we can't figure out how to get it into a parameter. Seeing the code will make more sense: DECLARE @StartRow INT DECLARE @EndRow INT DECLARE @PerPage INT DECLARE @PageNumber i...

Select rows that are different in SQL

I have a table with way too many columns and a couple million rows that I need to query for differences. On these rows there will hopefully be only one column that is different and that should be the Auto incremented id field. What I need to do is check to see if these rows ARE actually the same and if there are any that have any diffe...

SQL Copy Row for a list, change one column value

I need to duplicate a row a couple thousand times. I need to change one column from the copied row based on a list of ids. Psuedo-code: INSERT INTO MyTable (TabID, Other Columns) VALUES (TabID = (SELECT TabID FROM OtherTable WHERE ParentID = 1), Other Columns) Is this doable? ...

I need to retrive last of these results? (T-SQL)

This query: SELECT refPatient_id,actDate,refReason_id,refClinic_id,active FROM PatientClinicHistory WHERE refClinic_id = 24 GROUP BY refPatient_id,actDate,refReason_id,refClinic_id,active ORDER BY refPatient_id,actDate returns this result: refPatient_id actDate refReason_id refClinic_id active ============= ...