July 14, 2011 at 6:42 am
So I'm guessing that this is a dynamic parameter and not static value
yep I talked to the dev again and he said it was a dynamic parameter..so they have to use it (?)
July 14, 2011 at 8:35 am
krypto69 (7/14/2011)
So I'm guessing that this is a dynamic parameter and not static value
yep I talked to the dev again and he said it was a dynamic parameter..so they have to use it (?)
Would you please post the original (dynamic) query or describe the way how the dynamic part works (e.g. is it a dynamic SQL or some .NET stuff or something else)?
July 14, 2011 at 8:55 am
sure..
exec spPaymentViewGetPagedByUserId @PageIndex=0, @PageSize=25, @PaymentStatus='P, VR, AM'
USE [dbVBank]
GO
/****** Object: StoredProcedure [dbo].[spPaymentViewGetPagedByUserId] Script Date: 07/14/2011 10:55:23 ******/
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[spPaymentViewGetPagedByUserId]
@PageIndex int,
@PageSize int,
@PaymentStatus varchar(100),
@userid varchar(25) = null,
@PayeeId int = null,
@Category varchar(48) = null,
@DateFrom datetime = null,
@DateTo datetime = null,
@AmountMin real = null,
@AmountMax real = null,
@IsElectronic bit = null
AS
SET NOCOUNT ON
-- SQL Statement and Where clause variables
DECLARE @sqlStatement nvarchar(2048)
DECLARE @whereSql nvarchar(512)
-- Define Columns to select
SET @sqlStatement =
'WITH PaymentViewPaged AS
(
SELECT
ROW_NUMBER() OVER (ORDER BY [UserID], [PaymentRefNo]) AS RowIndex
,[Name]
,[PayeeID]
,[Category]
,[PayeeRefNo]
,[PayeeStatus]
,[UserID]
,[PaymentRefNo]
,[InstanceNo]
,[InstancePmtAmt]
,[InstancePmtDate]
,[SettlementDate]
,[TransmissionDate]
,[PaymentStatus]
,[SpecialNotes]
,[CheckTraceNumber]
,[CountTotal]
,[Frequency]
,[AccountNumber]
,[IsElectronic]
FROM [dbo].[PaymentView] a (nolock)
INNER JOIN func_charlist_to_table(@PaymentStatus, DEFAULT) b ON a.[PaymentStatus] = b.[str]
'
-- Define Where clause
SET @whereSql = 'WHERE 1 = 1'
IF @userid Is Not Null
SET @whereSql = @whereSql + ' AND [UserId] = @userid'
IF @PayeeId Is Not Null
SET @whereSql = @whereSql + ' AND [PayeeId] = @PayeeId'
IF @Category Is Not Null
SET @whereSql = @whereSql + ' AND [Category] = @Category'
IF @AmountMin Is Not Null
SET @whereSql = @whereSql + ' AND [InstancePmtAmt] >= @AmountMin'
IF @AmountMax Is Not Null
SET @whereSql = @whereSql + ' AND [InstancePmtAmt] <= @AmountMax'
IF @DateFrom Is Not Null
SET @whereSql = @whereSql + ' AND [InstancePmtDate] >= @DateFrom'
IF @DateTo Is Not Null
SET @whereSql = @whereSql + ' AND [InstancePmtDate] >= @DateTo'
IF @IsElectronic Is Not Null
SET @whereSql = @whereSql + ' AND [IsElectronic] = @IsElectronic'
SET @sqlStatement = @sqlStatement + @whereSql + '
)
SELECT * FROM PaymentViewPaged
WHERE RowIndex between @PageIndex * @PageSize + 1 and (@PageIndex + 1) * @PageSize
-- Get Total Count
SELECT Count(''x'') FROM [dbo].[PaymentView] a (nolock)
INNER JOIN func_charlist_to_table(@PaymentStatus, DEFAULT) b ON a.[PaymentStatus] = b.[str]
' + @whereSql
-- Execute SQL statement
EXEC sp_executesql
@sqlStatement,
-- Statement parameters
N'
@PageIndex int,
@PageSize int,
@PaymentStatus varchar(100),
@userid varchar(25),
@PayeeId int = null,
@Category varchar(48),
@DateFrom datetime,
@DateTo datetime,
@AmountMin real,
@AmountMax real,
@IsElectronic bit
',
-- pass sp arguments to sql statement
@PageIndex,
@PageSize,
@PaymentStatus,
@PayeeId,
@Category,
@DateFrom,
@DateTo,
@AmountMin,
@AmountMax,
@IsElectronic
July 14, 2011 at 9:16 am
According to that - since you're dynmiacally building the query, so definitely don't need the function in the JOIN. Instead - use the parameter differently:
SELECT * FROM PaymentViewPaged
WHERE RowIndex between @PageIndex * @PageSize + 1 and (@PageIndex + 1) * @PageSize
-- Get Total Count
SELECT Count(''x'') FROM [dbo].[PaymentView] a (nolock)
where a.[PaymentStatus] in ('+@PaymentStatus + ')'
+ @whereSql
You may need to adjust the @paymentstatus ever so slightly to return the values formatted as
'value','value','value'
rather than
value, value, value
Either way, your query ends up running faster.
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
July 14, 2011 at 9:51 am
Matt Miller (#4) (7/14/2011)
According to that - since you're dynmiacally building the query, so definitely don't need the function in the JOIN. Instead - use the parameter differently:
SELECT * FROM PaymentViewPaged
WHERE RowIndex between @PageIndex * @PageSize + 1 and (@PageIndex + 1) * @PageSize
-- Get Total Count
SELECT Count(''x'') FROM [dbo].[PaymentView] a (nolock)
where a.[PaymentStatus] in ('+@PaymentStatus + ')'
+ @whereSql
You may need to adjust the @paymentstatus ever so slightly to return the values formatted as
'value','value','value'
rather than
value, value, value
Either way, your query ends up running faster.
(Almost) exactly what I had in mind! I would just investigate the root cause for the NOLOCK hint and eliminate that, too. 😉
July 14, 2011 at 9:57 am
Not needed anymore.
Lutz finally understood the quoting system.
:hehe:
July 14, 2011 at 10:16 am
@ninja: I edited the quote range in my reply. You might need to adjust yours to reflect that, too 😀
July 14, 2011 at 10:29 am
LutzM (7/14/2011)
@Ninja: I edited the quote range in my reply. You might need to adjust yours to reflect that, too 😀
Done!
July 14, 2011 at 10:30 am
Ninja's_RGR'us (7/14/2011)
Not needed anymore.Lutz finally understood the quoting system.
NO. :-P:-D
:hehe:
July 14, 2011 at 10:32 am
We are having trouble with the syntax -
When I change it to what you suggested I get an error:
Msg 102, Level 15, State 1, Procedure spPaymentViewGetPagedByUserId, Line 79
Incorrect syntax near ')'.
Msg 102, Level 15, State 1, Procedure spPaymentViewGetPagedByUserId, Line 84
Incorrect syntax near 'x'.
Here's the procedure the way I have it -
USE [dbVBank]
GO
/****** Object: StoredProcedure [dbo].[spPaymentViewGetPagedByUserId] Script Date: 07/14/2011 10:55:23 ******/
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[spPaymentViewGetPagedByUserId]
@PageIndex int,
@PageSize int,
@PaymentStatus varchar(100),
@userid varchar(25) = null,
@PayeeId int = null,
@Category varchar(48) = null,
@DateFrom datetime = null,
@DateTo datetime = null,
@AmountMin real = null,
@AmountMax real = null,
@IsElectronic bit = null
AS
SET NOCOUNT ON
-- SQL Statement and Where clause variables
DECLARE @sqlStatement nvarchar(2048)
DECLARE @whereSql nvarchar(512)
-- Define Columns to select
SET @sqlStatement =
'WITH PaymentViewPaged AS
(
SELECT
ROW_NUMBER() OVER (ORDER BY [UserID], [PaymentRefNo]) AS RowIndex
,[Name]
,[PayeeID]
,[Category]
,[PayeeRefNo]
,[PayeeStatus]
,[UserID]
,[PaymentRefNo]
,[InstanceNo]
,[InstancePmtAmt]
,[InstancePmtDate]
,[SettlementDate]
,[TransmissionDate]
,[PaymentStatus]
,[SpecialNotes]
,[CheckTraceNumber]
,[CountTotal]
,[Frequency]
,[AccountNumber]
,[IsElectronic]
FROM [dbo].[PaymentView] a (nolock)
INNER JOIN func_charlist_to_table(@PaymentStatus, DEFAULT) b ON a.[PaymentStatus] = b.[str]
'
-- Define Where clause
SET @whereSql = 'WHERE 1 = 1'
IF @userid Is Not Null
SET @whereSql = @whereSql + ' AND [UserId] = @userid'
IF @PayeeId Is Not Null
SET @whereSql = @whereSql + ' AND [PayeeId] = @PayeeId'
IF @Category Is Not Null
SET @whereSql = @whereSql + ' AND [Category] = @Category'
IF @AmountMin Is Not Null
SET @whereSql = @whereSql + ' AND [InstancePmtAmt] >= @AmountMin'
IF @AmountMax Is Not Null
SET @whereSql = @whereSql + ' AND [InstancePmtAmt] <= @AmountMax'
IF @DateFrom Is Not Null
SET @whereSql = @whereSql + ' AND [InstancePmtDate] >= @DateFrom'
IF @DateTo Is Not Null
SET @whereSql = @whereSql + ' AND [InstancePmtDate] >= @DateTo'
IF @IsElectronic Is Not Null
SET @whereSql = @whereSql + ' AND [IsElectronic] = @IsElectronic'
SET @sqlStatement = @sqlStatement + @whereSql +
)
SELECT * FROM PaymentViewPaged
WHERE RowIndex between @PageIndex * @PageSize + 1 and (@PageIndex + 1) * @PageSize
---- Get Total Count
SELECT Count(''x'') FROM [dbo].[PaymentView] a (nolock)
----** two lines below changed by rob to test
--INNER JOIN func_charlist_to_table(@PaymentStatus, DEFAULT) b ON a.[PaymentStatus] = b.[str]
-- '+ @whereSql
where a.[PaymentStatus] in ('+@PaymentStatus + '))
+ @whereSql
-- Execute SQL statement
EXEC sp_executesql
@sqlStatement,
-- Statement parameters
N'
@PageIndex int,
@PageSize int,
@PaymentStatus varchar(100),
@userid varchar(25),
@PayeeId int = null,
@Category varchar(48),
@DateFrom datetime,
@DateTo datetime,
@AmountMin real,
@AmountMax real,
@IsElectronic bit
',
-- pass sp arguments to sql statement
@PageIndex,
@PageSize,
@PaymentStatus,
@PayeeId,
@Category,
@DateFrom,
@DateTo,
@AmountMin,
@AmountMax,
@IsElectronic
July 14, 2011 at 10:37 am
---- Get Total Count
SELECT Count(''x'') FROM [dbo].[PaymentView] a (nolock)
should be
---- Get Total Count
SELECT Count('x') FROM [dbo].[PaymentView] a (nolock)
July 14, 2011 at 10:42 am
I think there's a little more to change...
Change the block before the comment -- Execute SQL statement
SET @sqlStatement = @sqlStatement + @whereSql + '
)
SELECT * FROM PaymentViewPaged
WHERE RowIndex between @PageIndex * @PageSize + 1 and (@PageIndex + 1) * @PageSize
-- Get Total Count
SELECT Count(''x'') FROM [dbo].[PaymentView] a (nolock)
----** two lines below changed by rob to test
--INNER JOIN func_charlist_to_table(@PaymentStatus, DEFAULT) b ON a.[PaymentStatus] = b.[str]
where a.[PaymentStatus] in (+@PaymentStatus + ))
' + @whereSql
It seems like the single quotation marks around that section are a little out of sync...
July 14, 2011 at 11:24 am
That worked as far as accepting the altered proc...but when I go to run I get an error-
Msg 102, Level 15, State 1, Line 47
Incorrect syntax near ')'.
this is what I run -
exec spPaymentViewGetPagedByUserId @PageIndex=0, @PageSize=25, @PaymentStatus='P, VR, AM'
July 14, 2011 at 11:41 am
It's hard to troubleshoot without having the table defs referenced by the sproc.
You might need to run the code with defiined parameter instead of calling the sproc with input variables. Alternatively, you could provide the table defs for the tables involved so we can simulate the behavior.
July 14, 2011 at 1:27 pm
krypto69 (7/14/2011)
That worked as far as accepting the altered proc...but when I go to run I get an error-Msg 102, Level 15, State 1, Line 47
Incorrect syntax near ')'.
this is what I run -
exec spPaymentViewGetPagedByUserId @PageIndex=0, @PageSize=25, @PaymentStatus='P, VR, AM'
Try inserting this in just after you start the stored procedure:
select @PaymentStatus= ''''+REPLACE(@PaymentStatus,',',''',''')+''''
This is the "adjustment" I mentioned earlier (the IN needs to have string values surrounded in quotes to operate correctly).
this may fix your error. If not - then capture the SQL it wants to execute as a string and execute that on its own to see where the syntax error is.
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
Viewing 15 posts - 16 through 30 (of 30 total)
You must be logged in to reply to this topic. Login to reply