Another cast problem
-
Hi
Sorry i'm novice in SQL Server and when i try :
ALTER PROCEDURE MYPROC(@idSite int, @Type varchar(4)) AS
DECLARE @strReq varchar(1000)
SET @strReq = 'SELECT DLZ_EMAIL.EMAIL_LIB, COUNT(*) AS nb_email
FROM DLZ_EMAIL WHERE DLZ_EMAIL.DATE_DEB <=' + cast(GETDATE() as varchar)
If @Type='HTML'
begin
SET @strReq = @strReq + ' AND (DLZ_EMAIL.EMAIL_HTML <> 1 OR DLZ_EMAIL.EMAIL_HTML = null) '
end
else
begin
SET @strReq = @strReq + ' AND (DLZ_EMAIL.EMAIL_HTML = 1) '
end
SET @strReq= @strReq + 'GROUP BY DLZ_EMAIL.SITE_ID'
Exec(@strReq)
But i got message : "Syntax error converting datetime from character string".Why ???
NB: DATE_DEB is a datetime column.
And the second question is : How can i return the column nb_email in a OUTPUT parameter ?
Thx a lot
-
WHERE DLZ_EMAIL.DATE_DEB <=' + cast(GETDATE() as varchar)
That is comparing a DATETIME value (date_deb) with a VARCHAR.
Why are you casting GETDATE() to a VARCHAR and then comparing it to a DATETIME value?
-SQLBill
-
SQLBill:
'cos when i just do :
SET @strReq = 'SELECT DLZ_EMAIL.EMAIL_LIB, COUNT(*) AS nb_email
FROM DLZ_EMAIL WHERE DLZ_EMAIL.DATE_DEB <=' + GETDATE()
it tellls me the same message : "Syntax error converting datetime from character string"
My date format is dmy (a french format : dd/mm/yyyy) in my column DATE_DEB.
And if i do :
SET @strReq = 'SELECT DLZ_EMAIL.EMAIL_LIB, COUNT(*) AS nb_email
FROM DLZ_EMAIL WHERE DLZ_EMAIL.DATE_DEB <=' + convert(varchar,getdate(),103)
my result is not the same if i just do a simple select like this :
SELECT DLZ_EMAIL.EMAIL_LIB, COUNT(*) AS nb_email
FROM DLZ_EMAIL WHERE DLZ_EMAIL.DATE_DEB <= GetDate()
I don't understand why my result is not the same when i use a SET @strReq = ...
Have you got an idee???
Thx
-
Why aren't you just doing this:
WHERE DLZ_EMAIL.DATE_DEB <= GETDATE()'
-SQLBill
-
Oh YES !!! It works !
I believed that a CAST had to be made.
Thx a lot SQLBill ... I'm so stupid
))))) -
Perico,
I don't know what this part of the select is supposed to do:
, @nb-2 = COUNT(*)
but it certainly isn't going to get you an output parameter. You can't set a variable to = more than one result, so if the rest of the where clause will return more than dlz_email.email_lib then you need to restructure the entire query, and possibly the entire process.
Look in BOL for output parameters
You will, at a minimum (if there is only one email_lib returned) have to add a Group by dlz_email.email_lib to the query (anytime there is a select "field", count(*) you must group by all fields selected ).
It looks to me you like you need to create a temp table (email_lib, totalcount) and then insert into it using your select state. Your output would then be the rows in the temp table - like I said above if the intent was to return one count and one email_lib then either you know your dataset and believe this is going to happen, or you'll need to rething the process (or use a cursor and get one row at a time).
Tell us more and maybe we can help
Thanks, and don't forget to Chuckle -
Well my original SP is :
ALTER PROCEDURE MYPROC(@idSite int, @Type varchar(4)) AS
DECLARE @strReq varchar(1000)
SET @strReq = 'SELECT DLZ_EMAIL.EMAIL_LIB, COUNT(*) AS nb_email
FROM DLZ_EMAIL WHERE DLZ_EMAIL.DATE_DEB <=GETDATE() '
If @Type='HTML'
begin
SET @strReq = @strReq + ' AND (DLZ_EMAIL.EMAIL_HTML <> 1 OR DLZ_EMAIL.EMAIL_HTML = null) '
end
else
begin
SET @strReq = @strReq + ' AND (DLZ_EMAIL.EMAIL_HTML = 1) '
end
SET @strReq= @strReq + 'GROUP BY DLZ_EMAIL.SITE_ID'
Exec(@strReq)
It works fine. But now i want to add an OUTPUT parameter which retrieves me the field "nb_email" while carrying out my request.How can i do this ?
Thx again
-
this will return only the nb_email as the result set (I can't test) :
ALTER PROCEDURE MYPROC(@idSite int, @Type varchar(4)) AS
DECLARE @strReq varchar(1000)
SET @strReq = 'SELECT COUNT(*) AS nb_email
FROM DLZ_EMAIL WHERE DLZ_EMAIL.DATE_DEB <=GETDATE() '
If @Type='HTML'
begin
SET @strReq = @strReq + ' AND (DLZ_EMAIL.EMAIL_HTML <> 1 OR DLZ_EMAIL.EMAIL_HTML = null) '
end
else
begin
SET @strReq = @strReq + ' AND (DLZ_EMAIL.EMAIL_HTML = 1) '
end
SET @strReq= @strReq + 'GROUP BY DLZ_EMAIL.SITE_ID'
return Exec(@strReq)
Thanks, and don't forget to Chuckle
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply