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
...
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...
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
...
I have following table
I want to update its TownId from following table on matching TownId.
The desired result of Table 1 is
...
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...
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?
...
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?
...
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'...
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...
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...
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
...
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-...
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...
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...
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...
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.
...
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...
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...
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?
...
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
============= ...