如何在SQL Server的存储过程的Crystal Report中使用像Switch语句一样的语句 [英] how to use like Switch statement in crystal report in Stored Procedure of sql server

问题描述

大家好，

我正在使用Parameter和S.P.制作Crystal报表.
现在，Crystal报表中有两个参数分别为Company Name和Days.

当我选择一个公司时，该公司的数据库将在S.P.中执行.

我使用如下查询:

Hello To All,

I am making a Crystal report with the use of Parameter and S.P.
now, there is two parameter in Crystal report as Company Name and Days.

When i Select one company, the database of that company will be executed in S.P.

i use the query like below:

SELECT     ''FMCPL''as company, itmast.itcode, sl.itname AS itemname, SUM(sl.qtyin) AS qtyin, SUM(sl.qtyout) AS qtyout, SUM(sl.qtyin)- SUM(sl.qtyout) as qty, sl.unit AS unit, MAX(sl.docdt) AS maxdt, munim002.dbo.itmast.material, 
                      munim002.dbo.itgroup.dtldesc, munim002.dbo.gdmast.gdname
FROM         munim002.dbo.StockLederView sl INNER JOIN
                      munim002.dbo.itmast ON sl.itcode = munim002.dbo.itmast.itcode INNER JOIN
                      munim002.dbo.itgroup ON munim002.dbo.itmast.itgroup = munim002.dbo.itgroup.code INNER JOIN
                      munim002.dbo.gdmast ON sl.gdcode = munim002.dbo.gdmast.gdcode
GROUP BY sl.itname, sl.unit, munim002.dbo.itmast.material, munim002.dbo.itgroup.dtldesc, munim002.dbo.gdmast.gdname
HAVING      SUM(sl.qtyin)- SUM(sl.qtyout) > 0 and (MAX(sl.DOCdt) <= DATEADD(dd, 0, DATEDIFF(dd, 0, GETDATE() - @dys)))

数据库将根据公司的选择参数自动更改.

所以，请帮助我...


谢谢和问候...
Mitesh

it databas will automatically change as per the selection parameter of company.

so,please help me...


Thanks and Regards...
Mitesh

推荐答案

请尝试以下操作

Please try this

declare @dbname nvarchar(50)
DECLARE @EXECQ AS VARCHAR(MAX)

set @dbname=''

if @comapny='companyname'
Begin
set @dbname='dbname'
End


SET @EXECQ='SELECT '''+@comapny+''' as company, sl.itname AS itemname, SUM(sl.qtyin) AS qtyin, SUM(sl.qtyout) AS qtyout, SUM(sl.qtyin)- SUM(sl.qtyout) as qty, sl.unit AS unit, MAX(sl.docdt) AS maxdt, ' + @dbname + '.dbo.itmast.material,
                      ' + @dbname + '.dbo.itgroup.dtldesc, ' + @dbname + '.dbo.gdmast.gdname
FROM         ' + @dbname + '.dbo.StockLederView sl INNER JOIN
                      ' + @dbname + '.dbo.itmast ON sl.itcode = ' + @dbname + '.dbo.itmast.itcode INNER JOIN
                      ' + @dbname + '.dbo.itgroup ON ' + @dbname + '.dbo.itmast.itgroup = ' + @dbname + '.dbo.itgroup.code INNER JOIN
                      ' + @dbname + '.dbo.gdmast ON sl.gdcode = ' + @dbname + '.dbo.gdmast.gdcode
GROUP BY sl.itname, sl.unit, ' + @dbname + '.dbo.itmast.material, ' + @dbname + '.dbo.itgroup.dtldesc, ' + @dbname + '.dbo.gdmast.gdname
HAVING      SUM(sl.qtyin)- SUM(sl.qtyout) > 0 and (MAX(sl.DOCdt) <= DATEADD(dd, 0, DATEDIFF(dd, 0, GETDATE() - @dys)))

ORDER BY sl.itname'
--print @EXECQ
Exec (@ExecQ)

谢谢

Thanks

这篇关于如何在SQL Server的存储过程的Crystal Report中使用像Switch语句一样的语句的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！