sp execution

Question

sp execution

Frances L

SSCarpal Tunnel

Points: 4727
More actions
September 20, 2007 at 7:57 am

#177184

table definiation;
USE
[Rainbow]
GO
/****** Object: Table [UserDataEntry].[GeneralInfo] Script Date: 09/20/2007 09:55:44 ******/
SET
ANSI_NULLS ON
GO
SET
QUOTED_IDENTIFIER ON
GO
SET
ANSI_PADDING ON
GO
CREATE
TABLE [UserDataEntry].[GeneralInfo](
[Date] [smalldatetime]
NOT NULL,
[Source] [char]
(3) NOT NULL,
[ALName] [nvarchar]
(25) NOT NULL,
[AFName] [nvarchar]
(25) NOT NULL,
[AAddress] [nvarchar]
(35) NULL,
[ACity] [nvarchar]
(20) NULL,
[AState] [char]
(2) NULL,
[AZip] [char]
(5) NOT NULL
)
ON [PRIMARY]
GO
SET
ANSI_PADDING OFF
sp:
SET ANSI_NULLS ON
GO
SET
QUOTED_IDENTIFIER ON
GO
ALTER
proc [UserDataEntry].[InsertNewApplication]
@Date smalldatetime,
@Source
char(3),
@ALName
nvarchar(25),
@AFName
nvarchar(25),
@AAddress
nvarchar(35) ,
@ACity
nvarchar(20),
@AState
char(2),
@AZip
char(5)
as
INSERT
INTO UserDataEntry.GeneralInfo
( Date,Source, ALName, AFName, AAddress, ACity, AState, AZip)
values
(@Date ,
@Source
,
@ALName
,
@AFName
,
@AAddress
,
@ACity
,
@AState
,
@AZip
)

USE [Rainbow]
GO
DECLARE
@return_value int
EXEC
@return_value = [UserDataEntry].[InsertNewApplication]
@Date
= '09-01-2005',
@Source
= N'1',
@ALName
= N'1',
@AFName
= N'1',
@AAddress
= N'1',
@ACity
= N'1',
@AState
= N'1',
@AZip
= N'1'
SELECT
'Return Value' = @return_value
GO

I run the sp a couple time. it kept gave me one row affected. but I could not find any records in the table.

If I run this
USE
[Rainbow]
GO
DECLARE
@return_value int
EXEC
@return_value = [UserDataEntry].[InsertNewApplication]
@Date
= CAST('12-1-2005' AS DATETIME) ,
@Source
= N'1',
@ALName
= N'1',
@AFName
= N'1',
@AAddress
= N'1',
@ACity
= N'1',
@AState
= N'1',
@AZip
= N'1'
SELECT
'Return Value' = @return_value
GO
error log in
Msg 102, Level 15, State 1, Line 5
Incorrect syntax near '12-1-2005'.

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply

MarkusB SSC-Dedicated Points: 37370 More actions · Answer 1

Frances,

I don't see any problem. When I try your original procedure the records are all inserted into the table. Tried it 6 times, got 6 records in the table.

About the CAST, I think the problem is that you cannot use a function when passing parameters to a stored procedure.

Markus

[font="Verdana"]Markus Bohse[/font]

Frances L SSCarpal Tunnel Points: 4727 More actions · Answer 2

It is odd while I called that from asp.net . it also told me sp does not found. It used to give me error. can't convert the string to integer. I just elimited the fields so I can the problem. so..