Execute Statement 2

[CP60]

Is it alright to write the following SP this way (aside from sql injections)?

The code works right.

What I have is a function which receives two Date parameters and gets records from a table based on the Date criteria.

Then this SP uses uses also a parameter and runs a Select statement off of the Function, passing additional criteria is desired.

Also, how to use the Date Parameters in function in the Select statement properly (doesn't look right to me).

set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
GO

ALTER PROCEDURE [dbo].[TestfBP]

	@DateStart DateTime,
	@DateEnd DateTime,
	[b]@OtherCriteria VarChar(1000)[/b]
AS
BEGIN

	SET NOCOUNT ON;

	EXECUTE ('SELECT * FROM fMyFunction('''+@DateStart+''','''+@DateEnd+''') '+[b]@OtherCriteria[/b])
END

GO
EXEC [dbo].[TestfBP] '23-01-2008','23-01-2008',[b]'WHERE SomeField=''64279'''[/b]
GO

 

[bborissov]

If it works - it is ok
(aside from SQL Injection)
Just add a default value for the last parameter, what will happens if you didn't pass it

Borislav Borissov
VFP9 SP2, SQL Server 2000/2005.

 

[CP60]

Thank you!

>Just add a default value for the last parameter, what will happens if you didn't pass it

So, I just added a default value like:

@OtherCriteria VarChar(1000) =" "

and then there is no "Where" clause to be used.

 

[bborissov]

not with double quotes
(I wonder how this didn't BOMB)

@OtherCriteria VarChar(1000) =''
is enough and yes no WHERE clause will be there.

Borislav Borissov
VFP9 SP2, SQL Server 2000/2005.

 

[CP60]

No, it didn't bomb out with the double quotes

 

[Geoka]

It is because you have used set QUOTED_IDENTIFIER ON statement in your code.

 

[Geoka]

More over, need to qualify ownername before the function. for example - dbo.fMyFunction