依次解释以下存储过程.... [英] explain the below storedprocedure sequentially....

问题描述

hai ...

任何人都可以按顺序解释以下存储过程..bcoz我是初学者...


存储过程:

创建过程[dbo].[amazon_Number1Direct_reports](@ usname varchar(100)，@ date varchar(50))
如
开始
如果(@date ='''')
开始
十进制@sql VARCHAR(2000)
--declare @usname varchar(100)
SET @sql =''
SELECT ID，MPN，产品名称，更新日期，
MAX(低时= 1，然后是商户，否则为空结束)的情况为LowPriceMerchant，
MAX(低位时= 1，则基准价格，否则为空结束)AS低价，
MAX(高时= 1，然后是商户，否则为空结束)AS HighPriceMerchant，
MAX(高时为1，则底价为ELSE NULL END的情况)AS高价，
MAX(CASE商家，当''''''+ @usname +''''''THEN baseprice ELSE NULL END)AS MyPrice，
MAX(CASE商家，当''''''+ @usname +''''''THEN bottomlineprice ELSE NULL END)AS MyBottomprice
INTO ## Temp
从(
SELECT ID，MPN，产品名称，更新日期，
商家，
基本价格，
bottomlineprice，
ROW_NUMBER()超过(按ID，MPN，更新日期，产品名称按基价DESC排序的商品)高，
ROW_NUMBER()超过(按ID，MPN，更新日期，产品名称或按基本价格升序排列的订单)AS低
来自[tbl_amazon1_master_merchant]
)AS t
GROUP BY ID，MPN，产品名称，更新日期
选择
m.MPN，
t1.product_description作为Product_Title，
m.MyPrice，
m.MyBottomprice，
m.LowPrice，
案例m.LowPriceMerchant当''''''+ @usname +''''''然后''''您''''否则m.LowPriceMerchant结尾为LowPriceMerchant，
m.HighPrice，
案例m.HighPriceMerchant当''''''+ @ usname +''''''然后''''''''否则m.HighPriceMerchant结尾为HighPriceMerchant
来自## Temp AS m
内联tbl_amazon1_master_product AS t1在t1上t.ID = m.ID和日期名(dd，t1.updated_date)=日期名(dd，m.updateddate)和日期名(mm，t1.updated_date)=日期名(mm，m.updateddate)和datename(yy，t1.updated_date)=日期名称(yy，m.updateddate)和datename(dd，t1.updated_date)= datename(dd，getdate())和datename(mm，t1.updated_date)= datename(mm，getdate ())和datename(yy，t1.updated_date)= datename(yy，getdate())
删除表## temp''
exec(@sql)
结束
其他
开始
宣告@ sql1 VARCHAR(2000)
--declare @usname varchar(100)
SET @ sql1 =''
SELECT ID，MPN，产品名称，更新日期，
MAX(低时= 1，然后是商户，否则为空结束)的情况为LowPriceMerchant，
MAX(低位时= 1，则基准价格，否则为空结束)AS低价，
MAX(高时= 1，然后是商户，否则为空结束)AS HighPriceMerchant，
MAX(高时为1，则底价为ELSE NULL END的情况)AS高价，
MAX(CASE商家，当''''''+ @usname +''''''THEN baseprice ELSE NULL END)AS MyPrice，
MAX(CASE商家，当''''''+ @usname +''''''THEN bottomlineprice ELSE NULL END)AS MyBottomprice
INTO ## Temp
从(
SELECT ID，MPN，产品名称，更新日期，
商家，
基本价格，
bottomlineprice，
ROW_NUMBER()超过(按ID，MPN，更新日期，产品名称按基价DESC排序的商品)高，
ROW_NUMBER()超过(按ID，MPN，更新日期，产品名称或按基本价格升序排列的订单)AS低
来自tbl_amazon1_master_merchant
)AS t
GROUP BY ID，MPN，产品名称，更新日期
选择
m.ID，
m.MPN，
t1.product_description作为Product_Title，
m.MyPrice，
m.MyBottomprice，
m.LowPrice，
案例m.LowPriceMerchant当''''''+ @usname +''''''然后''''您''''否则m.LowPriceMerchant结尾为LowPriceMerchant，
m.HighPrice，
案例m.HighPriceMerchant当''''''+ @ usname +''''''然后''''''''否则m.HighPriceMerchant结尾为HighPriceMerchant
来自## Temp AS m
内联tbl_amazon1_master_product AS t1开启t1.ID = m.ID和t1.updated_date = m.updateddate和m.updateddate = cast(''''''+ @ date +''''''作为日期时间)
删除表## temp''
--in(从tbl_googleshopping2_master_merchant中选择更新日期)和datepart(dd，t1.updated_date)= datepart(dd，cast(''''''''+ @ date +''''''作为datetime))和datepart(mm，t1 .updated_date)= datepart(mm，cast(''''''+ @ date +''''''作为datetime))和datepart(yy，t1.updated_date)= datepart(yy，cast(''''' '+ @ date +''''''作为日期时间))
--INNER JOIN [tbl_Pricegrabber_master_product]作为t1上的t1.MobID= m.MobID和datepart(dd，CAST(FLOOR((CAST(t1.Updated_Date as varchar)))AS DATETIME))= datepart(dd，getdate())和datepart(mm，CAST(FLOOR((CAST(t1.Updated_Date as varchar)))AS DATETIME))= datepart(mm，getdate())和datepart(yy，CAST(FLOOR((CAST(t1.Updated_Date as varchar) )))AS DATETIME))= datepart(yy，getdate())
--INNER JOIN [tbl_Pricegrabber_master_product]作为t1上的t1.MobID= m.MobID和datepart(dd，t1.Updated_Date)= datepart(dd，getdate())和datepart(mm，t1.Updated_Date)= datepart(mm，getdate ())和datepart(yy，t1.Updated_Date)= datepart(yy，getdate())
exec(@ sql1)
结束
end

hai...

Can anyone explain the following stored procedure orderly..bcoz i am a beginner...


storedprocedure:

create procedure [dbo].[amazon_Number1Direct_reports](@usname varchar(100),@date varchar(50))
as
begin
if (@date ='''')
begin
DECLARE @sql VARCHAR(2000)
--declare @usname varchar(100)
SET @sql = ''
SELECT ID,MPN,productname,updateddate,
MAX(CASE WHEN low = 1 THEN Merchants ELSE NULL END) AS LowPriceMerchant,
MAX(CASE WHEN low = 1 THEN baseprice ELSE NULL END) AS LowPrice,
MAX(CASE WHEN high = 1 THEN merchants ELSE NULL END) AS HighPriceMerchant,
MAX(CASE WHEN high = 1 THEN baseprice ELSE NULL END) AS HighPrice,
MAX(CASE merchants WHEN '''''' + @usname + '''''' THEN baseprice ELSE NULL END) AS MyPrice,
MAX(CASE merchants WHEN '''''' + @usname + '''''' THEN bottomlineprice ELSE NULL END) AS MyBottomprice
INTO ##Temp
FROM (
SELECT ID,MPN,productname,updateddate,
merchants,
baseprice,
bottomlineprice,
ROW_NUMBER() OVER (PARTITION BY ID,MPN,updateddate,productname ORDER BY baseprice DESC) AS high,
ROW_NUMBER() OVER (PARTITION BY ID,MPN,updateddate,productname ORDER BY baseprice asc) AS low
FROM [tbl_amazon1_master_merchant]
) AS t
GROUP BY ID,MPN,productname,updateddate
SELECT
m.MPN,
t1.product_description as Product_Title,
m.MyPrice,
m.MyBottomprice,
m.LowPrice,
case m.LowPriceMerchant when '''''' + @usname + '''''' then ''''you'''' else m.LowPriceMerchant end as LowPriceMerchant,
m.HighPrice,
case m.HighPriceMerchant when ''''''+ @usname+ '''''' then ''''you'''' else m.HighPriceMerchant end as HighPriceMerchant
FROM ##Temp AS m
INNER JOIN tbl_amazon1_master_product AS t1 ON t1.ID = m.ID and datename(dd,t1.updated_date)= datename(dd,m.updateddate) and datename(mm,t1.updated_date)= datename(mm,m.updateddate)and datename(yy,t1.updated_date)=datename(yy,m.updateddate) and datename(dd,t1.updated_date)= datename(dd,getdate()) and datename(mm,t1.updated_date)=datename(mm,getdate()) and datename(yy,t1.updated_date)=datename(yy,getdate())
drop table ##temp''
exec (@sql)
end
else
begin
DECLARE @sql1 VARCHAR(2000)
--declare @usname varchar(100)
SET @sql1 = ''
SELECT ID,MPN,productname,updateddate,
MAX(CASE WHEN low = 1 THEN Merchants ELSE NULL END) AS LowPriceMerchant,
MAX(CASE WHEN low = 1 THEN baseprice ELSE NULL END) AS LowPrice,
MAX(CASE WHEN high = 1 THEN merchants ELSE NULL END) AS HighPriceMerchant,
MAX(CASE WHEN high = 1 THEN baseprice ELSE NULL END) AS HighPrice,
MAX(CASE merchants WHEN '''''' + @usname + '''''' THEN baseprice ELSE NULL END) AS MyPrice,
MAX(CASE merchants WHEN '''''' + @usname + '''''' THEN bottomlineprice ELSE NULL END) AS MyBottomprice
INTO ##Temp
FROM (
SELECT ID,MPN,productname,updateddate,
merchants,
baseprice,
bottomlineprice,
ROW_NUMBER() OVER (PARTITION BY ID,MPN,updateddate,productname ORDER BY baseprice DESC) AS high,
ROW_NUMBER() OVER (PARTITION BY ID,MPN,updateddate,productname ORDER BY baseprice asc) AS low
FROM tbl_amazon1_master_merchant
) AS t
GROUP BY ID,MPN,productname,updateddate
SELECT
m.ID,
m.MPN,
t1.product_description as Product_Title,
m.MyPrice,
m.MyBottomprice,
m.LowPrice,
case m.LowPriceMerchant when '''''' + @usname + '''''' then ''''you'''' else m.LowPriceMerchant end as LowPriceMerchant,
m.HighPrice,
case m.HighPriceMerchant when ''''''+ @usname+ '''''' then ''''you'''' else m.HighPriceMerchant end as HighPriceMerchant
FROM ##Temp AS m
INNER JOIN tbl_amazon1_master_product AS t1 ON t1.ID = m.ID and t1.updated_date=m.updateddate and m.updateddate=cast(''''''+@date+'''''' as datetime)
drop table ##temp''
--in (select updateddate from tbl_googleshopping2_master_merchant) and datepart(dd,t1.updated_date)= datepart(dd,cast(''''''+@date+'''''' as datetime)) and datepart(mm,t1.updated_date)=datepart(mm,cast(''''''+@date+'''''' as datetime)) and datepart(yy,t1.updated_date)=datepart(yy,cast(''''''+@date+'''''' as datetime))
--INNER JOIN [tbl_Pricegrabber_master_product] as t1 on t1.MobID = m.MobID and datepart(dd,CAST(FLOOR((CAST(t1.Updated_Date as varchar))) AS DATETIME))= datepart(dd,getdate()) and datepart(mm,CAST(FLOOR((CAST(t1.Updated_Date as varchar))) AS DATETIME))=datepart(mm,getdate()) and datepart(yy,CAST(FLOOR((CAST(t1.Updated_Date as varchar))) AS DATETIME))=datepart(yy,getdate())
--INNER JOIN [tbl_Pricegrabber_master_product] as t1 on t1.MobID = m.MobID and datepart(dd,t1.Updated_Date)= datepart(dd,getdate()) and datepart(mm,t1.Updated_Date)=datepart(mm,getdate()) and datepart(yy,t1.Updated_Date)=datepart(yy,getdate())
exec (@sql1)
end
end

<pre lang="HTML"><pre lang="CSS"><pre lang="SQL"><>

推荐答案

与5小时前M.Narmatha发表的问题相同:

his looks the same as the question posted by M.Narmatha 5 hours ago: Can Anyone Explain following code step by step please....[^] and my answer remains the same:

Do you have any idea how much work explaining code line by line is?
Every single line needs a paragraph of explanation! For example:

ALTER procedure [dbo].[amazon_Number1Direct_reports](@usname varchar(100),@date varchar(50))

在当前数据库中更改名为amazon_Number1Direct_reports的现有存储过程.新的过程定义应包含两个参数:第一个称为"@usname"，并且期望可变长度的字符串不超过100个字符.第二个称为"@date"，期望可变长度的字符串不超过50个字符.

您能想象我们需要花多长时间一行一行地解释一个非常短的代码片段吗?

不，这不会发生.如果您有特定的问题，请提出一个问题.但是首先考虑一下-您是否想坐下45分钟并在没有充分理由的情况下逐行输入说明?

如果您在袜子娃娃帐户下不是同一用户，那么我认为你们两个应该聚在一起，因为你们俩显然都需要相同的帮助.

Change an existing stored procedure called amazon_Number1Direct_reports in the current database. The new procedure definition should take two parameters: the first is called "@usname" and expects a variable length character string of no more than 100 characters. The second is called "@date", and expects a variable length character string of no more than 50 characters.

Can you imagine how long it would take us to explain even a very short code fragment like your example, line by line?

No. It is not going to happen. If you have a specific problem, then ask a question about it. But think first - would you want to sit down for 45 minutes and type up a line-by-line description for no good reason?

If you are not the same user under a sock puppet account, then I think the two of you should get together, since you are both obviously in need of the same help.

这篇关于依次解释以下存储过程....的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！