Return string from SQL

[JSMITH242B]

Hello,

I have the following SP which takes in a date input parameter and exports a string which is made up of a returndate, Action, Total and PermitTotal.
I am new to creating SP's.... so cribbed most of this from looking at samples.
Based on the date input parameter and the record values in the table, at least 1 row should be returned. I don't get anything.

What's wrong?

CREATE PROCEDURE pe_getParkMobileTotal
@datecheck datetime,
@pmString varchar(100) OUTPUT

with recompile
AS
declare @rowcount integer,
--@errorno integer,
@resultCode int,
@ReturnDate datetime,
@pmAction varchar,
@pmTotal integer,
@permitTotal integer


select * from parkMobileTotal
where CONVERT(VARCHAR(10), datesent ,103)=@datecheck
and ISNULL(parkmobiletotal,0) <> ISNULL(permittotal,0)


select @rowcount = @@Rowcount --, @Errorno = @@Error
if (@rowcount <> 0)
begin
SELECT @ReturnDate = DateSent from ParkMobileTotal where datesent = @datecheck
SELECT @pmAction = pm_function from ParkMobileTotal where datesent = @datecheck
SELECT @pmTotal = ParkMobileTotal from ParkMobileTotal where datesent = @datecheck
SELECT @permitTotal = ParkMobileTotal from ParkMobileTotal where datesent = @datecheck
set @pmString = @ReturnDate + ' ' + @pmAction + ' ' + @pmTotal + ' ' + @permitTotal

RETURN 0
end
GO

 

[manjarikosaraju]

Try this.. for the variable @pmAction you need to specify the length of the varchar datatype unless it is single letter. You need to cast datetime,integer data types to varchar when storing a result in a string data type.
Hope this helps.
------------------------------------------------
CREATE PROCEDURE pe_getParkMobileTotal
@datecheck datetime,
@pmString varchar(100) OUTPUT

with recompile
AS
declare @rowcount integer,
--@errorno integer,
@resultCode int,
@ReturnDate datetime,
@pmAction varchar(25),
@pmTotal integer,
@permitTotal integer


select * from parkMobileTotal
where CONVERT(VARCHAR(10), datesent ,103)=@datecheck
and ISNULL(parkmobiletotal,0) <> ISNULL(permittotal,0)


select @rowcount = @@Rowcount --, @Errorno = @@Error
if (@rowcount <> 0)
begin
SELECT @ReturnDate = DateSent from ParkMobileTotal where datesent = @datecheck
SELECT @pmAction = pm_function from ParkMobileTotal where datesent = @datecheck
SELECT @pmTotal = ParkMobileTotal from ParkMobileTotal where datesent = @datecheck
SELECT @permitTotal = ParkMobileTotal from ParkMobileTotal where datesent = @datecheck
set @pmString = convert(varchar(10),@ReturnDate,110) + ' ' + @pmAction + ' ' + cast(@pmTotal as varchar(10))+ ' ' + cast(@permitTotal as varchar(10))

RETURN 0
end
--------------------------------------------------

 

[JSMITH242B]

Thanks - I'll give that a go. With a SP like this, would I need error trapping?
You can see that I've commented out this.

Kind Regards