22,210
社区成员
发帖
与我相关
我的任务
分享
ALTER PROCEDURE [dbo].[WinFormConfigExhibitStandByID]
-- Add the parameters for the stored procedure here
@ExhibitID int,
@XYrowcol nvarchar(50) output, --终端所处的 行-列 (第一个要传出的参数)
@PhoneInfo nvarchar(50) output, --终端的品牌 型号 (第二个要传出的参数)
@TerminalID int output --终端id (第三个要传出的参数)
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
-- Insert statements for procedure here
SELECT @XYrowcol=(str(XRow)+'--'+ltrim(str(YColumn))),@PhoneInfo=(c.BrandName+' '+b.TerminalModel),@TerminalID=a.TerminalID from InteHel_ExhibitTerminal a
inner join InteHel_Terminal b on a.TerminalID=b.TerminalID
inner join InteHel_Brand c on b.BrandID=c.BrandID
where a.ExhibitID=@ExhibitID
END
if object_id(N'[tempdb].[dbo].[#Sun_Tmpone]') is not null
drop table #Sun_Tmpone
GO -----这里加个GO
if object_id(N'[tempdb].[dbo].[#Sun_Tmpone]') is not null
drop table #Sun_Tmpone
create table #Sun_Tmpone
(
ID int IDENTITY (1,1) not null,
XYrowcol nvarchar(50),
PhoneInfo nvarchar(50),
TerminalID int
primary key (ID)
)
select * into #Sun_Tmpone from
(
SELECT (str(XRow)+'--'+ltrim(str(YColumn))) as XYrowcol ,(c.BrandName+' '+b.TerminalModel) as PhoneInfo,a.TerminalID from InteHel_ExhibitTerminal a
inner join InteHel_Terminal b on a.TerminalID=b.TerminalID
inner join InteHel_Brand c on b.BrandID=c.BrandID
where a.ExhibitID=5
) tempsun
select * into #Sun_Tmpone from
(
SELECT (str(XRow)+'--'+ltrim(str(YColumn))) as XYrowcol ,(c.BrandName+' '+b.TerminalModel) as PhoneInfo,a.TerminalID from InteHel_ExhibitTerminal a
inner join InteHel_Terminal b on a.TerminalID=b.TerminalID
inner join InteHel_Brand c on b.BrandID=c.BrandID
where a.ExhibitID=5
)t----这里加个t
select * into #Sun_Tmpone from
(
SELECT (str(XRow)+'--'+ltrim(str(YColumn))) as XYrowcol ,(c.BrandName+' '+b.TerminalModel) as PhoneInfo,a.TerminalID from InteHel_ExhibitTerminal a
inner join InteHel_Terminal b on a.TerminalID=b.TerminalID
inner join InteHel_Brand c on b.BrandID=c.BrandID
where a.ExhibitID=5
)
if object_id(N'[tempdb].[dbo].[#Sun_Tmpone]') is not null
drop table #Sun_Tmpone
create table #Sun_Tmpone
(
ID int IDENTITY (1,1) not null,
XYrowcol nvarchar(50),
PhoneInfo nvarchar(50),
TerminalID int
primary key (ID)
)
select * into #Sun_Tmpone from
(
SELECT (str(XRow)+'--'+ltrim(str(YColumn))) as XYrowcol ,(c.BrandName+' '+b.TerminalModel) as PhoneInfo,a.TerminalID from InteHel_ExhibitTerminal a
inner join InteHel_Terminal b on a.TerminalID=b.TerminalID
inner join InteHel_Brand c on b.BrandID=c.BrandID
where a.ExhibitID=5
)
Select * from #Sun_Tmpone
truncate table #Sun_Tmpone
create procedure ProcName
@param1 param1Type,....
as
set nocount on
--和create table语句语法类似,列名 后接 列类型就够了,是你返回表的结构
declare @t table(id int,[name] varchar(20)....)
insert into @t (feild1,field2....)
select .....
from ....
where ...
group by ...
order by ....
set nocount off
select * from @t