?? 简单SQL,如何将参数带入存储过程并返回结果 ??
如下三个表:
--销售表
CREATE TABLE [Y_liushui] (
[id] [int] IDENTITY (1, 1) NOT NULL ,
[jinhuodanwei] [varchar] (100) COLLATE Chinese_PRC_CI_AS NULL ,
[cpid] [int] NULL ,
[yppinming] [varchar] (50) COLLATE Chinese_PRC_CI_AS NOT NULL ,
[ypjine] [decimal](18, 4) NULL ,
[shuliang] [decimal](18, 4) NOT NULL ,
[caozuobiaoshi] [varchar] (20) COLLATE Chinese_PRC_CI_AS NOT NULL ,
[caozuoriqi] [datetime] NULL CONSTRAINT [DF_Y_liushui1_caozuoriqi] DEFAULT (getdate()),
CONSTRAINT [PK_YP_liushui1] PRIMARY KEY CLUSTERED
(
[id]
) ON [PRIMARY]
) ON [PRIMARY]
GO
id jinhuodanwei cpid yppinming ypjine shuliang caozuobiaoshi caozuoriqi
1 大兴店 1 香蕉 10 2 产品销售 2014-04-05 10:01:01
2 大兴店 1 香蕉 5 1 产品销售 2014-04-05 10:01:01
--产品表
CREATE TABLE [Y_baseinfo] (
[id] [int] IDENTITY (1, 1) NOT NULL ,
[cpid] [int] NULL ,
[yppinming] [varchar] (50) COLLATE Chinese_PRC_CI_AS NULL
CONSTRAINT [PK_Y_baseinfo] PRIMARY KEY CLUSTERED
(
[id]
) ON [PRIMARY]
) ON [PRIMARY]
GO
id cpid yppinming
1 1 香蕉
2 2 苹果
--客户表
CREATE TABLE [Y_xskehu] (
[id] [int] IDENTITY (1, 1) NOT NULL ,
[jhdanwei] [varchar] (50) COLLATE Chinese_PRC_CI_AS NULL CONSTRAINT [DF_Y_xskehu_jhdanwei] DEFAULT (''),
[diqu] [varchar] (50) COLLATE Chinese_PRC_CI_AS NULL CONSTRAINT [DF_Y_xskehu_diqu] DEFAULT ('')
CONSTRAINT [PK_miye_jinhuokehu] PRIMARY KEY CLUSTERED
(
[id]
) ON [PRIMARY]
) ON [PRIMARY]
GO
id jhdanwei diqu
1 大兴店 北京
2 东直店 上海
-----------------以下是SQL查询过程---------------
问题: 如何用存储过程的方式,将下面的红字部分用给定的传入参数来代替?
diqu like '%%' and jhdanwei like '%%' 分别传入@diqu、@jhdanwei,''2014-01-01'' 与 ''2014-05-01'' 分别传入日期1、日期2。请问该怎么写,要显示运行结果
IF EXISTS (SELECT name FROM sysobjects
WHERE name = 'jieguo' AND type = 'P')
DROP PROCEDURE jieguo
go
IF EXISTS(SELECT NAME FROM sysobjects AS o WHERE NAME = 'T_jiamengdiantongji')
DROP TABLE T_jiamengdiantongji
GO
--create procedure jieguo
--@diqu varchar(50),
--@jhdanwei varchar(50)
--@日期1 datetime(),
--@日期2 datetime()
--as
DECLARE @sql VARCHAR(8000)
DECLARE @str VARCHAR(8000)
SELECT @sql = ISNULL(@sql , '') + N' sum(case when jhdanwei = N''' + jhdanwei + ''' then shuliang else 0 end) as [' +jhdanwei+N'|数量]'
+N' , sum(case when jhdanwei = N''' + jhdanwei + N''' then ypjine else 0 end) as ['+jhdanwei+N'|金额]'+ N',' FROM&n