日期:2014-05-17  浏览次数:20556 次

已经有sql语句了,如何修改为存储过程呢?急求...
SQL code
;with maco as 
(
    select * from table1
    union 
    select * from table2
)
select a.datetime,b.temperature,c.temperature from maco a 
left join table1 b on a.[datetime]=b.[datetime]
left join table2 c on a.[datetime]=c.[datetime]

我想写成存储过程,把table1、table2表名传入,请问这个该怎么做?
如果那个不好改,改下面这个也可以的,谢谢啦
SQL code
SELECT  * FROM 
(
SELECT *,1  AS value FROM table1
UNION ALL
SELECT *,2 FROM table2
)a
PIVOT (MAX([temperature]) FOR value IN ([1],[2]))b


------解决方案--------------------
SQL code

--不同的表,字段会不一样
--把表名做参数,如果表没有[TEMPERATURE]字段会报错的。

CREATE PROC PROCNAME
(
    @TAB1 VARCHAR(50),
    @TAB2 VARCHAR(50),
)
AS
BEGIN
    DECLARE @SQL VARCHAR(MAX)
    SET @SQL='
    SELECT * FROM    
    (
         SELECT *,1 AS VALUE FROM '+@TAB1+'
         UNION ALL
         SELECT *,2 FROM '+@TAB2+'
    ) A PIVOT ( MAX([TEMPERATURE]) FOR VALUE IN ([1], [2])) B'
    EXEC (@SQL)
END

------解决方案--------------------
create proc a
(
@table1 varchar(100),
@table2 varchar(100)
)
as
begin
 declare @sql varchar(4000) = 
';with maco as 
(
select * from @table1
union 
select * from @table2
)
select a.datetime,b.temperature,c.temperature from maco a 
left join table1 b on a.[datetime]=b.[datetime]
left join table2 c on a.[datetime]=c.[datetime]
'

set @sql = REPLACE( @sql,'@table1',@table1)
set @sql = REPLACE( @sql,'@table2',@table2)

exec (@sql)
end
------解决方案--------------------
SQL code

DECLARE @table_name VARCHAR(1000)
SET  @table_name='tb1,tb2,tb3,tbn,tbm'
DECLARE @t TABLE(id INT IDENTITY(1,1),name  VARCHAR(50))
DECLARE @x XML ,@row_cnt INT ,@i INT ,@sql VARCHAR(MAX),@sql2 VARCHAR(MAX)
SET @i=1
SET @x=CONVERT(XML,'<x>'+REPLACE(@table_name, ',' , '</x><x>')+'</x>')
INSERT INTO @t
SELECT N.v.value('.' , 'varchar(50)')
FROM @x.nodes('/x') N (v)
SELECT @sql=ISNULL(@sql+CHAR(10)+'union all'+CHAR(10),'')+'select *,'+LTRIM(id)+' as id from '+QUOTENAME(name) FROM @t
SELECT @sql2= ISNULL(@sql2+',','')+QUOTENAME(id)FROM @t
SELECT @sql='SELECT * FROM('+@sql+') ta PIVOT ( MAX([TEMPERATURE]) FOR VALUE IN ('+@sql2+')) tb'
--PRINT @sql
EXEC(@sql)