Click here to Skip to main content
13,197,804 members (41,081 online)
Rate this:
 
Please Sign up or sign in to vote.
See more:
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)))



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

so,please help me...


Thanks and Regards...
Mitesh
Posted 11-Oct-12 19:47pm

1 solution

Rate this: bad
 
good
Please Sign up or sign in to vote.

Solution 1

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
  Permalink  

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

  Print Answers RSS
Top Experts
Last 24hrsThis month


Advertise | Privacy |
Web03 | 2.8.171020.1 | Last Updated 12 Oct 2012
Copyright © CodeProject, 1999-2017
All Rights Reserved. Terms of Service
Layout: fixed | fluid

CodeProject, 503-250 Ferrand Drive Toronto Ontario, M3C 3G8 Canada +1 416-849-8900 x 100