how to use like Switch statement in crystal report in Stored Procedure of sql server

Question

0.00/5 (No votes)

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

Mits Machhi

Add a Solution

1 solution

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

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

AshishChaudha · Accepted Answer · 2012-10-11T19:50:00

Please try this

SQL

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