Problem with replacing dbname with variable
-
I need to create a stored procedure which holds 3 different insert statements - one of the inserts occurs on a table in a database who's name needs to be a variable, because its value is determined from a field in a table on a different database.
First I just tried replacing the dbname with a variable but thats not working. This one generates an 'invalid object @DBString.dbo.People' error message:
SELECT @PeopleID = (SELECT max(People_ID) FROM [@DBString].[dbo].[People] HOLDLOCK) + 1
SET IDENTITY_INSERT [@DBString].[dbo].[People] OFF
SET IDENTITY_INSERT [@DBString].[dbo].[People] ON
INSERT INTO [@DBString].[dbo].[People](People_ID, First_Name, Last_Name, Email_Address, DateEntered)VALUES(@PeopleID,@FName,@LName,@Email,GETDATE())
SET IDENTITY_INSERT [@DBString].[dbo].[People] OFF
IF @@error <> 0
BEGIN
ROLLBACK TRAN
RETURN
END
I then tried to create an @statement and then execute with sp_executesql however its not working and Im not comfortable with this stuff enough to determine whether what Im trying to do is illegal or if its just the syntax Im not getting properly.
DECLARE @SQLString NVARCHAR(500)
SET @SQLString=N'SELECT @PeopleID = (SELECT max(People_ID) FROM [' + @DBString +N'].[dbo].[People] HOLDLOCK) + 1
SET IDENTITY_INSERT [' + @DBString + N'].[dbo].[People] OFF
SET IDENTITY_INSERT [' + @DBString + N'].[dbo].[People] ON
INSERT INTO [' + @DBString + N'].[dbo].[People](People_ID, First_Name, Last_Name, Email_Address, DateEntered)VALUES(@PeopleID,@FName,@LName,@Email,GETDATE())
SET IDENTITY_INSERT [' + @DBString + N'].[dbo].[People] OFF
IF @@error <> 0
BEGIN
ROLLBACK TRAN
RETURN
END'
EXECUTE @SQLString
Any help is greatly appreciated
-
What you are proposing should work. Are you getting an error message back from your second example?
-
Yes - today coldfusion is spitting this back at me (yesterday it was different although that could have been typos for all I know):
[Macromedia][SQLServer JDBC Driver][SQLServer]The name 'SELECT @PeopleID = (SELECT max(People_ID) FROM [wednesdaytest5].[dbo].[People] HOLDLOCK) + 1 SET IDENTITY_INSERT [wednesdaytest5].[dbo].[People] OFF SET IDENTITY_INSERT [wednesdaytest5].[dbo].[People] ON INSERT INTO [wednesdaytest5].[dbo].[People](People_ID, First_Name, Last_Name, Email_Address, DateEntered)VALUES(@PeopleID,@FName,@LName,@Email,GETDATE()) SET IDENTITY_INSERT [wed...
Unfortunately it cuts off and thats all the relevant information. If I run this in qry analyzer its fine, and checking for syntax in enterprise manager doesnt return any errors either.
-
I thought you were running this through a stored procedure?? If so, run your stored procedure outside of ColdFusion so that you can debug your code. Once your code works, then bring ColdFusion into the mix.
-
It is a stored procedure. CF has a tag that passes variables to the sp in sqlserver.
I just dont get it, as it runs fine in query analyzer but bails when I execute it in the application.
Ah well such is life. Thanks for replying.
-
DECLARE @SQLString NVARCHAR(500) is this sufficient. Try increase the size and why are you using nvarchar
Kindest Regards,Amit Lohia
-
WHERE IS THIS @PeopleId Coming from , are u passing it as an argument to the procedure or declared inside the
procedure . If it is declare inside the procedure then change to this way
DECLARE @SQLString NVARCHAR(500)
SET @SQLString=N'DECLARE @PeopleId numeric SELECT @PeopleID = (SELECT max(People_ID) FROM [' + @DBString +N'].[dbo].[People] HOLDLOCK) + 1
SET IDENTITY_INSERT [' + @DBString + N'].[dbo].[People] OFF
SET IDENTITY_INSERT [' + @DBString + N'].[dbo].[People] ON
INSERT INTO [' + @DBString + N'].[dbo].[People](People_ID, First_Name, Last_Name, Email_Address, DateEntered)VALUES(@PeopleID,@FName,@LName,@Email,GETDATE())
SET IDENTITY_INSERT [' + @DBString + N'].[dbo].[People] OFF
IF @@error <> 0
BEGIN
ROLLBACK TRAN
RETURN
END'
EXECUTE @SQLString
-
@PeopleID has already been declared.
So the update to this is that I didnt have the 'sp_executesql in the EXECUTE statement - having added that, now its choking on the @FName variables asking them to be declared. However - that and the other variables listed in the VALUES part of the insert statement are arguments passed to the procedure and already declared. Do I have to redeclare them in the string??
-
Problem solved. The incoming parameters need to be stated in the executesql :
CREATE PROCEDURE newPrimaryMainContact
@FName varchar(255),
@LName varchar(255),
@Email varchar(255)
AS
DECLARE @PeopleID int
DECLARE @SQLString NVARCHAR(1000)
DECLARE @params nvarchar(500)
SET @params = N'@FName1 varchar(255),@LName2 varchar(255),@Email2 varchar(255)'
SET @SQLString=N'INSERT INTO [' + @referrer + N'].[dbo].[People](First_Name, Last_Name, Email_Address, DateEntered)VALUES(@FName1,@LName2,@Email2, GETDATE())'
EXECUTE sp_executesql @SQLString,@Params, @FName,@LName,@Email
GO
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply