Stored Procedure Issue

[obrienkev]

Hi all,

Why does the stored procedure return no results when it should?

Here's all the code...

ALTER PROCEDURE mySearch	
	-- Add the parameters for the stored procedure here
	@myDate Datetime = NULL,
	@myID varchar(250) = NULL,
	@myName varchar(250) = NULL

AS
BEGIN
	-- SET NOCOUNT ON added to prevent mytra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;

DECLARE @thisQuery as varchar(250)
DECLARE @strAppend as varchar(150)

SET @thisQuery = 'SELECT myID, myDate FROM myTbl '
-- Insert statements for procedure here
BEGIN
SET @thisQuery = @thisQuery + 'WHERE newID IS NOT NULL '
END
BEGIN
IF @myDate IS NOT NULL
SET @thisQuery = @thisQuery + 'AND myDate =' + @myDate
END
BEGIN
IF @myID IS NOT NULL
SET @thisQuery = @thisQuery + 'AND myID =' + '@myID'
END
BEGIN
IF @myName IS NOT NULL
SET @thisQuery = @thisQuery + 'AND myName =' + @myName
END
BEGIN
SET @thisQuery = @thisQuery + ';'
END
END

[Sxooter]

I don't know mysql stored procedures but it looks to me like you're not actually running the query you're building.

[obrienkev]

sorry its SQL Server stored procedure.

added this line...
EXEC @thisQuery

and returns the below error...

Could not find stored procedure 'SELECT myID, myDate FROM myTbl WHERE newID IS NOT NULL ;'.

[Lars_Berg]

The syntax for executing a string is

EXEC (@thisQuery)

[obrienkev]

Thanks!!

Now if I set myDate = '01/01/2007' I get the below error...

Syntax error converting datetime from character string.

any ideas? set up correctly in database table as datetime.

[Lars_Berg]

It may depend on how you configured SQL server to interpret dates.

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_set-set_40c4.asp

Look at set date_format

[Sxooter]

You should be able to use ISO dates for a match (except for oracle 9... urg):

'2007-05-24 14:34:26.23423'

But no guarantees