¶¯Ì¬SQL
Sample1:
/* Variable Declaration */
DECLARE @EmpID AS SMALLINT
DECLARE @SQLQuery AS NVARCHAR(500)
/* Build and Execute a Transact-SQL String with a single parameter value Using EXECUTE Command */
SET @EmpID = 1001
SET @SQLQuery = 'SELECT * from tblEmployees WHERE EmployeeID = ' + CAST(@EmpID AS NVARCHAR(10))
Print @SQLQuery
EXECUTE(@SQLQuery)
Sample2:
* Using sp_executesql - Example 1.1 */
/* Variable Declaration */
DECLARE @EmpID AS SMALLINT
DECLARE @SQLQuery AS NVARCHAR(500)
DECLARE @ParameterDefinition AS NVARCHAR(100)
/* Build and Execute a Transact-SQL String with a single parameter value Using sp_executesql Command */
SET @EmpID = 1001
SET @SQLQuery = 'SELECT * from tblEmployees WHERE EmployeeID = @EmpID'
/* Specify Parameter Format */
SET @ParameterDefinition = '@EmpID SMALLINT'
EXECUTE sp_executesql @SQLQuery, @ParameterDefinition, @EmpID
Sample3:
ALTER Procedure sp_EmployeeSelect
@EmployeeName NVarchar(100),
@Department NVarchar(50),
@Designation NVarchar(50),
@StartDate DateTime,
@EndDate DateTime,
@Salary Decimal(10,2),
@Total INT OUTPUT
AS
Set NoCount ON
Declare @SQLQuery AS NVarchar(4000)
Declare @ParamDefinition AS NVarchar(2000)
Set @SQLQuery = 'Select * from tblEmployees where (1=1) '
If @EmployeeName Is Not Null
Set @SQLQuery = @SQLQuery + ' And (EmployeeName = @EmployeeName)'
If @Department Is Not Null
Set @SQLQuery = @SQLQuery + ' And (Department = @Department)'
If @Designation Is Not Null
Set @SQLQuery = @SQLQuery + ' And (Designation = @Designation)'
If (@StartDate Is Not Null) AND (@EndDate Is Not Null)
Set @SQLQuery = @SQLQuery + ' And (JoiningDate BETWEEN @StartDa
Ïà¹ØÎĵµ£º
create PROCEDURE pagelist
@tablename nvarchar(50),
@fieldname nvarchar(50)='*',
@pagesize int output,--ÿҳÏÔʾ¼Ç¼ÌõÊý
@currentpage int output,--µÚ¼¸Ò³
@orderid nvarchar(50),--Ö÷¼üÅÅÐò
@sort int,--ÅÅÐò·½Ê½£¬1±íʾÉýÐò£¬0±íʾ½µÐòÅÅÁÐ
......
ϵͳ»·¾³£ºWindows 7
Èí¼þ»·¾³£ºVisual C++ 2008 SP1 +SQL Server 2005
±¾´ÎÄ¿µÄ£º±àдһ¸öº½¿Õ¹ÜÀíϵͳ
ÕâÊÇÊý¾Ý¿â¿Î³ÌÉè¼ÆµÄ³É¹û£¬ËäÈ»³É¼¨²»¼Ñ£¬µ«ÊÇ×÷ΪÎÒÓÃVC++ ÒÔÀ´±àдµÄ×î´ó³ÌÐò»¹ÊÇ´«µ½ÍøÉÏ£¬ÒÔ¹©²Î¿¼¡£ÓÃVC++ ×öÊý¾Ý¿âÉè¼Æ²¢²»ÈÝÒ×£¬µ«Ò²²»ÊDz»¿ÉÄÜ¡£ÒÔÏÂÊÇÎҵijÌÐò½çÃæ£¬ºóÃæ ......
Case¾ßÓÐÁ½ÖÖ¸ñʽ¡£¼òµ¥Caseº¯ÊýºÍCaseËÑË÷º¯Êý¡£
--¼òµ¥Caseº¯Êý
CASE sex
WHEN '1' THEN 'ÄÐ'
WHEN '2' THEN 'Å®'
ELSE 'ÆäËû' END
--CaseËÑË÷º¯Êý
CASE WHEN sex = '1' THEN 'ÄÐ'
WHEN sex = '2' THEN 'Å®'
ELSE 'ÆäËû' END
ÕâÁ½ÖÖ·½Ê½£¬¿ÉÒÔʵÏÖÏàͬµÄ¹¦ÄÜ¡£¼òµ¥Caseº¯ÊýµÄд·¨Ïà ......
--1:ÎÞORDER BYÅÅÐòµÄд·¨¡£(ЧÂÊ×î¸ß)
--(¾¹ý²âÊÔ£¬´Ë·½·¨³É±¾×îµÍ£¬Ö»Ç¶Ì×Ò»²ã£¬ËÙ¶È×î¿ì£¡¼´Ê¹²éѯµÄÊý¾ÝÁ¿ÔÙ´ó£¬Ò²¼¸ºõ²»ÊÜÓ°Ï죬ËÙ¶ÈÒÀÈ»£¡)
SELECT *
from (Select ROWNUM AS ROWNO, T.*
from k_task T &s ......
Êý¾Ý²ã´ÎµÄ¸ÅÄî:
Êý¾Ý²ã´ÎÊDZí´ïÊý¾ÝµÄÒ»ÖÖÖØÒª¹ØÏµ£¬ÔÚÊý¾Ý¿âµÄÉè¼ÆÖУ¬È磺×éÖ¯½á¹¹·Ö½â¡¢¹¤×÷ÈÎÎñ·Ö½â¡¢ÐÐÕþÇø»®µÄ·Ö½âµÈ¶¼ÊDzã´Î¹ØÏµÊý¾ÝµÄµäÐÍʵÀý¡£
±í´ï²ã´Î¹ØÏµµÄÊý¾ÝÒ»°ãÐèҪʵÏÖÈçÏÂÊôÐÔ£º
1.²ã´ÎµÄ×î´ó¼¶Áª²ã´ÎÊý¡£È磺Öйú£>ºþÄÏÊ¡->³¤É³ÊÐ->Óê»¨Çø£¬¾ÍÊÇ4²ã¡£
2.ÄÜ·´Ó³Í¬Ò»² ......