Friday, March 9, 2012

I am getting an error in creating a simple store procedure!

Hi ,
Here is my stored proce and I am not sure why its complaining about
the below error
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[USP_InsertUserInfo]
@.aspnet_userid uniqueidentifier(50)
,@.DealershipID int
,@.LastName varchar(50)
,@.FirstName varchar(50)
,@.AddressLine1 varchar(50)
,@.AddressLine2 varchar(50)
,@.City varchar(50)
,@.State varchar(50)
,@.Zip varchar(50)
,@.Country varchar(50)
,@.HomePhoneNumber varchar(50)
,@.MobileNumber varchar(50)
,@.OfficePhoneNumbre varchar(50)
,@.OfficePhoneExtension varchar(5)
,@.EmailAddress varchar(50)
,@.PreferredCommunication varchar(50)
,@.InsertedBy uniqueidentifier
,@.InsertedDate datetime
,@.ModifiedBy uniqueidentifier
,@.ModifiedDate datetime
AS
BEGIN
SET NOCOUNT ON;
INSERT INTO [GroundingDemo].[dbo].[Users]
(aspnet_userid
,DealershipID
,LastName
,FirstName
,AddressLine1
,AddressLine2
,City
,State
,Zip
,Country
,HomePhoneNumber
,MobileNumber
,OfficePhoneNumbre
,OfficePhoneExtension
,EmailAddress
,PreferredCommunication
,InsertedBy
,InsertedDate
,ModifiedBy
,ModifiedDate)
VALUES
(aspnet_userid
,DealershipID
,LastName
,FirstName
,AddressLine1
,AddressLine2
,City
,State
,Zip
,Country
,HomePhoneNumber
,MobileNumber
,OfficePhoneNumbre
,OfficePhoneExtension
,EmailAddress
,PreferredCommunication
,InsertedBy
,InsertedDate
,ModifiedBy
,ModifiedDate)
END
GO
*************************************her
e is the error
***********************************
Msg 128, Level 15, State 1, Procedure USP_InsertUserInfo, Line 63
The name "aspnet_userid" is not permitted in this context. Valid
expressions are constants, constant expressions, and (in some contexts)
variables. Column names are not permitted.
****************************************
***********************
if I take off the aspnet_userid column from the stored proce and also
the table and re execute it then complains about DealerShipID with the
same kind of error.
Am I missing some thing here?
Thanks
-Lyou are missing a comma after @.aspnet_userid uniqueidentifier(50)
and also
VALUES
(aspnet_userid
,DealershipID
has to be
VALUES
(@.aspnet_userid, --comma here also
,@.DealershipID etc etc etc
Denis the SQL Menace
http://sqlservercode.blogspot.com/|||Hi,
You are using the procedure's parameters in the values list, but forgot to
prefix them with the "@.". Like this:
VALUES
(@.aspnet_userid
,@.DealershipID
etc.
hth,
Dean
"Learner" <pradev@.gmail.com> wrote in message
news:1145648788.592029.198590@.g10g2000cwb.googlegroups.com...
> Hi ,
> Here is my stored proce and I am not sure why its complaining about
> the below error
>
> SET ANSI_NULLS ON
> GO
> SET QUOTED_IDENTIFIER ON
> GO
> CREATE PROCEDURE [dbo].[USP_InsertUserInfo]
> @.aspnet_userid uniqueidentifier(50)
> ,@.DealershipID int
> ,@.LastName varchar(50)
> ,@.FirstName varchar(50)
> ,@.AddressLine1 varchar(50)
> ,@.AddressLine2 varchar(50)
> ,@.City varchar(50)
> ,@.State varchar(50)
> ,@.Zip varchar(50)
> ,@.Country varchar(50)
> ,@.HomePhoneNumber varchar(50)
> ,@.MobileNumber varchar(50)
> ,@.OfficePhoneNumbre varchar(50)
> ,@.OfficePhoneExtension varchar(5)
> ,@.EmailAddress varchar(50)
> ,@.PreferredCommunication varchar(50)
> ,@.InsertedBy uniqueidentifier
> ,@.InsertedDate datetime
> ,@.ModifiedBy uniqueidentifier
> ,@.ModifiedDate datetime
> AS
> BEGIN
> SET NOCOUNT ON;
> INSERT INTO [GroundingDemo].[dbo].[Users]
> (aspnet_userid
> ,DealershipID
> ,LastName
> ,FirstName
> ,AddressLine1
> ,AddressLine2
> ,City
> ,State
> ,Zip
> ,Country
> ,HomePhoneNumber
> ,MobileNumber
> ,OfficePhoneNumbre
> ,OfficePhoneExtension
> ,EmailAddress
> ,PreferredCommunication
> ,InsertedBy
> ,InsertedDate
> ,ModifiedBy
> ,ModifiedDate)
> VALUES
> (aspnet_userid
> ,DealershipID
> ,LastName
> ,FirstName
> ,AddressLine1
> ,AddressLine2
> ,City
> ,State
> ,Zip
> ,Country
> ,HomePhoneNumber
> ,MobileNumber
> ,OfficePhoneNumbre
> ,OfficePhoneExtension
> ,EmailAddress
> ,PreferredCommunication
> ,InsertedBy
> ,InsertedDate
> ,ModifiedBy
> ,ModifiedDate)
> END
> GO
> *************************************her
e is the error
> ***********************************
> Msg 128, Level 15, State 1, Procedure USP_InsertUserInfo, Line 63
> The name "aspnet_userid" is not permitted in this context. Valid
> expressions are constants, constant expressions, and (in some contexts)
> variables. Column names are not permitted.
> ****************************************
***********************
> if I take off the aspnet_userid column from the stored proce and also
> the table and re execute it then complains about DealerShipID with the
> same kind of error.
>
> Am I missing some thing here?
> Thanks
> -L
>|||"SQL" <denis.gobo@.gmail.com> wrote in message
news:1145649123.181217.315180@.u72g2000cwu.googlegroups.com...
> you are missing a comma after @.aspnet_userid uniqueidentifier(50)
Actually, he's not :) The comma *is* there, but not where one would normally
expect it to be.
Dean

> and also
> VALUES
> (aspnet_userid
> ,DealershipID
> has to be
> VALUES
> (@.aspnet_userid, --comma here also
> ,@.DealershipID etc etc etc
>
> Denis the SQL Menace
> http://sqlservercode.blogspot.com/
>|||I am not sure if I am understanding it right... I have another stored
proc that works and it doesn't have commas after it . I mean I am
trying to do the same thing with my new one ...
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go
ALTER PROCEDURE [dbo].[SP_InsertNewVehicleFeed]
@.VIN char(17),
@.AccountNum varchar(14),
@.CustomerName varchar(50),
@.CoByerName varchar(50),
@.Owner varchar(15),
@.SubOwner varchar(15),
@.IssuingFSO varchar(10),
@.OwningFSO varchar(10),
@.DealerShipID int,
@.ReturnDate smallDateTime,
@.Odometer varchar(6),
@.Year int,
@.MakeID int,
@.ModelID int,
@.SubModelID int
AS
BEGIN
SET NOCOUNT ON;
INSERT INTO VehicleInformation
(VIN,
AccountNum,
CustomerName,
CoBuyerName,
Owner,
SubOwner,
IssuingFSO,
OwningFSO,
DealerShipID,
ReturnDate,
Odometer,
Year,
MakeID,
ModelID,
SubModelID)
VALUES
(@.VIN,
@.AccountNum,
@.CustomerName,
@.CoByerName,
@.Owner,
@.SubOwner,
@.IssuingFSO,
@.OwningFSO,
@.DealerShipID,
@.ReturnDate,
@.Odometer,
@.Year,
@.MakeID,
@.ModelID,
@.SubModelID)
END
IF (@.@.ERROR > 0)
Begin
Select 'Failure'
End
Else
Begin
Select 'Success'
End|||He Dean,
yes that was silly :) jezz I was so stupid abou it..
THANK YOU
-L|||yes thats right. I just put infront of the each parameter not after :)
I don't know when I started it but just doing it.

No comments:

Post a Comment