CREATE PROC P_ID_paixun @id INT, @PAIXU VARCHAR(50) AS EXEC('select VIP_NO,Sum(Test_Num) as Test_Num,Max(Test_Time) as test_time from soft_Vip_sum where Soft_name='+@id+' GROUP BY VIP_NO order by '+ @PAIXU+' desc ') GO
CREATE proc F_ID_paixun(@id INT, @PAIXU VARCHAR(50)) AS exec ('select VIP_NO,Sum(Test_Num) as Test_Num,Max(Test_Time) as test_time from soft_Vip_sproum where Soft_name='''+@id+''' GROUP BY VIP_NO order by '+@PAIXU+' desc ') GO
CREATE PROC P_ID_paixun @id INT, @PAIXU VARCHAR(50) AS DECLARE @STR VARCHAR(8000) SET @STR='select VIP_NO,Sum(Test_Num) as Test_Num,Max(Test_Time) as test_time from soft_Vip_sum where Soft_name='+RTRIM(@id)+' GROUP BY VIP_NO order by '+ @PAIXU+' desc ' EXEC(@STR) GO
--如果Soft_name是字符型 CREATE PROC P_ID_paixun @id INT, @PAIXU VARCHAR(50) AS DECLARE @STR VARCHAR(8000) SET @STR='select VIP_NO,Sum(Test_Num) as Test_Num,Max(Test_Time) as test_time from soft_Vip_sum where Soft_name='''+RTRIM(@id)+''' GROUP BY VIP_NO order by '+ @PAIXU+' desc ' EXEC(@STR) GO
谢谢各位,我的是自定义函数不是存储过程 CREATE FUNCTION F_ID_paixun
CREATE FUNCTION F_ID_paixun(@id INT, @PAIXU VARCHAR(50)) RETURNS TABLE AS RETURN (select TOP 100 VIP_NO,Sum(Test_Num) as Test_Num,Max(Test_Time) as test_time from soft_Vip_sum where Soft_name=@id GROUP BY VIP_NO order by '@PAIXU' desc GO 这样试试??呵呵
CREATE FUNCTION F_ID_paixun(@id INT, @PAIXU VARCHAR(50)) RETURNS @TABLE TABLE(VIP_NO VARCHAR(20),TEST_NUM INT,TEST_TIME DATETIME) AS select VIP_NO, Sum(Test_Num) as Test_Num, Max(Test_Time) as test_time from soft_Vip_sum where Soft_name=@id GROUP BY VIP_NO order by '@PAIXU' desc RETURN GO 这样吧,应该可以了,呵呵
少了几个东西,呵呵!!补上!!CREATE FUNCTION F_ID_paixun(@id INT, @PAIXU VARCHAR(50)) RETURNS @TABLE TABLE(VIP_NO VARCHAR(20),TEST_NUM INT,TEST_TIME DATETIME) AS begin insert @table select VIP_NO, Sum(Test_Num) as Test_Num, Max(Test_Time) as test_time from soft_Vip_sum where Soft_name=@id GROUP BY VIP_NO order by '@PAIXU' desc return end GO
--这样吧--方法1: ALTER function [dbo].[TestVar](@paixu varchar(20)) returns @Table TABLE(Object VARCHAR(500),Event varchar(500),PostTime datetime) as begin insert into @Table select Object,max([Event]) Event,max(PostTime) PostTime from databaseLog group by object order by (select name from syscolumns where id=object_id('DatabaseLog') and name=@paixu) Desc /*case @paixu when 'Event' then 'Event' when 'PostTime' then 'PostTime' else 'Object' end */
return end --方法2 ALTER function [dbo].[TestVar](@paixu varchar(20)) returns @Table TABLE(Object VARCHAR(500),Event varchar(500),PostTime datetime) as begin insert into @Table select Object,max([Event]) Event,max(PostTime) PostTime from AdventureWorks.dbo.databaseLog group by object order by case @paixu when 'Event' then 'Event' when 'PostTime' then 'PostTime' else 'Object' end return end
@id INT,
@PAIXU VARCHAR(50)
AS
EXEC('select VIP_NO,Sum(Test_Num) as Test_Num,Max(Test_Time) as test_time from soft_Vip_sum where Soft_name='+@id+' GROUP BY VIP_NO order by '+ @PAIXU+' desc ')
GO
AS exec ('select VIP_NO,Sum(Test_Num) as Test_Num,Max(Test_Time) as test_time from soft_Vip_sproum where Soft_name='''+@id+''' GROUP BY VIP_NO order by '+@PAIXU+' desc ')
GO
CREATE PROC P_ID_paixun
@id INT,
@PAIXU VARCHAR(50)
AS
DECLARE @STR VARCHAR(8000)
SET @STR='select VIP_NO,Sum(Test_Num) as Test_Num,Max(Test_Time) as test_time from soft_Vip_sum where Soft_name='+RTRIM(@id)+' GROUP BY VIP_NO order by '+ @PAIXU+' desc '
EXEC(@STR)
GO
CREATE PROC P_ID_paixun
@id INT,
@PAIXU VARCHAR(50)
AS
DECLARE @STR VARCHAR(8000)
SET @STR='select VIP_NO,Sum(Test_Num) as Test_Num,Max(Test_Time) as test_time from soft_Vip_sum where Soft_name='''+RTRIM(@id)+''' GROUP BY VIP_NO order by '+ @PAIXU+' desc '
EXEC(@STR)
GO
CREATE FUNCTION F_ID_paixun
RETURNS TABLE
AS RETURN (select TOP 100 VIP_NO,Sum(Test_Num) as Test_Num,Max(Test_Time) as test_time from soft_Vip_sum where Soft_name=@id GROUP BY VIP_NO order by '@PAIXU' desc
GO
这样试试??呵呵
CREATE FUNCTION F_ID_paixun(@id INT, @PAIXU VARCHAR(50))
RETURNS @TABLE TABLE(VIP_NO VARCHAR(20),TEST_NUM INT,TEST_TIME DATETIME)
AS
select VIP_NO,
Sum(Test_Num) as Test_Num,
Max(Test_Time) as test_time
from soft_Vip_sum where Soft_name=@id
GROUP BY VIP_NO
order by '@PAIXU' desc RETURN
GO
这样吧,应该可以了,呵呵
关于动态SQL基本语法,楼主可以参考这个。
http://blog.csdn.net/sdhdy/archive/2009/05/15/4190010.aspx
用函数处理这种情况,不太容易,建议楼主变通一下用存储过程来实现。
少了几个东西,呵呵!!补上!!CREATE FUNCTION F_ID_paixun(@id INT, @PAIXU VARCHAR(50))
RETURNS @TABLE TABLE(VIP_NO VARCHAR(20),TEST_NUM INT,TEST_TIME DATETIME)
AS
begin
insert @table
select VIP_NO,
Sum(Test_Num) as Test_Num,
Max(Test_Time) as test_time
from soft_Vip_sum
where Soft_name=@id
GROUP BY VIP_NO
order by '@PAIXU' desc
return
end
GO
--这样吧--方法1:
ALTER function [dbo].[TestVar](@paixu varchar(20))
returns @Table TABLE(Object VARCHAR(500),Event varchar(500),PostTime datetime)
as
begin
insert into @Table
select Object,max([Event]) Event,max(PostTime) PostTime from databaseLog
group by object
order by (select name from syscolumns where id=object_id('DatabaseLog') and name=@paixu) Desc
/*case @paixu when 'Event' then 'Event' when 'PostTime' then 'PostTime' else 'Object' end */
return
end
--方法2
ALTER function [dbo].[TestVar](@paixu varchar(20))
returns @Table TABLE(Object VARCHAR(500),Event varchar(500),PostTime datetime)
as
begin
insert into @Table
select Object,max([Event]) Event,max(PostTime) PostTime from AdventureWorks.dbo.databaseLog
group by object order by case @paixu when 'Event' then 'Event' when 'PostTime' then 'PostTime' else 'Object' end
return
end