--创建一个函数,用来分拆字符串(引用邹建的)
CREATE FUNCTION f_splitSTR(
@s varchar(8000), --待分拆的字符串
@split varchar(10) --数据分隔符
)RETURNS @re TABLE(Dept_Code varchar(100))
AS
BEGIN
DECLARE @splitlen int
SET @splitlen=LEN(@split+'a')-2
WHILE CHARINDEX(@split,@s)>0
BEGIN
INSERT @re VALUES(LEFT(@s,CHARINDEX(@split,@s)-1))
SET @s=STUFF(@s,1,CHARINDEX(@split,@s)+@splitlen,'')
END
INSERT @re VALUES(@s)
RETURN
END
GO--创建存储过程,用来执行楼主要求
CREATE PROCEDURE sp1(
@s varchar(8000),
@date varchar(10)
)
AS
SELECT * FROM tbl_Acc
WHERE Dept_Code IN (select Dept_Code from f_splitSTR(@s,',')) AND datediff(d,From_Date, cast(@date as datetime))=0
GO
--调用时
exec sp1 '0101,0102,0103','2005-12-14'
CREATE FUNCTION f_splitSTR(
@s varchar(8000), --待分拆的字符串
@split varchar(10) --数据分隔符
)RETURNS @re TABLE(Dept_Code varchar(100))
AS
BEGIN
DECLARE @splitlen int
SET @splitlen=LEN(@split+'a')-2
WHILE CHARINDEX(@split,@s)>0
BEGIN
INSERT @re VALUES(LEFT(@s,CHARINDEX(@split,@s)-1))
SET @s=STUFF(@s,1,CHARINDEX(@split,@s)+@splitlen,'')
END
INSERT @re VALUES(@s)
RETURN
END
GO--创建存储过程,用来执行楼主要求
CREATE PROCEDURE sp1(
@s varchar(8000),
@date varchar(10)
)
AS
SELECT * FROM tbl_Acc
WHERE Dept_Code IN (select Dept_Code from f_splitSTR(@s,',')) AND datediff(d,From_Date, cast(@date as datetime))=0
GO
--调用时
exec sp1 '0101,0102,0103','2005-12-14'
CREATE FUNCTION dbo.f_splitSTR(
@s varchar(8000), --待分拆的字符串
@split varchar(10) --数据分隔符
)RETURNS @re TABLE(Dept_Code varchar(100))
AS
BEGIN
DECLARE @splitlen int
SET @splitlen=LEN(@split+'a')-2
WHILE CHARINDEX(@split,@s)>0
BEGIN
INSERT @re VALUES(LEFT(@s,CHARINDEX(@split,@s)-1))
SET @s=STUFF(@s,1,CHARINDEX(@split,@s)+@splitlen,'')
END
INSERT @re VALUES(@s)
RETURN
END
GO--创建存储过程,用来执行楼主要求
CREATE PROCEDURE sp1(
@s varchar(8000),
@date varchar(10)
)
AS
SELECT * FROM tbl_Acc
WHERE Dept_Code IN (select Dept_Code from dbo.f_splitSTR(@s,',')) AND datediff(d,From_Date, cast(@date as datetime))=0
GO
--调用时
exec sp1 '0101,0102,0103','2005-12-14'
@DeptIdAll varchar(6000),
@dDate datetime
AS
EXEC('SELECT * FROM tbl_Acc WHERE Dept_Code IN ('+@DeptIdAll+') AND FROM_Date='''+CONVERT(VARCHAR(10),@dDate,120)+'''')
GOexec pro_GetAcc '''0101'',''0102'',''0103''','2005-12-14'
@DeptIdAll varchar(6000),
@dDate datetimeAS
SELECT * FROM tbl_Acc
WHERE charindex('#'+cast(Dept_Code as varchar)+'#',@DeptIdAll)>0 AND convert(varchar(8),FROM_Date,120) =@dDateGO
exec '#0101#0102#0103#','2005-12-14'
insert into tb1_Acc select '0101','2005-12-14' union all
select '0102','2005-12-14' union all
select '0102','2005-12-14' create proc pro_GetAcc
@DeptIdAll varchar(6000),
@dDate datetime
as
select * from tb1_Acc
WHERE Dept_Code IN (@DeptIdAll) AND convert(varchar(10),from_date,120) = @dDate
--在VB中
'****************************************************************
' 連接SQL Server
'****************************************************************
Dim strServer As String
Dim strUID As String
Dim strPWD As String
Dim strDBName As String
Dim strConString As String strServer = MyGetSetting("服務器名", "服務器名", "")
strUID = MyGetSetting("用戶名", "用戶名", "")
strPWD = MyGetSetting("密碼", "密碼", "")
strDBName = MyGetSetting("數據庫名", "數據庫名", "")
strConString = "Driver={SQL Server};Network Library=TCP/IP Sockets;SERVER=" & strServer & ";UID=" & strUID & ";PWD=" & strPWD & ";DATABASE=" & strDBName & ""
DBEnv.DBConn.CommandTimeout = 30000
DBEnv.DBConn.Open strConString
Dim rs As ADODB.Recordset
Dim sql As String
sql = "exec pro_GetAcc @DeptIdAll='0101',@dDate='2005-12-14'"
Set rs = DBEnv.DBConn.Execute(sql)
Set DataGrid1.DataSource = rs