-- creating the store procedure
IF EXISTS (SELECT name
FROM sysobjects
WHERE name = N'<procedure_name, sysname, proc_test>'
AND type = 'P')
DROP PROCEDURE <procedure_name, sysname, proc_test>
GOCREATE PROCEDURE <procedure_name, sysname, proc_test>
<@param1, sysname, @p1> <datatype_for_param1, , int> = <default_value_for_param1, , 0>,
<@param2, sysname, @p2> <datatype_for_param2, , int> OUTPUT
AS
SELECT @p2 = @p2 + @p1
GO-- =============================================
-- example to execute the store procedure
-- =============================================
DECLARE <@variable_for_output_parameter, sysname, @p2_output> <datatype_for_output_parameter, , int>
EXECUTE <procedure_name, sysname, proc_test> <value_for_param1, , 1>, <@variable_for_output_parameter, sysname, @p2_output> OUTPUT
SELECT <@variable_for_output_parameter, sysname, @p2_output>
GO
IF EXISTS (SELECT name
FROM sysobjects
WHERE name = N'<procedure_name, sysname, proc_test>'
AND type = 'P')
DROP PROCEDURE <procedure_name, sysname, proc_test>
GOCREATE PROCEDURE <procedure_name, sysname, proc_test>
<@param1, sysname, @p1> <datatype_for_param1, , int> = <default_value_for_param1, , 0>,
<@param2, sysname, @p2> <datatype_for_param2, , int> OUTPUT
AS
SELECT @p2 = @p2 + @p1
GO-- =============================================
-- example to execute the store procedure
-- =============================================
DECLARE <@variable_for_output_parameter, sysname, @p2_output> <datatype_for_output_parameter, , int>
EXECUTE <procedure_name, sysname, proc_test> <value_for_param1, , 1>, <@variable_for_output_parameter, sysname, @p2_output> OUTPUT
SELECT <@variable_for_output_parameter, sysname, @p2_output>
GO
解决方案 »
- KmaiNDfLGKYVRlVepd4lwg== 这是什么意思“?
- 请教VisualStudio2010中附带的SQL如何使用链接语句
- sql server如何进行数据库同步
- create table中的小问题,3ks
- 如何替换一张表的几个字段?
- SQL语句:怎样暂停;停止;启动服务管理器?
- 请问各位大侠,为什么VB.NET 2003不能运行VB.NET 2002的程序出错呢?
- 有没有办法不安装SQL SERVER就可以直接用DTSRUN执行包,谢谢!
- 【请教】请教一个SQL语句,关于职位和公司的查询
- 我想学习编数据库应用程序,是该在这里拜个老师呢?还是去报个学习班?
- kill客户端查询分析器的进程后,可否让查询分析器重新连接
- Cannot insert explicit value for identity column in table 'iepb02h' when IDENTITY_INSERT is set to OFF.
存储过程:
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[CE_LIST_PRT]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[CE_LIST_PRT]
GOSET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
create proc dbo.[CE_LIST_PRT]as
SET NOCOUNT ON
SELECT IDENTITY(INT,1,1) AS PR_SN,PR_NO,PR_NAM,PR_TYPE INTO #TT
FROM CE_PRT Order By PR_NO
select * from #TT ;
RETURN 0GO
页面调用
<%
...
set cn = Server.CreateObject("ADODB.Connection")
set comm = Server.CreateObject("ADODB.Command")
set RcSet = Server.CreateObject("ADODB.RecordSet")
'cn.open gScriptSQL_test
'SET CN = GetSQLServerConnection( gSQLServerName, gSQLACCName, gSQLPassword, gSQLDBName)
set cn=GetSQLServerConnection( gSQLServerName, gSQLACCName, gSQLPassword, gSQLDBName)
set comm.ActiveConnection = cn
comm.CommandType = 4
comm.CommandText = "dbo.CE_LIST_PRT"
RcSet.cursorlocation = adUseClient
set RcSet = comm.Execute--付给一个数据集,
do until RcSet.eof
response.write RcSet("PR_SN") &"<br>"
RcSet.movenext
loop
...
%>
1、where中使用like这将采用全表扫描,性能低下
2、order by也是一个很耗资源的语句。
如果你的表数据多,你的sql语句肯定很耗资源了