May 10, 2008 at 1:05 am
Hi all,
I get the above error in the following SP in which I am trying to do two things.
1) - Insert a parent record.
2) - Insert 1 or many records in the child table using the parent ID
The child records are being passed as a XML param.
The error I am getting is
'Subqueries are not allowed in this context. Only scalar expressions are allowed.'
when I try to create the procedure.
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:pryder
-- Create date:
-- Description:
-- =============================================
CREATE PROCEDURE TestProc
-- Add the parameters for the stored procedure here
@Name String ,
@bings XML
AS
BEGIN transaction
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
declare @NewID as int
declare @err as int
-- Insert statements for procedure here
INSERT INTO PARENT
(Name)
VALUES (@Name)
SELECT @err = @@error
if @err <> 0
begin
rollback transaction
return @err
end
SELECT @NewID = SCOPE_IDENTITY
INSERT INTO Child
(ParentID,
name)
Values
((
SELECT @NewID, ParamValues.ID.value('.','VARCHAR(MAX)')
FROM @bings.nodes('bings/group') as ParamValues(ID)
))
SELECT @err = @@error
if @err <> 0 begin rollback transaction return @err end
commit transaction
return @@error
GO
May 10, 2008 at 5:53 am
INSERT INTO Child (ParentID,name)
SELECT @NewID, ParamValues.ID.value('.','VARCHAR(MAX)')
FROM @bings.nodes('bings/group') as ParamValues(ID)
should fix this
May 10, 2008 at 7:33 am
Hmm, I now get another error.
Msg 170, Level 15, State 1, Procedure TestProc, Line 34
Line 34: Incorrect syntax near '.'.
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:pryder
-- Create date:
-- Description:
-- =============================================
CREATE PROCEDURE TestProc
-- Add the parameters for the stored procedure here
@Name String ,
@bings XML
AS
BEGIN transaction
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
declare @NewID as int
declare @err as int
-- Insert statements for procedure here
INSERT INTO PARENT
(Name)
VALUES (@Name)
SELECT @err = @@error
if @err <> 0
begin
rollback transaction
return @err
end
SELECT @NewID = SCOPE_IDENTITY
INSERT INTO Child (ParentID,name)
SELECT @NewID, ParamValues.ID.value('.','VARCHAR(20)')
FROM @bings.nodes('bings/group') as ParamValues(ID)
SELECT @err = @@error
if @err <> 0 begin rollback transaction return @err end
commit transaction
return @@error
GO
My Test tables are as follows.
USE [test]
GO
/****** Object: Table [dbo].[Parent] Script Date: 05/10/2008 14:33:24 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[Parent](
[ID] [int] IDENTITY(1,1) NOT NULL,
[Name] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
CONSTRAINT [PK_Parent] PRIMARY KEY CLUSTERED
(
[ID] ASC
) ON [PRIMARY]
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
USE [test]
GO
/****** Object: Table [dbo].[Child] Script Date: 05/10/2008 14:34:01 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[Child](
[ID] [int] IDENTITY(1,1) NOT NULL,
[ParentID] [int] NOT NULL,
[Name] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
CONSTRAINT [PK_Child] PRIMARY KEY CLUSTERED
(
[ID] ASC
) ON [PRIMARY]
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
ALTER TABLE [dbo].[Child] WITH CHECK ADD CONSTRAINT [FK_Child_Parent] FOREIGN KEY([ParentID])
REFERENCES [dbo].[Parent] ([ID])
GO
ALTER TABLE [dbo].[Child] CHECK CONSTRAINT [FK_Child_Parent]
May 10, 2008 at 8:03 am
try it without paramvalues.
INSERT INTO Child (ParentID,name)
SELECT @NewID, ID.value('.','VARCHAR(20)')
FROM @bings.nodes('bings/group') as ParamValues(ID)
May 10, 2008 at 8:10 am
Nope still the same error.
Thanks for the replies though, this is all new to me. Simple SP's are not a problem, but I wanted something more elegant then the two SP's I've got going at the moment.
Cheers,
Paul.
May 10, 2008 at 8:17 am
sorry it doesnt 🙁 still in the data center but will drive back to work soon to see it on sql server
May 10, 2008 at 8:25 am
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author: pryder
-- Create date:
-- Description:
-- =============================================
CREATE PROCEDURE TestProc
-- Add the parameters for the stored procedure here
@Name varchar(max),
@bings XML
AS
BEGIN transaction
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
declare @NewID as int
declare @err as int
-- Insert statements for procedure here
INSERT INTO PARENT
(Name)
VALUES (@Name)
SELECT @err = @@error
if @err <> 0
begin
rollback transaction
return @err
end
SELECT @NewID = SCOPE_IDENTITY()
INSERT INTO Child (ParentID,name)
SELECT @NewID, ParamValues.ID.value('.','VARCHAR(20)')
FROM @bings.nodes('bings/group') as ParamValues(ID)
SELECT @err = @@error
if @err <> 0 begin rollback transaction return @err end
commit transaction
return @@error
GO
do you get the error on executing the procedure or on creating it?
May 10, 2008 at 11:45 pm
Hi all,
Right it is now working..
Found out that it was a numpty play on my part, although I've the 2005 management tools, I had SQLExpress 2000 running in the back ground.
One quick install later and it's working, thanks for your help though.
Paul.
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply