Dyanamic Where Clause

Can anyone advise how i resolve this in SQL, I have the following Stored
procedure :
CREATE PROCEDURE sp_jp_report_won_lost_open_test
@.datestart as varchar(12),
@.dateend as varchar(12),
@.Region as varchar(25), -- e.g. Scotland
@.Territory as int, --ie 901232
@.ReportType as Varchar(7), -- eg. Won or Lost or Ordered
@.mv as money, -- eg Minimum Value of Project
@.mx as money -- eg Maximum Value of Project
AS
set nocount on
If rtrim(@.ReportType)='Open' and LEN(rtrim(@.Region)) = 1
Begin
SELECT t.Description, p.LISANo, p.CreatedDate, p.FollowUpDate,
p.ExpectedOrderDate, p.Name, p.Status, p.ProjectStatus, q.TotalValue,
p.SpecifierCompanyDisplayName, p.Notes
FROM dbo.TblProject p INNER JOIN
dbo.TblTerritory t ON p.TerritoryCode = t.Code LEFT
OUTER JOIN
(SELECT LISANo, MIN(QuoteDate) QuoteDate,
MIN(QuoteNo) QuoteNo
FROM tblquotation_header
WHERE (TblQuotation_Header.TotalValue <=
@.mx AND TblQuotation_Header.TotalValue >= @.mv)
GROUP BY LISANo) x ON x.LISANo = p.LISANo LEFT
OUTER JOIN
dbo.TblQuotation_Header q ON x.QuoteDate = q.QuoteDate
AND x.QuoteNo = q.QuoteNo AND p.LISANo = q.LISANo
WHERE (p.Status = 'Active') AND (p.ProjectStatus = 'Open') AND
(p.CreatedDate >= CONVERT(DATETIME, @.DateStart, 102)) AND
(p.CreatedDate <= CONVERT(DATETIME, @.DateEnd, 102))
AND (t.CurrencySymbol = @.Region)
ORDER BY t.Description
End
If @.mv>0 then i want the additional clause "WHERE (is not null q.TotalValue)
" included in the SQL statement, how do i acheive this ?
Thanks in anticipation
JohnJohn
http://www.sommarskog.se/dyn-search.html
"John" <topguy75@.hotmail.com> wrote in message
news:43a954bf$0$23290$db0fefd9@.news.zen.co.uk...
> Can anyone advise how i resolve this in SQL, I have the following Stored
> procedure :
> CREATE PROCEDURE sp_jp_report_won_lost_open_test
> @.datestart as varchar(12),
> @.dateend as varchar(12),
> @.Region as varchar(25), -- e.g. Scotland
> @.Territory as int, --ie 901232
> @.ReportType as Varchar(7), -- eg. Won or Lost or Ordered
> @.mv as money, -- eg Minimum Value of Project
> @.mx as money -- eg Maximum Value of Project
> AS
> set nocount on
> If rtrim(@.ReportType)='Open' and LEN(rtrim(@.Region)) = 1
> Begin
> SELECT t.Description, p.LISANo, p.CreatedDate, p.FollowUpDate,
> p.ExpectedOrderDate, p.Name, p.Status, p.ProjectStatus, q.TotalValue,
> p.SpecifierCompanyDisplayName, p.Notes
> FROM dbo.TblProject p INNER JOIN
> dbo.TblTerritory t ON p.TerritoryCode = t.Code LEFT
> OUTER JOIN
> (SELECT LISANo, MIN(QuoteDate) QuoteDate,
> MIN(QuoteNo) QuoteNo
> FROM tblquotation_header
> WHERE (TblQuotation_Header.TotalValue <=
> @.mx AND TblQuotation_Header.TotalValue >= @.mv)
> GROUP BY LISANo) x ON x.LISANo = p.LISANo LEFT
> OUTER JOIN
> dbo.TblQuotation_Header q ON x.QuoteDate =
> q.QuoteDate AND x.QuoteNo = q.QuoteNo AND p.LISANo = q.LISANo
> WHERE (p.Status = 'Active') AND (p.ProjectStatus = 'Open') AND
> (p.CreatedDate >= CONVERT(DATETIME, @.DateStart, 102)) AND
> (p.CreatedDate <= CONVERT(DATETIME, @.DateEnd, 102))
> AND (t.CurrencySymbol = @.Region)
> ORDER BY t.Description
> End
>
> If @.mv>0 then i want the additional clause "WHERE (is not null
> q.TotalValue) " included in the SQL statement, how do i acheive this ?
> Thanks in anticipation
> John
>|||Use dynamic SQL
Look at
http://www.sommarskog.se/dynamic_sql.html
Regards
Amish|||"John" <topguy75@.hotmail.com> wrote in message
news:43a954bf$0$23290$db0fefd9@.news.zen.co.uk...
> Can anyone advise how i resolve this in SQL, I have the following Stored
> procedure :
> CREATE PROCEDURE sp_jp_report_won_lost_open_test
> @.datestart as varchar(12),
> @.dateend as varchar(12),
> @.Region as varchar(25), -- e.g. Scotland
> @.Territory as int, --ie 901232
> @.ReportType as Varchar(7), -- eg. Won or Lost or Ordered
> @.mv as money, -- eg Minimum Value of Project
> @.mx as money -- eg Maximum Value of Project
> AS
> set nocount on
> If rtrim(@.ReportType)='Open' and LEN(rtrim(@.Region)) = 1
> Begin
> SELECT t.Description, p.LISANo, p.CreatedDate, p.FollowUpDate,
> p.ExpectedOrderDate, p.Name, p.Status, p.ProjectStatus, q.TotalValue,
> p.SpecifierCompanyDisplayName, p.Notes
> FROM dbo.TblProject p INNER JOIN
> dbo.TblTerritory t ON p.TerritoryCode = t.Code LEFT
> OUTER JOIN
> (SELECT LISANo, MIN(QuoteDate) QuoteDate,
> MIN(QuoteNo) QuoteNo
> FROM tblquotation_header
> WHERE (TblQuotation_Header.TotalValue <=
> @.mx AND TblQuotation_Header.TotalValue >= @.mv)
> GROUP BY LISANo) x ON x.LISANo = p.LISANo LEFT
> OUTER JOIN
> dbo.TblQuotation_Header q ON x.QuoteDate =
> q.QuoteDate AND x.QuoteNo = q.QuoteNo AND p.LISANo = q.LISANo
> WHERE (p.Status = 'Active') AND (p.ProjectStatus = 'Open') AND
> (p.CreatedDate >= CONVERT(DATETIME, @.DateStart, 102)) AND
> (p.CreatedDate <= CONVERT(DATETIME, @.DateEnd, 102))
> AND (t.CurrencySymbol = @.Region)
> ORDER BY t.Description
> End
>
> If @.mv>0 then i want the additional clause "WHERE (is not null
> q.TotalValue) " included in the SQL statement, how do i acheive this ?
> Thanks in anticipation
> John
One quick fix could be this:
IF @.mv>0
SELECT ...
WHERE...
AND q.TotalValue is not null
..
ELSE
SELECT "your query as is"|||where ...
and ((@.mv<=0) or (q.TotalValue is not null) )|||Thanks Alex, simple but effective :-)
"Alexander Kuznetsov" <AK_TIREDOFSPAM@.hotmail.COM> wrote in message
news:1135183436.404303.48580@.z14g2000cwz.googlegroups.com...
> where ...
> and ((@.mv<=0) or (q.TotalValue is not null) )
>

Dyanamic stored procedures

Hi all,
I'm building a search function and I need do it using stored procedures. I'm searching a table on 'Name' 'email address' and 'key words'. (basically 3 columns). The user must be able to use it as
SELECT ......... WHERE (NAME LIKE @.name) AND/OR (Email LIKE @.email) AND/OR ..........
Can some one tell me how to handle this in a stored procedure. By the way is this approch correct. Please tell me if there is a better method to tackle this situation.
Thanks,
-VJ

I always generate a dynamic SQL statement based on what the user has entered and then execute it or use sp_executesql to run it. Some will say that dynamic SQL is bad because the execution plan doesn't get cached like normal procs, but you can get better performance with dynamic SQL sometimes because since the query criteria can change dramtically, the cached execution plan may be a bad one. Always bets to time and view the execution plan in QA, but my experience is that dynamic SQL within the stored proc works best when there are varying search parameters.

|||If it's just 3 columns you're validating, you should just use case statements instead of utilizing dynamic sql.
selelct * from table where -- normal stuff
Name Like (
CASE
WHEN @.name IS NOT NULL THEN @.name
ELSE Name
END
)

-----
A lot of times, I'll validate a string for nulls and empty strings, so I'll usually have this function in most of my databases.
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
CREATE FUNCTION [dbo].[IsEmptyString]
(
@.String VarChar(2000)
)
RETURNS BIT AS
BEGIN
DECLARE @.ReturnVar BIT
IF (@.String IS NOT NULL AND @.String <> '') -- you can trim if you are avoiding whitespaces.
SET @.ReturnVar = 0
ELSE
SET @.ReturnVar = 1

RETURN @.ReturnVar
END
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

|||Test it both ways. I've done it with case statements and gotten terrible performance compared with dynamic SQL. It all depends on the situation. Having 3 or 6 or 20 columns really isn't the issue; it's how the procedure performs when varying numbers or combinations of the criteria are entered. I've seen demos where the dynamic SQL blew away using case or isnull.|||You're right that it's completely situational in terms of performance,and many will do their best to keep the actual logic in code as much aspossible. From personal experience, iterations through decentsized databases (2 million records in each of the major tables), a casestatement will perform fairly well as compared to utilizing dynamicsql. It helps further when you require calculations to be madeper record to assist in it's selection criteria (Distance for example),and the one of the factors is based on the parameters. In caseslike this, a case statement tends to outweigh a dump of string to beexecuted in a stored procedure.
Create Procedure KraGiE.ExecuteString
@.SQL varchar (2000)
AS
EXECUTE( @.SQL )
GO
For some reason, that makes me cringe, and I'm sure many database administrators will plainly say "no."

Dyanamic Sql

Hi I was building a dynamic sql with output please tell me where I am wrong.
Or Tell me right way to do it

My table name is variable (Stock name)
I want close price for given Stock

CREATE PROCEDURE SP_GET_CLOSE
@.TableName as varchar(50),
@.close_price as varchar(50) output

AS
Declare @.SQL NVarChar(1000)
Declare @.high varchar(50)
SET @.SQL = 'Select top 1 @.close_price = [close] from '+ @.TableName+ ' order by trade_date desc'

print @.SQL
Exec sp_executesql @.SQL, N'@.close_price varchar(50) output' , @.close_price
GOWaht abotu this idea?

CREATE PROCEDURE SP_GET_CLOSE
@.TableName as varchar(50),
@.close_price as varchar(50) output

AS
Declare @.SQL NVarChar(1000)
Declare @.high varchar(50)
SET @.SQL = 'Select top 1 [close] from '+ @.TableName+ ' order by trade_date desc'

create table #tmp(ret varchar(450))

insert #tmp
exec(@.SQL)

select @.close_price=cast(ret as money) from #tmp|||Yes That seems a good idea but I have some 200 trading Scripts in month and this would create lot of #tmp tables I thought it should be possible to send dynamic SQl a output variable.
Besides I am having lot of similar quiries in mind for my project

Thanks|||Originally posted by shriya
Yes That seems a good idea but I have some 200 trading Scripts in month and this would create lot of #tmp tables I thought it should be possible to send dynamic SQl a output variable.
Besides I am having lot of similar quiries in mind for my project

Thanks

Temporary table will dropped after procedure execution. BTW, sql server is using temporary objects during any operations (even like select from with order by) so do not worry about this at all.

Dyanamic date changing to current date when printing PDF

I have a dynamic date in my reporting services report that displays
the previous month and year we are reporting on. Here is the
expression: =MonthName(Month(Now().AddMonths(-1))) & " " &
Year(Now())
When I create a pdf from this it displays correctly, but when I print
it, it always puts the current month and year in this field. Does
anyone know why and how to prevent it from doing this when I print the
pdf?
Thanks for any help you can give.
-CarlyJust to clarify, you create a pdf then print the pdf from within Adobe
Acrobat and while the pdf file you see on the screen has September
2007, for instance, the printed pdf will have October 2007?
Is that correct? You're printing the pdf, not printing the report
directly from Report Manager?
- C
On Oct 5, 10:22 am, caroline.fe...@.sdncommunications.com wrote:
> I have a dynamic date in my reporting services report that displays
> the previous month and year we are reporting on. Here is the
> expression: =MonthName(Month(Now().AddMonths(-1))) & " " &
> Year(Now())
> When I create a pdf from this it displays correctly, but when I print
> it, it always puts the current month and year in this field. Does
> anyone know why and how to prevent it from doing this when I print the
> pdf?
> Thanks for any help you can give.
> -Carly