Help with dynamic stored procedure

Question

Post reply

Help with dynamic stored procedure

SQL_Kills

Hall of Fame

Points: 3154
More actions
February 15, 2016 at 4:53 pm

#308463

Hi,
I have the below table called PaymentsLog
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[PaymentsLog](
[ID] [int] NULL,
[DebtorName] [nvarchar](255) NULL,
[CreditController] [nvarchar](255) NULL,
[DueDate] [datetime] NULL,
[CurrencyCode] [varchar](10) NULL,
[Amount] [float] NULL,
[InjectedDate] [datetime] NULL
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
insert into PaymentsLog values (1,COMPANY A','Jack Daniels','25/09/2015 00:00:00','GBP','100','01/09/2015 10:00:09')
insert into PaymentsLog values (1,COMPANY A','Jack Daniels','25/09/2015 00:00:00','GBP','100','03/09/2015 10:00:09')
insert into PaymentsLog values (1,COMPANY A','Jack Daniels','25/09/2015 00:00:00','GBP','100','04/09/2015 10:00:09')
insert into PaymentsLog values (1,COMPANY A','Jack Daniels','25/09/2015 00:00:00','GBP','100','02/09/2015 10:00:09')
insert into PaymentsLog values (1,COMPANY A','Jack Daniels','25/09/2015 00:00:00','GBP','100','05/09/2015 10:00:09')
insert into PaymentsLog values (2,COMPANY B','Jim Beam','25/09/2015 00:00:00','GBP','200','01/09/2015 10:00:09')
insert into PaymentsLog values (2,COMPANY B','Jim Beam','25/09/2015 00:00:00','GBP','200','02/09/2015 10:00:09')
insert into PaymentsLog values (2,COMPANY B','Jim Beam','25/09/2015 00:00:00','GBP','200','03/09/2015 10:00:09')
insert into PaymentsLog values (3,COMPANY C','Spice Morgan','25/09/2015 00:00:00','GBP','50','03/09/2015 10:00:09')
insert into PaymentsLog values (3,COMPANY C','Spice Morgan','25/09/2015 00:00:00','GBP','50','04/09/2015 10:00:09')
insert into PaymentsLog values (4,COMPANY D','Cheap Booze','25/09/2015 00:00:00','GBP','10','01/09/2015 10:00:09')
insert into PaymentsLog values (5,COMPANY E','Expensive Booze','25/09/2015 00:00:00','GBP','300','01/09/2015 10:00:09')
insert into PaymentsLog values (5,COMPANY E','Expensive Booze','25/09/2015 00:00:00','GBP','300','02/09/2015 10:00:09')
insert into PaymentsLog values (5,COMPANY E','Expensive Booze','25/09/2015 00:00:00','GBP','300','03/09/2015 10:00:09')
insert into PaymentsLog values (5,COMPANY E','Expensive Booze','25/09/2015 00:00:00','GBP','300','04/09/2015 10:00:09')
insert into PaymentsLog values (5,COMPANY E','Expensive Booze','25/09/2015 00:00:00','GBP','300','05/09/2015 10:00:09')
insert into PaymentsLog values (5,COMPANY E','Expensive Booze','25/09/2015 00:00:00','GBP','300','06/09/2015 10:00:09')
insert into PaymentsLog values (5,COMPANY E','Expensive Booze','25/09/2015 00:00:00','GBP','300','07/09/2015 10:00:09')
insert into PaymentsLog values (5,COMPANY E','Expensive Booze','25/09/2015 00:00:00','GBP','300','08/09/2015 10:00:09')
insert into PaymentsLog values (5,COMPANY E','Expensive Booze','25/09/2015 00:00:00','GBP','300','09/09/2015 10:00:09')
insert into PaymentsLog values (5,COMPANY E','Expensive Booze','25/09/2015 00:00:00','GBP','300','10/09/2015 10:00:09')
insert into PaymentsLog values (5,COMPANY E','Expensive Booze','25/09/2015 00:00:00','GBP','300','11/09/2015 10:00:09')
insert into PaymentsLog values (6,COMPANY F','Budweiser','25/09/2015 00:00:00','GBP','45','03/09/2015 10:00:09')
insert into PaymentsLog values (6,COMPANY F','Budweiser','25/09/2015 00:00:00','GBP','20','04/09/2015 10:00:09')
insert into PaymentsLog values (6,COMPANY F','Budweiser','25/09/2015 00:00:00','GBP','20','05/09/2015 10:00:09')
I have then the below stored procedure which works fine
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE procedure [dbo].[spPayLogDetails]
(
@RecordType as varchar(15),
@CreditContr as varchar(20),
@DateFrom as Date,
@DateTo as Date
)
as
begin
Declare @StartOfSQLString as varchar(8000);
If (@RecordType = 'Area A')
begin
set @StartOfSQLString = '
SELECT
''Area A'' as RecordType
,ID
,DebtorName
,CreditController
,DueDate
,CurrencyCode
,Amount
FROM
PaymentsLog
WHERE
Amount >= 100
andCreditController = ' + ''''+ @CreditContr + ''''
End
exec (@StartOfSQLString)
--print @StartOfSQLString
End
GO
exec spPayLogDetails 'Area A','Jim Beam','2016-02-15','2016-02-15'
I am then trying to create the below stored procedure but it keeps on failing and not sure why?
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE procedure [dbo].[spPayLogDetails2]
(
@RecordType as varchar(15),
@CreditContr as varchar(20),
@DateFrom as Date,
@DateTo as Date
)
as
begin
Declare @StartOfSQLString as varchar(8000);
If (@RecordType = 'Area A')
begin
set @StartOfSQLString = '
SELECT
''Area A'' as RecordType
,ID
,DebtorName
,CreditController
,DueDate
,CurrencyCode
,Amount
FROM
PaymentsLog
WHERE
Amount >= 100
andCreditController = ' + ''''+ @CreditContr + ''''
End
Else If (@RecordType = 'Area B')
begin
set @StartOfSQLString = '
SELECT
''Area B'' as RecordType
,ID
,DebtorName
,CreditController
,DueDate
,CurrencyCode
,Amount
,InjectedDate
FROM
PaymentsLog
WHERE
Amount >= 100
andCreditController = ' + ''''+ @CreditContr + '''
and InjectedDate Between + '''@DateFrom +'''And''' +@DateTo ''
end
exec (@StartOfSQLString)
--print @StartOfSQLString
End
exec spPayLogDetails2 'Area A','Jim Beam','2016-02-15','2016-02-15'

Viewing 2 posts - 1 through 1 (of 1 total)

You must be logged in to reply to this topic. Login to reply

Orlando Colamatteo SSC Guru Points: 182279 More actions · Answer 1

At the bottom of your dynamic proc change you needed to change this:

and InjectedDate Between + '''@DateFrom +'''And''' +@DateTo ''

to this:

and InjectedDate Between ''' + convert(varchar(100),@DateFrom) +'''And''' + convert(varchar(100),@DateTo) + ''''

to fix the syntax error.

Regarding your use of dynamic SQL I would suggest you use sp_executesql instead of EXEC and also parameterize your SQL. You'll get better plan re-use and be better insulated from SQL injection.

Like this:

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

CREATE PROCEDURE dbo.spPayLogDetails2

(

@RecordType as varchar(15),

@CreditContr as varchar(20),

@DateFrom as Date,

@DateTo as Date

)

as

begin

Declare @SQLString as NVARCHAR(MAX);

DECLARE @ParmDefinition NVARCHAR(500);

set @SQLString = '

SELECT @param_RecordType as RecordType,

ID,

DebtorName,

CreditController,

DueDate,

CurrencyCode,

Amount

FROM dbo.PaymentsLog -- always specify schema when referencing objects

WHERE Amount >= 100

andCreditController = @param_CreditContr'

If (@RecordType = 'Area B')

begin

set @SQLString = @SQLString + '

and InjectedDate Between @param_DateFrom And @param_DateTo'

end

SET @ParmDefinition = N'@param_RecordType VARCHAR(15), @param_CreditContr VARCHAR(20), @param_DateFrom DATE, @param_DateTo DATE';

EXEC sys.sp_executesql

@SQLString,

@ParmDefinition,

@param_RecordType = @RecordType,

@param_CreditContr = @CreditContr,

@param_DateFrom = @DateFrom,

@param_DateTo = @DateTo;

End

go

exec dbo.spPayLogDetails2 'Area A','Jim Beam','2016-02-15','2016-02-15'

go

There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato