22,209
社区成员
发帖
与我相关
我的任务
分享
下面是一段基本asp语句
<%
'通过接收用户输入,获取查询参数值
name= trim(request("name"))
sex = trim(request("sex"))
age = trim(request("age"))
depart = trim(request("depart"))
telphone = trim(request("telphone"))
city = trim(request("city"))
。。。。
%>
<%
'根据获取的参数值构造完sql语句
sql=select * from dtable where CONTAINS(name,'""" & name &"""')" and CONTAINS(city,'""" & city&"""')" 。。。。。
'由于用户输入的条件不定,所以sql是不一样的,但是基本的构造是这样的,并总是可以获得正确的sql语句
%>
USE MASTER
GO
IF OBJECT_ID('dbo.Proc_Test','P') IS NOT NULL
DROP PROC dbo.Proc_Test
GO
CREATE PROC dbo.Proc_Test
@name NVARCHAR(50),@create_date DATETIME
AS
BEGIN
SET NOCOUNT ON
DECLARE @sql NVARCHAR(MAX)
SET @sql='SELECT * FROM sys.tables AS t WHERE 1=1'
IF ISNULL(@name,'')!=''
BEGIN
SET @sql=@sql+' and t.name like ''%''+@name+''%'' '
END
IF @create_date IS NOT NULL
BEGIN
SET @sql=@sql+' and t.create_date <= @create_date '
END
EXEC sp_executesql @sql,N'@name nvarchar(50),@create_date datetime',@name,@create_date
END
GO
--测试1:@name 有值
EXEC dbo.Proc_Test @name='fall',@create_date=NULL
--测试2:@create_date 有值
EXEC dbo.Proc_Test @name='',@create_date='2016-01-01'
--测试3:两参数均有值
EXEC dbo.Proc_Test @name='sp',@create_date='2015-01-01'
DECLARE @tablenname VARCHAR(100)--='sysrscols'
SELECT OBJECT_NAME(s.[object_id]),* FROM sys.[columns] AS s WHERE OBJECT_NAME(s.[object_id])=isnull(@tablenname,OBJECT_NAME(s.[object_id]))