Can this stored procedure be optimised?

Hi there,
I hoping someone can help me reduce the number of line of code I'm
using, as these IF's are nested inside a bigger one.
The main problem I have is I need to add another variable to the IF and
don't want to copy and paste and make this statement even larger.
I have tried playing about with EXEC but with no joy. As far as I'm
aware it's not possible to do @.CSOrder > 0 ? CSPerson = @.CSOrder :
CSPerson > 0
I'm using SQL Server 2000 SP4.
Hopefully I'm missing the obvious, although any help or suggestions are
welcome.
-- snippet --
IF @.CSOrder > 0 AND @.SalesOrder > 0
SELECT * FROM MainOrder
LEFT JOIN AnCJobs ON MainOrder.[id] = AnCJobs.[id]
LEFT JOIN JobFlow ON JobFlow.[id] = MainOrder.[id]
WHERE JobFlow.Component=6
AND (Status = @.InProgress
OR Status = @.Complete
OR Status = @.Cancelled
OR Status = @.Acknowledged)
AND CSPerson = @.CSOrder
AND SalesPerson = @.SalesOrder
ORDER BY DateToArrive, Status
ELSE IF @.CSOrder > 0
SELECT * FROM MainOrder
LEFT JOIN AnCJobs ON MainOrder.[id] = AnCJobs.[id]
LEFT JOIN JobFlow ON JobFlow.[id] = MainOrder.[id]
WHERE JobFlow.Component=6
AND (Status = @.InProgress
OR Status = @.Complete
OR Status = @.Cancelled
OR Status = @.Acknowledged)
AND CSPerson = @.CSOrder
ORDER BY DateToArrive, Status
ELSE IF @.SalesOrder > 0
SELECT * FROM MainOrder
LEFT JOIN AnCJobs ON MainOrder.[id] = AnCJobs.[id]
LEFT JOIN JobFlow ON JobFlow.[id] = MainOrder.[id]
WHERE JobFlow.Component=6
AND (Status = @.InProgress
OR Status = @.Complete
OR Status = @.Cancelled
OR Status = @.Acknowledged)
AND SalesPerson = @.SalesOrder
ORDER BY DateToArrive, Status
ELSE
SELECT * FROM MainOrder
LEFT JOIN AnCJobs ON MainOrder.[id] = AnCJobs.[id]
LEFT JOIN JobFlow ON JobFlow.[id] = MainOrder.[id]
WHERE JobFlow.Component=6
AND (Status = @.InProgress
OR Status = @.Complete
OR Status = @.Cancelled
OR Status = @.Acknowledged)
ORDER BY DateToArrive, StatusWhat you can do is something like this.
SELECT * FROM MainOrder
LEFT JOIN AnCJobs ON MainOrder.[id] = AnCJobs.[id]
LEFT JOIN JobFlow ON JobFlow.[id] = MainOrder.[id]
WHERE JobFlow.Component=6
AND (Status = @.InProgress
OR Status = @.Complete
OR Status = @.Cancelled
OR Status = @.Acknowledged)
AND CSPerson = CSAE @.CSOrder WHEN 0 THEN CSPerson ELSE @.CSOrder
END
AND SalesPerson = CASE @.SalesOrder WHEN 0 THEN SalesPerson ELSE
@.SalesOrder END
AND ... -- add more
AND SalesPerson = @.SalesOrder
ORDER BY DateToArrive, Status|||Mia,
Try something like this
DECLARE @.strWHERE varchar(200)
SET @.strWHERE = ''
IF @.CSOrder > 0 AND @.SalesOrder > 0 THEN @.strWHERE = 'AND CSPerson =
@.CSOrder AND SalesPerson = @.SalesOrder'
IF @.CSOrder > 0 AND @.SalesOrder <> 0 THEN @.strWHERE = 'AND CSPerson =
@.CSOrder'
IF @.CSOrder <> 0 AND @.SalesOrder > 0 THEN @.strWHERE = 'AND SalesPerson =
@.SalesOrder'
DECLARE @.sSQL varchar(2000)
SET @.sSQL = ''
SET @.sSQL = @.sSQL + ' SELECT * FROM MainOrder '
SET @.sSQL = @.sSQL + ' LEFT JOIN AnCJobs ON MainOrder.[id] = AnCJobs.[id] '
SET @.sSQL = @.sSQL + ' LEFT JOIN JobFlow ON JobFlow.[id] = MainOrder.[id] '
SET @.sSQL = @.sSQL + ' WHERE JobFlow.Component=6 '
SET @.sSQL = @.sSQL + ' AND (Status = @.InProgress '
SET @.sSQL = @.sSQL + ' OR Status = @.Complete '
SET @.sSQL = @.sSQL + ' OR Status = @.Cancelled '
SET @.sSQL = @.sSQL + ' OR Status = @.Acknowledged) '
SET @.sSQL = @.sSQL + @.strWHERE
SET @.sSQL = @.sSQL + ' AND SalesPerson = @.SalesOrder '
SET @.sSQL = @.sSQL + ' AORDER BY DateToArrive, Status '
EXEC (@.sSQL)
Hope this assists,
Tony
"mia_cid@.hotmail.com" wrote:

> Hi there,
> I hoping someone can help me reduce the number of line of code I'm
> using, as these IF's are nested inside a bigger one.
> The main problem I have is I need to add another variable to the IF and
> don't want to copy and paste and make this statement even larger.
> I have tried playing about with EXEC but with no joy. As far as I'm
> aware it's not possible to do @.CSOrder > 0 ? CSPerson = @.CSOrder :
> CSPerson > 0
> I'm using SQL Server 2000 SP4.
> Hopefully I'm missing the obvious, although any help or suggestions are
> welcome.
> -- snippet --
> IF @.CSOrder > 0 AND @.SalesOrder > 0
> SELECT * FROM MainOrder
> LEFT JOIN AnCJobs ON MainOrder.[id] = AnCJobs.[id]
> LEFT JOIN JobFlow ON JobFlow.[id] = MainOrder.[id]
> WHERE JobFlow.Component=6
> AND (Status = @.InProgress
> OR Status = @.Complete
> OR Status = @.Cancelled
> OR Status = @.Acknowledged)
> AND CSPerson = @.CSOrder
> AND SalesPerson = @.SalesOrder
> ORDER BY DateToArrive, Status
> ELSE IF @.CSOrder > 0
> SELECT * FROM MainOrder
> LEFT JOIN AnCJobs ON MainOrder.[id] = AnCJobs.[id]
> LEFT JOIN JobFlow ON JobFlow.[id] = MainOrder.[id]
> WHERE JobFlow.Component=6
> AND (Status = @.InProgress
> OR Status = @.Complete
> OR Status = @.Cancelled
> OR Status = @.Acknowledged)
> AND CSPerson = @.CSOrder
> ORDER BY DateToArrive, Status
> ELSE IF @.SalesOrder > 0
> SELECT * FROM MainOrder
> LEFT JOIN AnCJobs ON MainOrder.[id] = AnCJobs.[id]
> LEFT JOIN JobFlow ON JobFlow.[id] = MainOrder.[id]
> WHERE JobFlow.Component=6
> AND (Status = @.InProgress
> OR Status = @.Complete
> OR Status = @.Cancelled
> OR Status = @.Acknowledged)
> AND SalesPerson = @.SalesOrder
> ORDER BY DateToArrive, Status
> ELSE
> SELECT * FROM MainOrder
> LEFT JOIN AnCJobs ON MainOrder.[id] = AnCJobs.[id]
> LEFT JOIN JobFlow ON JobFlow.[id] = MainOrder.[id]
> WHERE JobFlow.Component=6
> AND (Status = @.InProgress
> OR Status = @.Complete
> OR Status = @.Cancelled
> OR Status = @.Acknowledged)
> ORDER BY DateToArrive, Status
>|||Ooops,
take the following line out of the code:::
SET @.sSQL = @.sSQL + ' AND SalesPerson = @.SalesOrder '
it was an oversight on my part, sorry,
Tony
"Tony Scott" wrote:
> Mia,
> Try something like this
>
> DECLARE @.strWHERE varchar(200)
> SET @.strWHERE = ''
> IF @.CSOrder > 0 AND @.SalesOrder > 0 THEN @.strWHERE = 'AND CSPerson =
> @.CSOrder AND SalesPerson = @.SalesOrder'
> IF @.CSOrder > 0 AND @.SalesOrder <> 0 THEN @.strWHERE = 'AND CSPerson =
> @.CSOrder'
> IF @.CSOrder <> 0 AND @.SalesOrder > 0 THEN @.strWHERE = 'AND SalesPerson =
> @.SalesOrder'
> DECLARE @.sSQL varchar(2000)
> SET @.sSQL = ''
> SET @.sSQL = @.sSQL + ' SELECT * FROM MainOrder '
> SET @.sSQL = @.sSQL + ' LEFT JOIN AnCJobs ON MainOrder.[id] = AnCJobs.[id] '
> SET @.sSQL = @.sSQL + ' LEFT JOIN JobFlow ON JobFlow.[id] = MainOrder.[id] '
> SET @.sSQL = @.sSQL + ' WHERE JobFlow.Component=6 '
> SET @.sSQL = @.sSQL + ' AND (Status = @.InProgress '
> SET @.sSQL = @.sSQL + ' OR Status = @.Complete '
> SET @.sSQL = @.sSQL + ' OR Status = @.Cancelled '
> SET @.sSQL = @.sSQL + ' OR Status = @.Acknowledged) '
> SET @.sSQL = @.sSQL + @.strWHERE
> SET @.sSQL = @.sSQL + ' AND SalesPerson = @.SalesOrder '
> SET @.sSQL = @.sSQL + ' AORDER BY DateToArrive, Status '
> EXEC (@.sSQL)
>
> Hope this assists,
> Tony
> "mia_cid@.hotmail.com" wrote:
>|||Thanks for the help guys,
Patrik I liked your idea, only I need:
AND CSPerson = CASE @.CSOrder WHEN > 0 THEN CSPerson ELSE @.CSOrder
Not
AND CSPerson = CASE @.CSOrder WHEN 0 THEN CSPerson ELSE @.CSOrder
Or
AND CSPerson >= CASE @.CSOrder WHEN 0 THEN CSPerson ELSE @.CSOrder
As if @.CSOrder = 0, then I want to select everything, so CSPerson needs
to be greater than 0.
Tony,
I had a couple of errors with your original code, so now have:
DECLARE @.strWHERE varchar(200)
SET @.strWHERE = ''
IF @.CSOrder > 0 AND @.SalesOrder > 0
SET @.strWHERE = 'AND CSPerson = @.CSOrder AND SalesPerson =
@.SalesOrder'
IF @.CSOrder > 0 AND @.SalesOrder <> 0
SET @.strWHERE = 'AND CSPerson = @.CSOrder'
IF @.CSOrder <> 0 AND @.SalesOrder > 0
SET @.strWHERE = 'AND SalesPerson = @.SalesOrder'
DECLARE @.sSQL varchar(2000)
SET @.sSQL = ''
SET @.sSQL = @.sSQL + ' SELECT * FROM MainOrder '
SET @.sSQL = @.sSQL + ' LEFT JOIN AnCJobs ON MainOrder.[id] =
AnCJobs.[id] '
SET @.sSQL = @.sSQL + ' LEFT JOIN JobFlow ON JobFlow.[id] =
MainOrder.[id] '
SET @.sSQL = @.sSQL + ' WHERE JobFlow.Component=6 '
SET @.sSQL = @.sSQL + ' AND (Status = @.InProgress '
SET @.sSQL = @.sSQL + ' OR Status = @.Complete '
SET @.sSQL = @.sSQL + ' OR Status = @.Cancelled '
SET @.sSQL = @.sSQL + ' OR Status = @.Acknowledged) '
SET @.sSQL = @.sSQL + @.strWHERE
SET @.sSQL = @.sSQL + ' ORDER BY DateToArrive, Status'
EXEC (@.sSQL)
Only I've getting a syntax error near the keyword 'ELSE'. If I comment
this code out and put a standard SELECT in it works okay.
Any ideas?|||You can do that.
AND CSPerson = CASE WHEN @.CSOrder > 0 THEN CSPerson ELSE @.CSOrder END
Although, it would probably be more efficient with dynamic sql in this
case.
I suggest that you use sp_executesql instaead of exec.
Read sommarskogs really good article about using dynamic sql.
http://www.sommarskog.se/dynamic_sql.html|||Thanks for that Partrik,
I just needed it the other way round:
AND CSPerson = CASE WHEN @.CSOrder > 0 THEN @.CSOrder ELSE CSPerson END
It works a treat, for the time being I'm going to stick with it as
it'll be running over a LAN, have bookmarked the site you recommended
and will have a read when I get the chance.
Thanks again to Tony for his help as well.

Can SQL Database work as normal without the ldf file?

i got a prolem with the space of my C drive as the dadta bas eis too big for
it i am not a llowed to get a bigger disk drive but i am given another one
that means i got two small disk drives my data isn't inpputed fully into the
database and the disk drive is full . I check the mdf file and found out tha
t
the mdf file stands a smaller space compared to the ldf file .So i am
thinking of deleting the ldf file to make space or else what should i do? I
really need help .The quick answer is: Read up on Recovery Models, then when you understand
the risk, set the recovery model to simple. This will solve your transaction
log disk space problem but introduce a data recovery problem so you'll want
to make frequent back-ups.
-Paul Nielsen
SQL Server MVP
www.SQLServerBible.com
"Fairy239" <Fairy239@.discussions.microsoft.com> wrote in message
news:39E5ACBA-822E-43CB-BF99-9E8AB374228C@.microsoft.com...
>i got a prolem with the space of my C drive as the dadta bas eis too big
>for
> it i am not a llowed to get a bigger disk drive but i am given another one
> that means i got two small disk drives my data isn't inpputed fully into
> the
> database and the disk drive is full . I check the mdf file and found out
> that
> the mdf file stands a smaller space compared to the ldf file .So i am
> thinking of deleting the ldf file to make space or else what should i do?
> I
> really need help .|||
"Paul Nielsen (MVP)" wrote:

> The quick answer is: Read up on Recovery Models, then when you understand
> the risk, set the recovery model to simple. This will solve your transacti
on
> log disk space problem but introduce a data recovery problem so you'll wan
t
> to make frequent back-ups.
> -Paul Nielsen
> SQL Server MVP
i hope you can answer my qn and not asking me to read up stuff this stuff is
urgent need to besloved quickly i got a limit of time for completing the job
thank you
> www.SQLServerBible.com
>
> "Fairy239" <Fairy239@.discussions.microsoft.com> wrote in message
> news:39E5ACBA-822E-43CB-BF99-9E8AB374228C@.microsoft.com...
>
>|||No, the database can't run without an ldf file. Try moving either the ldf
or mdf file to the new drive - detach the database and then attach with
move. This article show how that works:
http://msdn2.microsoft.com/en-us/library/ms187858.aspx
http://support.microsoft.com/defaul...kb;en-us;818565
This posting is provided "AS IS" with no warranties, and confers no rights.
Use of included script samples are subject to the terms specified at
http://www.microsoft.com/info/cpyright.htm
"Fairy239" <Fairy239@.discussions.microsoft.com> wrote in message
news:CCB6FE51-B406-4C6B-96BC-5116BDDA9E7E@.microsoft.com...
>
> "Paul Nielsen (MVP)" wrote:
>
> i hope you can answer my qn and not asking me to read up stuff this stuff
> is
> urgent need to besloved quickly i got a limit of time for completing the
> job
> thank you|||to truncate the transaction log try doing :
backup log database_name with truncate_only
then
dbcc shrinklog('Log_File_name')
do remember to take a back up of the db prior to doing this.
also read up the recovery models as stated previously.
Harshal Mistry
"Roger Wolter[MSFT]" <rwolter@.online.microsoft.com> wrote in message news:%23qFHtWtVGHA.5012@.TK2M
SFTNGP10.phx.gbl...
> No, the database can't run without an ldf file. Try moving either the ldf
> or mdf file to the new drive - detach the database and then attach with
> move. This article show how that works:
>
> http://msdn2.microsoft.com/en-us/library/ms187858.aspx
> http://support.microsoft.com/defaul...kb;en-us;818565
>
>
>
> --
> This posting is provided "AS IS" with no warranties, and confers no rights
.
> Use of included script samples are subject to the terms specified at
> http://www.microsoft.com/info/cpyright.htm
>
> "Fairy239" <Fairy239@.discussions.microsoft.com> wrote in message
> news:CCB6FE51-B406-4C6B-96BC-5116BDDA9E7E@.microsoft.com...
>
>

Can Reportviewer control size be dynamic

When using an asp.net 2.0 reportviewer control to display an SSRS report, the size of the report is always bigger that the control it is housed in. So you have to scroll back and forth.

Can't the Reportviewer control size be dynamic? What is the general parctice on this?

Thanks

ReportControl and SSRS newbie!

Hi,

I'm not sure what do you mean by "control the size dynamically". Based on my knowledge, if the report does not need to scroll verticall within the iframe, you can handle it in two ways.

First, assign the height, width value in a hard coded way, you can refer the following code which shares the solution.
http://www.codeproject.com/sqlrs/ReportViewer2005.asp

Second, you can set two properties to make the repoertviewer autosize:

AsyncRendering = false; and SizeToReportContent = true;

Thanks.