April 1, 2008 at 12:43 am
Hello everyone, i m using SQL Server 2005, here is my Search SP, here is a bit problem , the fileds @RangeFrom and
@RangeTo shows the Form no range, here i found some problem, if the user selects (From app level , Front end using C#)
only the FromRange to search any specific FormNo than it gets the >= FormNo according to my query, as this query satifies
the condition if user select the rane i mean set From and to Values in the text boxes, but now if it select only one , than
how can i get the only one result if i used
IF @RangeFrom IS NOT NULL
SELECT @sql = @sql + ' AND TvsRecords.SystemFormNo = @xrangefrom' This satisfies but if user send 2 values (From and TO wants to get Nos within that range)
than problem occurs, kindly tell me how i deal this condition in my SP.PLz reply me asap.Its very urgent.
Thanx in Advance.
CREATE PROCEDURE [dbo].[SearchSP]
@RangeFrom int = null,
@RangeTo int= null,
@TvsFormStatusCode int= null
AS
DECLARE @sql nvarchar(4000)
DECLARE @paramlist nvarchar(4000)
SELECT @sql = N' SELECT dbo.TvsRecords.TvsRecordID, dbo.TvsRecords.SystemFormNo,dbo.TvsRecords.TvsFormNo, dbo.TvsRecords.TvsFormDate, dbo.TvsRecords.CreateDate,
dbo.TvsFormStatusCodes.TvsFormStatusName, dbo.SystemUsers.FullName AS OperatorName, SystemUsers_1.FullName AS QCAgentName
FROM dbo.TvsRecords INNER JOIN
dbo.TvsFormStatusCodes ON dbo.TvsRecords.TvsFormStatusCode = dbo.TvsFormStatusCodes.TvsFormStatusCode INNER JOIN
dbo.SystemUsers ON dbo.TvsRecords.EntryOperatorUserID = dbo.SystemUsers.SystemUserID LEFT OUTER JOIN
dbo.SystemUsers AS SystemUsers_1 ON dbo.TvsRecords.QCUserID = SystemUsers_1.SystemUserID
WHERE 1 = 1'
IF @RangeFrom IS NOT NULL
SELECT @sql = @sql + ' AND TvsRecords.SystemFormNo >= @xrangefrom'
IF @RangeTo IS NOT NULL
SELECT @sql = @sql + ' AND TvsRecords.SystemFormNo <= @xrangeto'
IF @FromTvsFormNo IS NOT NULL
SELECT @sql = @sql + ' AND TvsRecords.TvsFormNo >= @xfromtvsformno'
IF @ToTvsFormNo IS NOT NULL
SELECT @sql = @sql + ' AND TvsRecords.TvsFormNo <= @xtotvsformno'
IF @TvsFormStatusCode IS NOT NULL
SELECT @sql = @sql + ' AND TvsRecords.TvsFormStatusCode = @xtvsFormStatusCode'
SELECT @paramlist = N'@xrangefrom int,
@xrangeto int,
@xtvsFormStatusCode int'
EXEC sp_executesql @sql, @paramlist, @RangeFrom, @RangeTo, @TvsFormStatusCode
April 1, 2008 at 1:11 am
Here's one way:
CREATE PROCEDURE [dbo].[SearchSP]
@RangeFrom int = null,
@RangeTo int= null,
@TvsFormStatusCode int= null
AS
Set @RangeFrom = Coalesce(@RangeFrom, (-65536*65536))--max low
Set @RangeTo = Coalesce(@RangeTo, (65536*65535)+1 )--max high
SELECT dbo.TvsRecords.TvsRecordID, dbo.TvsRecords.SystemFormNo,dbo.TvsRecords.TvsFormNo, dbo.TvsRecords.TvsFormDate, dbo.TvsRecords.CreateDate,
dbo.TvsFormStatusCodes.TvsFormStatusName, dbo.SystemUsers.FullName AS OperatorName, SystemUsers_1.FullName AS QCAgentName
FROM dbo.TvsRecords
INNER JOIN dbo.TvsFormStatusCodes ON dbo.TvsRecords.TvsFormStatusCode = dbo.TvsFormStatusCodes.TvsFormStatusCode
INNER JOIN dbo.SystemUsers ON dbo.TvsRecords.EntryOperatorUserID = dbo.SystemUsers.SystemUserID
LEFT OUTER JOIN dbo.SystemUsers AS SystemUsers_1 ON dbo.TvsRecords.QCUserID = SystemUsers_1.SystemUserID
WHERE 1 = 1
AND TvsRecords.SystemFormNo >= @xrangefrom
AND TvsRecords.SystemFormNo <= @xrangeto
AND ( (TvsRecords.TvsFormStatusCode = @xtvsFormStatusCode) OR (@xtvsFormStatusCode IS NULL) )
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
April 1, 2008 at 8:08 am
Hmm, upon further review, I don't think that my first reply actually answered you question. Part of the problem for me is that [font="Courier New"]@FromTvsFormNo[/font] and [font="Courier New"]@ToTvsFormNo[/font] are never defined in your code. I'll just assume that they are supposed to be parameters also:
CREATE PROCEDURE [dbo].[SearchSP]
@RangeFrom int = null,
@RangeTo int= null,
@FromTvsFormNo int = null,
@ToTvsFormNo int= null,
@TvsFormStatusCode int= null
AS
Set @RangeFrom = Coalesce(@RangeFrom, (-65536*65536))--max low
Set @RangeTo = Coalesce(@RangeTo, (65536*65535)+65335 )--max high
--set the TvsFromNo range correctly:
IF @FromTvsFormNo Is NULL
BEGIN
IF @ToTvsFormNo Is Null
Begin--unrestricted range
Set @FromTvsFormNo = Coalesce(@RangeFrom, (-65536*65536))--max lo
Set @ToTvsFormNo = Coalesce(@RangeFrom, -(-65536*65536+1))--max hi
End
ELSE--only max out From
Set @FromTvsFormNo = Coalesce(@RangeFrom, (-65536*65536))--max lo
END
ELSE
BEGIN
IF @ToTvsFormNo Is Null--only max out To
Set @ToTvsFormNo = Coalesce(@RangeFrom, -(-65536*65536+1))--max hi
--ELSE neither is null, so use both as they are
END
SELECT dbo.TvsRecords.TvsRecordID, dbo.TvsRecords.SystemFormNo,dbo.TvsRecords.TvsFormNo, dbo.TvsRecords.TvsFormDate, dbo.TvsRecords.CreateDate,
dbo.TvsFormStatusCodes.TvsFormStatusName, dbo.SystemUsers.FullName AS OperatorName, SystemUsers_1.FullName AS QCAgentName
FROM dbo.TvsRecords
INNER JOIN dbo.TvsFormStatusCodes ON dbo.TvsRecords.TvsFormStatusCode = dbo.TvsFormStatusCodes.TvsFormStatusCode
INNER JOIN dbo.SystemUsers ON dbo.TvsRecords.EntryOperatorUserID = dbo.SystemUsers.SystemUserID
LEFT OUTER JOIN dbo.SystemUsers AS SystemUsers_1 ON dbo.TvsRecords.QCUserID = SystemUsers_1.SystemUserID
WHERE 1 = 1
AND TvsRecords.SystemFormNo >= @RangeFrom
AND TvsRecords.SystemFormNo <= @RangeTo
AND TvsRecords.TvsFormNo >= @FromTvsFormNo
AND TvsRecords.TvsFormNo <= @ToTvsFormNo
AND ( (TvsRecords.TvsFormStatusCode = @TvsFormStatusCode) OR (@TvsFormStatusCode IS NULL) )
[/quote]
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply