下面语句如何将@f,@Step设置为存储过程的输入参数,能够让delphi程序调用并且可以动态的传进输入的参数。请问存储过程应怎样改,具体修改的代码怎样!IF OBJECT_ID('dbo.统计表') IS NOT NULL DROP TABLE 统计表
CREATE TABLE 统计表 (方便面 INT,茶叶 INT,面包 INT,口香糖 INT,牛奶 INT,玻璃杯 INT,可乐 INT,冰糖 INT,牙膏 INT,牙刷 INT,故事书 INT,味素 INT,食盐 INT,榨菜 INT,食醋 INT)
INSERT INTO 统计表
SELECT 1,0,0,0,0,1,0,0,0,1,0,0,1,0,0 UNION ALL
SELECT 1,1,0,0,0,1,0,0,0,0,1,1,0,1,0 UNION ALL
SELECT 0,1,1,1,0,1,1,1,0,1,0,0,1,1,0 UNION ALL
SELECT 1,1,0,0,1,1,0,1,1,0,0,1,1,0,0 UNION ALL
SELECT 0,1,1,0,0,1,0,1,0,0,0,0,1,1,1 UNION ALL
SELECT 1,1,0,0,1,1,0,1,1,0,0,0,0,1,0 UNION ALL
SELECT 1,1,1,0,0,1,0,1,0,0,0,1,1,0,0 UNION ALL
SELECT 0,0,1,0,0,1,0,1,0,0,0,0,1,1,1 UNION ALL
SELECT 1,1,1,0,0,1,0,1,1,0,1,1,0,1,0 UNION ALL
SELECT 1,1,1,1,0,1,1,1,0,1,1,0,1,0,1 UNION ALL
SELECT 0,0,1,1,1,1,0,1,0,1,0,1,1,1,0 UNION ALL
SELECT 1,1,1,1,1,1,0,1,0,1,0,0,1,1,1 IF OBJECT_ID('dbo.Tmp') IS NOT NULL DROP TABLE dbo.Tmp
CREATE TABLE Tmp(id INT,NAME VARCHAR(20))
INSERT Tmp SELECT colid,NAME FROM syscolumns s WHERE id=OBJECT_ID('统计表')
DECLARE @num INT
DECLARE @f FLOAT
DECLARE @sql nVARCHAR(1000)
DECLARE @id VARCHAR(100),@w VARCHAR(100),@wh VARCHAR(100),@c VARCHAR(100)
DECLARE @Step int ,@i INT ,@j INT ,@s INT,@l INT SET @f=0.25
SELECT @num=count(1) FROM tmp SET @i=1
SET @Step=2WHILE @i<=@num
BEGIN
SET @wh=REPLICATE('1,',@Step)
--SELECT @wh
SET @j=@i
WHILE @j<=@num-(@Step-1)
BEGIN
SELECT @l=1,@id='',@w='',@c=null
WHILE @l<=@Step-1
BEGIN
SET @id=@id+LTRIM(@j+@l)+','
SET @l=@l+1
END
SELECT @id=ltrim(@i)+','+@id
SELECT @w=@w+'+ltrim('+NAME+')+'+''',''',@c=ISNULL(@c+',','')+Name FROM tmp WHERE CHARINDEX(','+LTRIM(id)+',',','+@id)>0 ORDER BY id
SET @w=STUFF(@w,1,1,'')
SET @sql='if ((select count(*)*1.0 from 统计表 where '+@w+'='''+@wh+''')/'+LTRIM(@num)+'>' +LTRIM(@f) +') print '''+ @c+''''
EXEC(@sql) SET @j=@j+1
END
SET @i=@i+1
END go
方便面,茶叶
方便面,面包
方便面,玻璃杯
方便面,冰糖
方便面,味素
方便面,食盐
方便面,榨菜
茶叶,面包
茶叶,玻璃杯
茶叶,冰糖
茶叶,味素
茶叶,食盐
茶叶,榨菜
面包,口香糖
面包,玻璃杯
面包,冰糖
面包,牙刷
面包,食盐
面包,榨菜
面包,食醋
口香糖,玻璃杯
口香糖,冰糖
口香糖,牙刷
口香糖,食盐
牛奶,玻璃杯
牛奶,冰糖
玻璃杯,冰糖
玻璃杯,牙刷
玻璃杯,味素
玻璃杯,食盐
玻璃杯,榨菜
玻璃杯,食醋
冰糖,牙刷
冰糖,味素
冰糖,食盐
冰糖,榨菜
冰糖,食醋
牙刷,食盐
食盐,榨菜
食盐,食醋
CREATE TABLE 统计表 (方便面 INT,茶叶 INT,面包 INT,口香糖 INT,牛奶 INT,玻璃杯 INT,可乐 INT,冰糖 INT,牙膏 INT,牙刷 INT,故事书 INT,味素 INT,食盐 INT,榨菜 INT,食醋 INT)
INSERT INTO 统计表
SELECT 1,0,0,0,0,1,0,0,0,1,0,0,1,0,0 UNION ALL
SELECT 1,1,0,0,0,1,0,0,0,0,1,1,0,1,0 UNION ALL
SELECT 0,1,1,1,0,1,1,1,0,1,0,0,1,1,0 UNION ALL
SELECT 1,1,0,0,1,1,0,1,1,0,0,1,1,0,0 UNION ALL
SELECT 0,1,1,0,0,1,0,1,0,0,0,0,1,1,1 UNION ALL
SELECT 1,1,0,0,1,1,0,1,1,0,0,0,0,1,0 UNION ALL
SELECT 1,1,1,0,0,1,0,1,0,0,0,1,1,0,0 UNION ALL
SELECT 0,0,1,0,0,1,0,1,0,0,0,0,1,1,1 UNION ALL
SELECT 1,1,1,0,0,1,0,1,1,0,1,1,0,1,0 UNION ALL
SELECT 1,1,1,1,0,1,1,1,0,1,1,0,1,0,1 UNION ALL
SELECT 0,0,1,1,1,1,0,1,0,1,0,1,1,1,0 UNION ALL
SELECT 1,1,1,1,1,1,0,1,0,1,0,0,1,1,1 IF OBJECT_ID('dbo.Tmp') IS NOT NULL DROP TABLE dbo.Tmp
CREATE TABLE Tmp(id INT,NAME VARCHAR(20))
INSERT Tmp SELECT colid,NAME FROM syscolumns s WHERE id=OBJECT_ID('统计表')
DECLARE @num INT
DECLARE @f FLOAT
DECLARE @sql nVARCHAR(1000)
DECLARE @id VARCHAR(100),@w VARCHAR(100),@wh VARCHAR(100),@c VARCHAR(100)
DECLARE @Step int ,@i INT ,@j INT ,@s INT,@l INT SET @f=0.25
SELECT @num=count(1) FROM tmp SET @i=1
SET @Step=2WHILE @i<=@num
BEGIN
SET @wh=REPLICATE('1,',@Step)
--SELECT @wh
SET @j=@i
WHILE @j<=@num-(@Step-1)
BEGIN
SELECT @l=1,@id='',@w='',@c=null
WHILE @l<=@Step-1
BEGIN
SET @id=@id+LTRIM(@j+@l)+','
SET @l=@l+1
END
SELECT @id=ltrim(@i)+','+@id
SELECT @w=@w+'+ltrim('+NAME+')+'+''',''',@c=ISNULL(@c+',','')+Name FROM tmp WHERE CHARINDEX(','+LTRIM(id)+',',','+@id)>0 ORDER BY id
SET @w=STUFF(@w,1,1,'')
SET @sql='if ((select count(*)*1.0 from 统计表 where '+@w+'='''+@wh+''')/'+LTRIM(@num)+'>' +LTRIM(@f) +') print '''+ @c+''''
EXEC(@sql) SET @j=@j+1
END
SET @i=@i+1
END go
方便面,茶叶
方便面,面包
方便面,玻璃杯
方便面,冰糖
方便面,味素
方便面,食盐
方便面,榨菜
茶叶,面包
茶叶,玻璃杯
茶叶,冰糖
茶叶,味素
茶叶,食盐
茶叶,榨菜
面包,口香糖
面包,玻璃杯
面包,冰糖
面包,牙刷
面包,食盐
面包,榨菜
面包,食醋
口香糖,玻璃杯
口香糖,冰糖
口香糖,牙刷
口香糖,食盐
牛奶,玻璃杯
牛奶,冰糖
玻璃杯,冰糖
玻璃杯,牙刷
玻璃杯,味素
玻璃杯,食盐
玻璃杯,榨菜
玻璃杯,食醋
冰糖,牙刷
冰糖,味素
冰糖,食盐
冰糖,榨菜
冰糖,食醋
牙刷,食盐
食盐,榨菜
食盐,食醋
CREATE TABLE 统计表 (方便面 INT,茶叶 INT,面包 INT,口香糖 INT,牛奶 INT,玻璃杯 INT,可乐 INT,冰糖 INT,牙膏 INT,牙刷 INT,故事书 INT,味素 INT,食盐 INT,榨菜 INT,食醋 INT)
INSERT INTO 统计表
SELECT 1,0,0,0,0,1,0,0,0,1,0,0,1,0,0 UNION ALL
SELECT 1,1,0,0,0,1,0,0,0,0,1,1,0,1,0 UNION ALL
SELECT 0,1,1,1,0,1,1,1,0,1,0,0,1,1,0 UNION ALL
SELECT 1,1,0,0,1,1,0,1,1,0,0,1,1,0,0 UNION ALL
SELECT 0,1,1,0,0,1,0,1,0,0,0,0,1,1,1 UNION ALL
SELECT 1,1,0,0,1,1,0,1,1,0,0,0,0,1,0 UNION ALL
SELECT 1,1,1,0,0,1,0,1,0,0,0,1,1,0,0 UNION ALL
SELECT 0,0,1,0,0,1,0,1,0,0,0,0,1,1,1 UNION ALL
SELECT 1,1,1,0,0,1,0,1,1,0,1,1,0,1,0 UNION ALL
SELECT 1,1,1,1,0,1,1,1,0,1,1,0,1,0,1 UNION ALL
SELECT 0,0,1,1,1,1,0,1,0,1,0,1,1,1,0 UNION ALL
SELECT 1,1,1,1,1,1,0,1,0,1,0,0,1,1,1 IF OBJECT_ID('dbo.Tmp') IS NOT NULL DROP TABLE dbo.Tmp
CREATE TABLE Tmp(id INT,NAME VARCHAR(20))
INSERT Tmp SELECT colid,NAME FROM syscolumns s WHERE id=OBJECT_ID('统计表') CREATE PROC SP_TEST
@f FLOAT ,
@Step int
AS
SET NOCOUNT ON
DECLARE @num INT
--DECLARE @f FLOAT
DECLARE @sql nVARCHAR(1000)
DECLARE @id VARCHAR(100),@w VARCHAR(100),@wh VARCHAR(100),@c VARCHAR(100)
DECLARE --@Step int ,
@i INT ,@j INT ,@s INT,@l INT --SET @f=0.25
SELECT @num=count(1) FROM tmp SET @i=1
--SET @Step=2 WHILE @i <=@num
BEGIN
SET @wh=REPLICATE('1,',@Step)
--SELECT @wh
SET @j=@i
WHILE @j <=@num-(@Step-1)
BEGIN
SELECT @l=1,@id='',@w='',@c=null
WHILE @l <=@Step-1
BEGIN
SET @id=@id+LTRIM(@j+@l)+','
SET @l=@l+1
END
SELECT @id=ltrim(@i)+','+@id
SELECT @w=@w+'+ltrim('+NAME+')+'+''',''',@c=ISNULL(@c+',','')+Name FROM tmp WHERE CHARINDEX(','+LTRIM(id)+',',','+@id)>0 ORDER BY id
SET @w=STUFF(@w,1,1,'')
SET @sql='if ((select count(*)*1.0 from 统计表 where '+@w+'='''+@wh+''')/'+LTRIM(@num)+'>' +LTRIM(@f) +') print '''+ @c+''''
EXEC(@sql) SET @j=@j+1
END
SET @i=@i+1
END
SET NOCOUNT OFF
go
-->调用
EXEC SP_TEST 0.25,2
@f float,@step int
as
begin
DECLARE @num INT DECLARE @sql nVARCHAR(1000)
DECLARE @id VARCHAR(100),@w VARCHAR(100),@wh VARCHAR(100),@c VARCHAR(100)
DECLARE i INT ,@j INT ,@s INT,@l INT
SELECT @num=count(1) FROM tmp SET @i=1
WHILE @i <=@num
BEGIN
SET @wh=REPLICATE('1,',@Step)
--SELECT @wh
SET @j=@i
WHILE @j <=@num-(@Step-1)
BEGIN
SELECT @l=1,@id='',@w='',@c=null
WHILE @l <=@Step-1
BEGIN
SET @id=@id+LTRIM(@j+@l)+','
SET @l=@l+1
END
SELECT @id=ltrim(@i)+','+@id
SELECT @w=@w+'+ltrim('+NAME+')+'+''',''',@c=ISNULL(@c+',','')+Name FROM tmp WHERE CHARINDEX(','+LTRIM(id)+',',','+@id)>0 ORDER BY id
SET @w=STUFF(@w,1,1,'')
SET @sql='if ((select count(*)*1.0 from 统计表 where '+@w+'='''+@wh+''')/'+LTRIM(@num)+'>' +LTRIM(@f) +') print '''+ @c+''''
EXEC(@sql) SET @j=@j+1
END
SET @i=@i+1
END
end
go
CREATE TABLE 统计表 (方便面 INT,茶叶 INT,面包 INT,口香糖 INT,牛奶 INT,玻璃杯 INT,可乐 INT,冰糖 INT,牙膏 INT,牙刷 INT,故事书 INT,味素 INT,食盐 INT,榨菜 INT,食醋 INT)
INSERT INTO 统计表
SELECT 1,0,0,0,0,1,0,0,0,1,0,0,1,0,0 UNION ALL
SELECT 1,1,0,0,0,1,0,0,0,0,1,1,0,1,0 UNION ALL
SELECT 0,1,1,1,0,1,1,1,0,1,0,0,1,1,0 UNION ALL
SELECT 1,1,0,0,1,1,0,1,1,0,0,1,1,0,0 UNION ALL
SELECT 0,1,1,0,0,1,0,1,0,0,0,0,1,1,1 UNION ALL
SELECT 1,1,0,0,1,1,0,1,1,0,0,0,0,1,0 UNION ALL
SELECT 1,1,1,0,0,1,0,1,0,0,0,1,1,0,0 UNION ALL
SELECT 0,0,1,0,0,1,0,1,0,0,0,0,1,1,1 UNION ALL
SELECT 1,1,1,0,0,1,0,1,1,0,1,1,0,1,0 UNION ALL
SELECT 1,1,1,1,0,1,1,1,0,1,1,0,1,0,1 UNION ALL
SELECT 0,0,1,1,1,1,0,1,0,1,0,1,1,1,0 UNION ALL
SELECT 1,1,1,1,1,1,0,1,0,1,0,0,1,1,1
Go
Create PROC xb_GetArray(@维数 INT, @系数 float ,@结果 VARCHAR(1000) out )
AS
SET NOCOUNT ON
IF OBJECT_ID('dbo.Tmp') IS NOT NULL DROP TABLE dbo.Tmp
CREATE TABLE Tmp(id INT,NAME VARCHAR(20))
INSERT Tmp SELECT colid,NAME FROM syscolumns s WHERE id=OBJECT_ID('统计表')
IF OBJECT_ID('tempdb.dbo.#') IS NOT NULL DROP TABLE #
CREATE TABLE #(ws INT,Result VARCHAR(1000))DECLARE @num INT
DECLARE @f FLOAT
DECLARE @sql nVARCHAR(1000)
DECLARE @id VARCHAR(100),@w VARCHAR(100),@wh VARCHAR(100),@c VARCHAR(100)
DECLARE @Step int ,@i INT ,@j INT ,@s INT,@l INT SET @f=@系数
SELECT @num=count(1) FROM tmp SET @i=1
SET @Step=@维数
set @结果=''
WHILE @i<=@num
BEGIN
SET @wh=REPLICATE('1,',@Step)
SET @j=@i
WHILE @j<=@num-(@Step-1)
BEGIN
SELECT @l=1,@id='',@w='',@c=NULL
WHILE @l<=@Step-1
BEGIN
SET @id=@id+LTRIM(@j+@l)+','
SET @l=@l+1
END
SELECT @id=ltrim(@i)+','+@id
SELECT @w=@w+'+ltrim('+NAME+')+'+''',''',@c=ISNULL(@c+',','')+Name FROM tmp WHERE CHARINDEX(','+LTRIM(id)+',',','+@id)>0 ORDER BY id
SET @w=STUFF(@w,1,1,'')
SET @sql=N'if ((select count(*)*1.0 from 统计表 where '+@w+'='''+@wh+''')/'+LTRIM(@num)+'>' +LTRIM(@f) +') insert # select '+ltrim(@Step)+',''('+ @c+'),'''
EXEC(@sql)
SET @j=@j+1
END
SET @i=@i+1
END
SET @sql=NULL
SELECT @sql=ISNULL(@sql+'','')+result FROM (SELECT DISTINCT result FROM # ) aa
SET @结果='{'+@sql+'}'
GO
DECLARE @sql VARCHAR(1000)
exec xb_GetArray 1,0.3,@sql OUT
SELECT @sql 一维--result
/*
一维
---------------------------------------------------------------------------
{(冰糖),(玻璃杯),(茶叶),(方便面),(面包),(食盐),(味素),(牙刷),(榨菜),}(所影响的行数为 1 行)*/DECLARE @sql VARCHAR(1000)
exec xb_GetArray 2,0.3,@sql OUT
SELECT @sql 二维
/*二维
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
{(冰糖,食盐),(冰糖,榨菜),(玻璃杯,冰糖),(玻璃杯,食盐),(玻璃杯,味素),(玻璃杯,牙刷),(玻璃杯,榨菜),(茶叶,冰糖),(茶叶,玻璃杯),(茶叶,面包),(茶叶,食盐),(茶叶,榨菜),(方便面,冰糖),(方便面,玻璃杯),(方便面,茶叶),(方便面,食盐),(面包,冰糖),(面包,玻璃杯),(面包,食盐),(面包,榨菜),(食盐,榨菜),(牙刷,食盐),}(所影响的行数为 1 行)*/