INSERT INTO - Data is not inserted

[ImraneA]

hi there

Created sproc - it stops dead in the first line

Why ????

Thanks in advance

CREATE PROCEDURE [dbo].[test] AS

insert into timesheet.dbo.t able1 (RE_Code, PR_Code, AC_Code, WE_Date,
SAT, SUN, MON, TUE, WED, THU, FRI, NOTES, GENERAL, PO_Number,
WWL_Number, CN_Number)
SELECT RE_Code, PR_Code, AC_Code, WE_Date, SAT, SUN, MON, TUE,
WED, THU, FRI, NOTES, GENERAL, PO_Number, WWL_Number, CN_Number
FROM dbo.WWL_TimeShe ets
WHERE (RE_Code = 'akram.i') AND (WE_Date = CONVERT(DATETIM E,
'1999-12-03 00:00:00', 102))
GO

[Erland Sommarskog]

Re: INSERT INTO - Data is not inserted

ImraneA (i.akram@weir.c o.uk) writes:[color=blue]
> Created sproc - it stops dead in the first line
>
> Why ????
>
> Thanks in advance
>
> CREATE PROCEDURE [dbo].[test] AS
>
> insert into timesheet.dbo.t able1 (RE_Code, PR_Code, AC_Code, WE_Date,
> SAT, SUN, MON, TUE, WED, THU, FRI, NOTES, GENERAL, PO_Number,
> WWL_Number, CN_Number)
> SELECT RE_Code, PR_Code, AC_Code, WE_Date, SAT, SUN, MON, TUE,
> WED, THU, FRI, NOTES, GENERAL, PO_Number, WWL_Number, CN_Number
> FROM dbo.WWL_TimeShe ets
> WHERE (RE_Code = 'akram.i') AND (WE_Date = CONVERT(DATETIM E,
> '1999-12-03 00:00:00', 102))
> GO[/color]


Didn't you post a question the other day, which I replied to? This
looks like a shortened version of the same procedure.

In any case, it is impossible to answer, unless your provide more
information. Do you get any error message? If you run the SELECT
without INSERT, do you get any data? Is there any trigger on the
table?

--
Erland Sommarskog, SQL Server MVP, sommar@algonet. se

Books Online for SQL Server SP3 at

http://www.microsoft.com/sql/techinfo/productdoc/2000/books.asp

[Imrane Akram]

Re: INSERT INTO - Data is not inserted

Hi there again

Been having a VERY VERY frustrating time with this sproc - not thinking
straight.

Question :-

Been using debugger throu Query Analyzer to check sproc.
Now I wish to call sproc throu Query Analyzer. Having a problem with
that. i.e How do I call it ??

recap :-
CREATE PROCEDURE procTimesheetIn sert(
@TimesheetDetai ls varchar(1000) = NULL,
@TimesheetID int = NULL OUTPUT,
@RetCode int = NULL OUTPUT,
@RetMsg varchar(100) = NULL OUTPUT)

----
EXEC procTimesheetIn sert @TimesheetDetai ls ="data....." ,
@TimesheetID = NULL,
@RetCode = NULL,
@RetMsg = NULL)

Do I need to declare anything here ???

N.B.
1.Test "Table" used - no triggers or relationships etc..






*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

[Matt Rellick]

Re: INSERT INTO - Data is not inserted

Three things I noticed in the SP call (it may have just been typos from
your posting):

1) Use should be using single quotes (') not double quotes (") to
deliminate a string

2) You have an extra end parent at the end of your SP call

3) You have three of your SP parameters declared as OUTPUT, if you
expect to actually get this data back from the SP you need to assign the
Parameter to a Variable within the SP call, and then identify the
parameter as OUTPUT in the SP call. Such as

DECLARE @TS_OUT INT,
@RetCodeOut INT,
@RetMsgOut VARCHAR(100)

EXEC procTimesheetIn sert @TimesheetDetai ls ='data.....',
@TimesheetID = @TS_OUT OUTPUT,
@RetCode = @RetCodeOut OUTPUT,
@RetMsg = @RetMsgOut OUTPUT


Although not doing #3 will not cause an error, just defeats the purpose
of the OUTPUT parameters.

HTH,
Matt


*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

[Imrane Akram]

Re: INSERT INTO - Data is not inserted


I give it try.

Thanks.


*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

[Erland Sommarskog]

Re: INSERT INTO - Data is not inserted

Matt Rellick (tics@yahoo.com ) writes:[color=blue]
> 1) Use should be using single quotes (') not double quotes (") to
> deliminate a string[/color]

However, Imrane's string includes a whole bunch of single quotes
itself. While you always can include single quotes in a literal by
doubling them:

DECLARE @str varchar(10)
SELECT @str = 'It isn''t'
SELECT @str

I would recommend Imrane to make use of the setting QUOTED IDENTIFIER:

SET QUOTED_IDENTIFI ER OFF
go
EXEC the_sp @testdata="'HAR LEY.I',03004,'A 000-AA00'..."

In fact, when I tested his procedure, this was precisly what I did.

--
Erland Sommarskog, SQL Server MVP, sommar@algonet. se

Books Online for SQL Server SP3 at

http://www.microsoft.com/sql/techinfo/productdoc/2000/books.asp

[Imrane Akram]

Re: INSERT INTO - Data is not inserted


Thanks for comments - did the job.


*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!