Problem returning values in a select

[mbcmike]

I have a procedure that is being called by a crystal report. The report sends a value to the procedure which is either "NEW" or a date. If the value is new, then I want all new records returned and also to update them as "Printed". Other wise show records that have the date selected.

If a date is selected, it returns the records with no problem. If "NEW" is selected, it updates the records, but does not return me the values. Essentially, the select statement for "NEW" in the following procedure is not working:



CREATE PROCEDURE dbo.PORTAL_upd_sel_PIN_REQUEST
@p_type char(10)

AS

declare @time datetime

select @time = getdate()


if @p_type = 'NEW'
begin
update VCSFC..PORTAL_PIN_REQUEST
set request_status = 'Printed',
date_printed = @time
where ltrim(rtrim(request_status)) = 'New'

select * from VCSFC..PORTAL_PIN_REQUEST
where ltrim(rtrim(date_printed)) = @time
end


else
select * from VCSFC..PORTAL_PIN_REQUEST
where replace(CONVERT(CHAR(10),date_printed,110),'-','/') = ltrim(rtrim(@p_type))
go

 

[SQLDenis]

for starters try changing
where ltrim(rtrim(date_printed)) = @time
to
where date_printed = @time

Denis The SQL Menace
SQL blog:

http://sqlservercode.blogspot.com/

Personal Blog:

http://otherthingsnow.blogspot.com/

 

[SQLDenis]

also change this to this

else
select * from VCSFC..PORTAL_PIN_REQUEST
where replace(CONVERT(CHAR(10),date_printed,110),'-','/') = ltrim(rtrim(@p_type))

to

else
select * from VCSFC..PORTAL_PIN_REQUEST
where date_printed >=@p_type
and date_printed < dateadd(d,1,@p_type)

to test use this

declare @d1 char(10)
select @d1 ='05/01/2006'

select @d1 as Today,dateadd(d,1,@d1) as Tomorrow


This will avoid a tables scan



Denis The SQL Menace
SQL blog:

http://sqlservercode.blogspot.com/

Personal Blog:

http://otherthingsnow.blogspot.com/