Stored Procedure

[sukriti1116]

hiii, i am executing the following stored procedure but having errors...

create procedure check_returns @salesman varchar(10),@RCV10 int,@RCV09 int,@RCV15 int,@GPRS15 int,@RCV20 int,@RCV25FTT int,@RCV25 int,@RCV31 int,@RCV30 int,@RCV35 int,@RCV50 int,@RCV55 int,@SIM int,@VTOPSIM int,@VTOPBAL int,@THREEGSIM int, @ENTRYDATE datetime
AS
BEGIN
if exists(select * from CardsIssued where salesman=@salesman and RCV10>@RCV10 AND RCV09>@RCV09 AND RCV15>@RCV15 AND GPRS15>@GPRS15 AND RCV20>@RCV20 AND RCV25FTT>@RCV25FTT AND RCV25>@RCV25 AND RCV31>@RCV31 AND RCV30>@RCV30 AND RCV35>@RCV35 AND RCV50>@RCV50 AND RCV55>@RCV55 AND SIM>@SIM AND VtopSim>@VTOPSIM AND VtopBal>@VTOPBAL AND ThreeGSim>@THREEGSIM and EntryDate=@ENTRYDATE )
BEGIN
insert Cards_Sold values(@salesman,@RCV10,@RCV09,@RCV15,@GPRS15,@RCV20,@RCV25FTT,@RCV25,@RCV31,@RCV30,@RCV35,@RCV50,@RCV55,@SIM,@VTOPSIM,@VTOPBAL,@THREEGSIM,@EntryDate)
END
ELSE
PRINT'CARDS RETURNED CANNOT BE GREATER THAN CARDS ISSUED'
END

the error coming is....
Msg 8114, Level 16, State 5, Procedure check_returns, Line 0
Error converting data type varchar to datetime.

plz correct the error...

[LastMitch]

@sukriti1116

plz correct the error...

In the future when you post your code click "Code" so it will highlight it. It's a bit hard to read it.

Error converting data type varchar to datetime.

It means that your varchar strings can't convert to datetime.

You have to check if your datatime looks like this format:

('yyyy-mm-dd hh:mm:ss' as datetime)

Try to used this:

convert(datetime,@Create,100)

I'm not sure if it's gonna work or not because I can't read your code

[sukriti1116]

hiii, i am executing the following stored procedure but having errors...

create procedure check_returns @salesman varchar(10),@RCV10 int,@RCV09 int,@RCV15 int,@GPRS15 int,@RCV20 int,@RCV25FTT int,@RCV25 int,@RCV31 int,@RCV30 int,@RCV35 int,@RCV50 int,@RCV55 int,@SIM int,@VTOPSIM int,@VTOPBAL int,@THREEGSIM int, @ENTRYDATE datetime
AS
BEGIN
if exists(select * from CardsIssued where salesman=@salesman and RCV10>@RCV10 AND RCV09>@RCV09 AND RCV15>@RCV15 AND GPRS15>@GPRS15 AND RCV20>@RCV20 AND RCV25FTT>@RCV25FTT AND RCV25>@RCV25 AND RCV31>@RCV31 AND RCV30>@RCV30 AND RCV35>@RCV35 AND RCV50>@RCV50 AND RCV55>@RCV55 AND SIM>@SIM AND VtopSim>@VTOPSIM AND VtopBal>@VTOPBAL AND ThreeGSim>@THREEGSIM and EntryDate=@ENTRYDATE )
BEGIN
insert Cards_Sold values(@salesman,@RCV10,@RCV09,@RCV15,@GPRS15,@RCV20,@RCV25FTT,@RCV25,@RCV31,@RCV30,@RCV35,@RCV50,@RCV55,@SIM,@VTOPSIM,@VTOPBAL,@THREEGSIM,@EntryDate)
END
ELSE
PRINT'CARDS RETURNED CANNOT BE GREATER THAN CARDS ISSUED'
END

the error coming is....
Msg 8114, Level 16, State 5, Procedure check_returns, Line 0
Error converting data type varchar to datetime.

plz correct the error at the place where it need to be corrected.as i am new to it..
plz let me know how to execute it in ado.net

[adam_k]

Does the error appear when executing the stored procedure or when creating it?

Also try LastMitch's advise to use select convert(datetime, @ENTRYDATE,100) instead of the exec check_returns.... and let us know if the conversion succeeded.

[sukriti1116]

no there is no error at the time of creating..
plz let me know where to add this in my statement

[adam_k]

when you are executing the stored procedure, you declare the variables or directly assign values to input of the sproc. Either way copy the date that you are assigning to @ENTRYDATE and replace with it the @ENTRYDATE in the select convert(datetime,@ENTRYDATE,100). You have to also enclose it to single quotes (').
Execute that statement and see if it returns errors.

If you are only executing your stored procedure from inside an app, try to execute it in the server with what would be normal values for your parameters.

[sukriti1116]

hii adam k ...
can u plz edit my code and send me with an execute statement... so that i may actually understand what wrong i am doing

[sukriti1116]

hi adam k i tried what u asked but still getting the conversion error..
can u plz edit my code n also provide me an execute statement so that i may get rid of this problem

[adam_k]

If you are getting the conversion error with the select convert(datetime, '2013-02-01',100) statement (or whatever format you are passing date in) , then SQL server can't determine the format of your date and you need to either tell it the format or change the format you are using.

A good way to see what format it is expecting would be to select an existing one or select getdate().

If you can't change the date format you are passing your procedure, then use DATEFORMAT to tell the server what format you are using for your date. Read here for SET DATEFORMAT : http://msdn.microsoft.com/en-us/library/ms189491.aspx

[LastMitch]

@sukriti1116

can u plz edit my code n also provide me an execute statement so that i may get rid of this problem

adam_k provided you a nice link plus he also explained the issue.