|
DECLARE @Current_SQL varchar(500)
DECLARE @QueryResult int
....
SET @QueryResult = EXEC(@Current_SQL)
I get error: "Incorrect syntax near the keyword 'EXEC'."
My @Current_SQL query like "Select Count(*) From MyTable Where ID = 1"
Haw can I get return value from EXEC metod.
|
|
|
|
|
You could use an output-parameter, somewhat similar to this;
declare @Current_SQL nvarchar(500)
declare @result bigint
set @Current_SQL = 'select @result = count(*) from [MyTable] where [ID] = 1'
execute sp_executesql @Current_SQL, N'@result int output', @result output Good luck
I are Troll
|
|
|
|
|
|
Hi to All Forum members
I am stuck in finding duration and Net duration in the following procedure
I also adding image Url for better under standing of my requirement.
Please go to this Url ..
http://picasaweb.google.com/lh/photo/203UnHt9l8EnouLOtIKxBg?authkey=Gv1sRgCM6puf-sutfJ2QE&feat=directlink[^]
Following is my T-Sql Query...
Select F.Id as [FleetID], Convert(varchar(30), F.AssignedOn, 113) as [Date], D.DepotName as [Depot], DT.DutyID as [Duty Number],
IsNull(Convert(varchar(30), F.ActualOutSheddingTime, 113),'Not Assigned') as [Actual OutShedding Time],
IsNull(Convert(varchar(30), F.ActualInsheddingTime, 113) ,'Not Assigned') as [Actual Inshedding Time],
IsNull(CONVERT(varchar(5), DATEADD(ms, DATEDIFF(ms, F.ActualOutSheddingTime, F.ActualInsheddingTime), 0), 114) ,'00:00')as [Duration],
IsNull(DT.RestHours, 00) as [ShiftChangeoverTime],
IsNull(CONVERT(varchar(5), DATEADD(ms, DATEDIFF(ms, CONVERT(varchar(5), DATEADD(ms, DATEDIFF(ms, F.ActualOutSheddingTime, F.ActualInsheddingTime), 0), 114), Convert(Datetime, '00:'+ IsNull(Convert(varchar(30), DT.RestHours, 114), 30))), 0), 114),'00:00') as [Net Duration]
From Fleet F
Left Outer Join Duties DT on F.DutyID = DT.ID
Inner Join Depot D on DT.DepotId = D.ID
Inner Join Vehicles V on V.ID = F.VehicleID
Welcome for your valuable suggestions.
Vishnu
Vishnu Narayan Mishra
Software Engineer
|
|
|
|
|
Hi
Actually Duration is coming right in this query and the issue is Net Duration is not coming right.
Vishnu Narayan Mishra
Software Engineer
|
|
|
|
|
hi all,
i want to build a query but not succesful
i have two table named A and B
A table has field id(N), title(Varchar)
B table has field mid(N), id(N)
so i want to id, title from A table and count of id from B table
let me explain with data
Table A data
1 title1
2 title2
3 title3
Table B data
1 1
2 1
3 1
4 1
5 2
6 2
7 2
8 3
9 3
so i want to following result
id title count
1 title1 4
2 title2 3
3 title3 2
Please suggest how can build this query?
|
|
|
|
|
Here's the idea how to do this:
SELECT A.id, A.title, COUNT(*) as cnt
FROM A left join B on A.id = B.id
GROUP BY A.id, A.title
|
|
|
|
|
SELECT A.id, A.title, S.total
FROM A
LEFT OUTER JOIN
(
SELECT id, COUNT(id) as total
FROM B
GROUP BY id
) S on S.id = A.id
ORDER BY A.id ASC
|
|
|
|
|
Hi
I have a select query.The test team told me that the query is weak.So how can I make a query stronger in SQL Server.
Thanks
Denny
|
|
|
|
|
Let it lift weights...
Wout Louwers
|
|
|
|
|
They probably refer to strong and weak typing.
Here's[^] an explanation, the rest you can google.
|
|
|
|
|
Go back to the Test team and ask them to explain in detail why they believe that your query is 'weak'.
|
|
|
|
|
Hi,
Can someone please advise me on what to do with this issue.
I have a table that has a DATE, GROSSHOURS, GROSSMINUTES and TOTAL. I have a select query that takes the GROSSHOURS and GROSSMINUTES and add them together in seconds e.g: 1H 30M = 90M
My issue is this:
I have 2 lines on the same day.
2010/01/05 1HOUR 30MINUTES 90MINUTES
2010/01/05 2HOURS 20MINUTES 140MINUTES
How will I write a select statement where I can only display 1 line for 2010/01/05 but with both totals?
2010/01/05 230MINUTES
My statement looks like this at the moment:
Select Id, CONVERT(VARCHAR,EntryDate,111) AS EntryDate, GrossHours, GrossMinutes, DATEDIFF(minute, StartJob, EndJob) / 60 * 60 + GrossMinutes AS Total FROM [Timesheet] GROUP BY Id, EntryDate, GrossHours, GrossMinutes, StartJob, EndJob
Thank you!
Illegal Operation
|
|
|
|
|
You need to remove GrossHours and GrossMinutes from your select statement and group by.
|
|
|
|
|
Hello,
I'm attempting to query a list of tables from an SQLite3 database and have come across the query PRAGMA table_info("table_name") in the SQLite docs.
Here is my code:
OdbcConnection DbConnection;
OdbcDataReader DbReader;
DbConnection = new OdbcConnection("DSN=" + odbcConnName);
DbConnection.Open();
OdbcCommand DbCommand = DbConnection.CreateCommand();
DbCommand.CommandText = ("PRAGMA table_info(bandwidth_records)");
DbReader = DbCommand.ExecuteReader();
while (DbReader.Read())
{
Trace.WriteLine(DbReader["type"].ToString());
}
DbReader.Close();
DbConnection.Close();
However, DbReader.Read() is not returning true . Very weird.
Any ideas?
Thanks,
Matt
|
|
|
|
|
I am not sure what was wrong with your case, but a test on my system returned the correct info.
Are you sure you want to use the PRAGMA commands in your programs? Based on the documentation future versions of SQLite will not guarantee the backward compatability for these PRAGMA commands.
|
|
|
|
|
Hm.. loyal ginger, which SQLite-ODBC driver have you used?
I want it too
|
|
|
|
|
This will not work in SQLite via ODBC (if you use SQLite.Data.Dll everything will be ok)
However, using ODBC you can get table fields too. Here's a sample:
DbConnection.Open();
DataTable dt = DbConnection.GetSchema("Columns");
foreach (var col in dt.Columns) {
Console.Write(col + "\t");
}
foreach (DataRow row in dt.Rows) {
foreach (object field in row.ItemArray) {
Console.Write(field + "\t");
}
Console.WriteLine();
}
|
|
|
|
|
|
DataTable class itself isn't exactly what you need - it just contains the data.
You should look at GetSchema method of DbConnection class. This method returns the metaschema of data for different databases.
|
|
|
|
|
|
Is there a given interface to the ODBC items:
TABLE_CAT
TABLE_SCHEM
TABLE_NAME
COLUMN_NAME
DATA_TYPE
TYPE_NAME
COLUMN_SIZE
BUFFER_LENGTH
DECIMAL_DIGITS
NUM_PREC_RADIX
NULLABLE
REMARKS
COLUMN_DEF
SQL_DATA_TYPE
SQL_DATETIME_SUB
CHAR_OCTET_LENGTH
ORDINAL_POSITION
IS_NULLABLE
to only parse certain "fields?"
Thanks for your time,
Matt
|
|
|
|
|
What do you mean as interface to the ODBC items?
I don't completely understand your question. Please be more concrete, maybe I'll be able to help you.
|
|
|
|
|
I have a view that contains a list transactions, each of which are associated with a single routing number. Multiple transactions can exist for each routing number. Each transaction can be one of two types (a and b). I need to get the routing numbers, the count of transactions for each routing number, and then the count of those that are of type b, and the result set can only include transactions of type b (along with the requisite counts, of course). Can I get some guidance?
.45 ACP - because shooting twice is just silly ----- "Why don't you tie a kerosene-soaked rag around your ankles so the ants won't climb up and eat your candy ass..." - Dale Earnhardt, 1997 ----- "The staggering layers of obscenity in your statement make it a work of art on so many levels." - J. Jystad, 2001
|
|
|
|
|
One way is to have a nested query
select a.[transaction], b.tran_count
from transactiontable A
inner join (select [transaction], count(*) as tran_count from transactiontable group by [transaction]) as b
on a.[trasaction] = b.[transaction];
This is an off-the-cuff answer so it needs some looking into, but should start you down the right path.
|
|
|
|
|