子查詢
子查詢意指巢狀結構存在於SELECT、INSERT、UPDATE、DELETE敘述中的SELECT查詢。子查詢可在巢狀子查詢 ，子查詢為join關聯資料子查詢與無關聯資料使用in(某段SELECT資料來源)，在效能表現上關連子查詢會較無 關聯子查詢表現較優。
-- 使用子查詢來檢查是否符合
--關連查詢
SELECT DISTINCT s.PurchaseOrderNumber
FROM Sales.SalesOrderHeader s
WHERE EXISTS ( SELECT SalesOrderID
FROM Sales.SalesOrderDetail
WHERE UnitPrice BETWEEN 1000 AND 2000 AND
SalesOrderID = s.SalesOrderID)
--無關連子查詢
SELECT SalesPersonID,
SalesQuota CurrentSalesQuota
FROM Sales.SalesPerson
WHERE SalesQuota IN
(SELECT MAX(SalesQuota)
FROM Sales.SalesPerson)
倧齊 發表在 痞客邦 留言(0) 人氣(27,398)
If the results table of your stored proc is too complicated to type out the "create table" statement by hand, and you can't use OPENQUERY OR OPENROWSET, you can use sp_help to generate the list of columns and data types for you. Once you have the list of columns, it's just a matter of formatting it to suit your needs.
Step 1: Add "into #temp" to the output query (e.g. "select [...] into #temp from [...]").The easiest way is to edit the output query in the proc directly. if you can't change the stored proc, you can copy the contents into a new query window and modify the query there.
Step 2: Run sp_help on the temp table. (e.g. "exec tempdb..sp_help #temp")After creating the temp table, run sp_help on the temp table to get a list of the columns and data types including the size of varchar fields.
Step 3: Copy the data columns & types into a create table statementI have an Excel sheet that I use to format the output of sp_help into a "create table" statement. You don't need anything that fancy, just copy and paste into your SQL editor. Use the column names, sizes, and types to construct a "Create table #x [...]" or "declare @x table [...]" statement which you can use to INSERT the results of the stored procedure.
Step 4: Insert into the newly created tableNow you'll have a query that's like the other solutions described in this thread.
DECLARE @t TABLE
(
--these columns were copied from sp_help
COL1 INT,
COL2 INT
)
INSERT INTO @t
Exec spMyProc This technique can also be used to convert a temp table (
#temp) to a table variable (
@temp). While this may be more steps than just writing the
create table statement yourself, it prevents manual error such as typos and data type mismatches in large processes. Debugging a typo can take more time than writing the query in the first place.
If you're lucky enough to have SQL 2012 or higher, you can use dm_exec_describe_first_result_set_for_object
I have just edited the sql provided by gotqn. Thanks gotqn.
This creates a global temp table with name same as procedure name. The temp table can later be used as required. Just don't forget to drop it before re-executing.
declare @procname nvarchar(255) = 'myProcedure',
@sql nvarchar(max)
set @sql = 'create table ##' + @procname + ' ('
begin
select @sql = @sql + '[' + r.name + '] ' + r.system_type_name + ','
from sys.procedures AS p
cross apply sys.dm_exec_describe_first_result_set_for_object(p.object_id, 0) AS r
where p.name = 'myProcedure'
set @sql = substring(@sql,1,len(@sql)-1) + ')'
execute (@sql)
execute('insert ##' + @procname + ' exec ' + @procname)
end
倧齊 發表在 痞客邦 留言(6) 人氣(2,645)
在遞迴時，有用到foreach
之前也習慣使用
結果看了幾篇文
平行運算 (一)：Parallel.For、Parallel.Foreach 用法及技巧
倧齊 發表在 痞客邦 留言(8) 人氣(2,197)
名稱：SQLite Database Browser
網站：http://sqlitebrowser.sourceforge.net/
分類：桌面程式（支援Win/Mac/Linux）
介紹：用Qt開發的Sqlite 小程式，功能相當簡單，但對於一般的資料庫建立和測試的確是夠用了。
截圖：
倧齊 發表在 痞客邦 留言(0) 人氣(1,920)
<HTML>
<HEAD>
<TITLE> Add/Remove dynamic rows in HTML table </TITLE>
<SCRIPT language="javascript">
function addRow(tableID) {
var table = document.getElementById(tableID);
var rowCount = table.rows.length;
var row = table.insertRow(rowCount);
var cell1 = row.insertCell(0);
var element1 = document.createElement("input");
element1.type = "checkbox";
element1.name="chkbox[]";
cell1.appendChild(element1);
var cell2 = row.insertCell(1);
cell2.innerHTML = rowCount + 1;
var cell3 = row.insertCell(2);
var element2 = document.createElement("input");
element2.type = "text";
element2.name = "txtbox[]";
cell3.appendChild(element2);
}
function deleteRow(tableID) {
try {
var table = document.getElementById(tableID);
var rowCount = table.rows.length;
for(var i=0; i<rowCount; i++) {
var row = table.rows[i];
var chkbox = row.cells[0].childNodes[0];
if(null != chkbox && true == chkbox.checked) {
table.deleteRow(i);
rowCount--;
i--;
}
}
}catch(e) {
alert(e);
}
}
</SCRIPT>
</HEAD>
<BODY>
<INPUT type="button" value="Add Row" onclick="addRow('dataTable')" />
<INPUT type="button" value="Delete Row" onclick="deleteRow('dataTable')" />
<TABLE id="dataTable" width="350px" border="1">
<TR>
<TD><INPUT type="checkbox" name="chk"/></TD>
<TD> 1 </TD>
<TD> <INPUT type="text" /> </TD>
</TR>
</TABLE>
</BODY>
</HTML>
倧齊 發表在 痞客邦 留言(0) 人氣(3,571)
祈求平順的生活 (2)