Dynamic SQL statement in Stored Procedure fails

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Dynamic SQL statement in Stored Procedure fails

Author

Topic

steelers75
Starting Member

2 Posts

Posted - 2008-08-12 : 17:49:27

While I'm a SQL veteran (in Informix-land), I'm a newbie to SQLserver and I'm having trouble with the following statement in my stored proc:

SET @SQLString = 'SET IDENTITY_INSERT ' + QUOTENAME(@dest_db) + '.dbo.RouteSteps ON; '
SET @SQLstring = @SQLString + 'INSERT INTO ' + QUOTENAME(@dest_db) + '.dbo.RouteSteps '
SET @SQLstring = @SQLString +
'(RouteStepID,RouteID,StepNumber,UserID,StepName,DueDays,StepPercent,
NumProcessed,NextRouteID,UserList,StepType,FieldNumber,FieldValue,UpdateFieldNumber,
UpdateFieldValue,RouteDirection,SetNextDueDate,DueHours,StatusFlag,RouteWait,DueField,
NotifyEmail,RejectToStep,PromptComplete,PromptReject,PromptUser,ShowButtonComplete,
ShowButtonReject,VotePercent,UpdateFieldValueOld,Script,Authenticate,Description,
ShowButtonReassign) '
SET @SQLstring = @SQLString +
'SELECT RouteStepID,RouteSteps.RouteID,StepNumber,UserID,StepName,DueDays,StepPercent,
NumProcessed,NextRouteID,UserList,StepType,FieldNumber,FieldValue,UpdateFieldNumber,
UpdateFieldValue,RouteDirection,SetNextDueDate,DueHours,StatusFlag,RouteWait,DueField,
NotifyEmail,RejectToStep,PromptComplete,PromptReject,PromptUser,ShowButtonComplete,
ShowButtonReject,VotePercent,UpdateFieldValueOld,Script,Authenticate,[Description],
ShowButtonReassign '
SET @SQLString = @SQLString +
'FROM ' + QUOTENAME(@source_db) + '.dbo.RouteSteps, '
+ QUOTENAME(@source_db) + '.dbo.Routes '
SET @SQLString = @SQLString +
'WHERE ' + QUOTENAME(@source_db) + '.dbo.RouteSteps.RouteID = '
+ QUOTENAME(@source_db) + '.dbo.Routes.RouteID '
SET @SQLString = @SQLString +
'AND '+QUOTENAME(@source_db)+'.dbo.Routes.ProjectID = '+CAST(@project_id AS VARCHAR)
EXEC (@SQLString)

The errors that I receive from this statement are as follows:

Msg 207, Level 16, State 1, Line 6
Invalid column name 'ShowButtonReassign'.
Msg 102, Level 15, State 1, Line 3
Incorrect syntax near '.'.

Yes, the column "ShowButtonReassign" is a valid column in the table.
I've tried placing a print just above the EXEC and it looks like a good SQL command. I also have 20 other dynamic SQL statements similar to this one being built & executed successfully ahead of this statement in my stored proc. Any assistance would be greatly appreciated.

Regards,
-Steeltown Swami

rmiao
Master Smack Fu Yak Hacker

7266 Posts

Posted - 2008-08-12 : 23:42:50

Add 'print @SQLString' in sp to check complete statement.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-08-13 : 00:35:40

What are you paassing as values for source_db and dest_db?

bhuvnesh.dogra
Starting Member

22 Posts

Posted - 2008-08-13 : 06:19:31

HERE IS THE CORRECTED QUERY
--================================================================================
DECLARE @SQLString NVARCHAR(3000)
DECLARE @dest_db VARCHAR(30)
DECLARE @source_db VARCHAR(70)
DECLARE @project_id VARCHAR(70)
SET @SQLString = ''

SET @SQLString = 'SET IDENTITY_INSERT ' + QUOTENAME(@dest_db) + '.dbo.RouteSteps ON; '
SET @SQLstring = @SQLString + 'INSERT INTO ' + QUOTENAME(@dest_db) + '.dbo.RouteSteps '
SET @SQLstring = @SQLString +
'(RouteStepID,RouteID,StepNumber,UserID,StepName,DueDays,StepPercent,
NumProcessed,NextRouteID,UserList,StepType,FieldNumber,FieldValue,UpdateFieldNumber,
UpdateFieldValue,RouteDirection,SetNextDueDate,DueHours,StatusFlag,RouteWait,DueField,
NotifyEmail,RejectToStep,PromptComplete,PromptReject,PromptUser,ShowButtonComplete,
ShowButtonReject,VotePercent,UpdateFieldValueOld,Script,Authenticate,Description,
ShowButtonReassign) '
SET @SQLstring = @SQLString +
'SELECT RouteStepID,RouteSteps.RouteID,StepNumber,UserID,StepName,DueDays,StepPercent,
NumProcessed,NextRouteID,UserList,StepType,FieldNumber,FieldValue,UpdateFieldNumber,
UpdateFieldValue,RouteDirection,SetNextDueDate,DueHours,StatusFlag,RouteWait,DueField,
NotifyEmail,RejectToStep,PromptComplete,PromptReject,PromptUser,ShowButtonComplete,
ShowButtonReject,VotePercent,UpdateFieldValueOld,Script,Authenticate,[Description],
ShowButtonReassign '
SET @SQLString = @SQLString +
'FROM ' + QUOTENAME(@source_db) + '.dbo.RouteSteps, '
+ QUOTENAME(@source_db) + '.dbo.Routes '
SET @SQLString = @SQLString +
'WHERE ' + QUOTENAME(@source_db) + '.dbo.RouteSteps.RouteID = '
+ QUOTENAME(@source_db) + '.dbo.Routes.RouteID '
SET @SQLString = @SQLString +
'AND '+QUOTENAME(@source_db)+'.dbo.Routes.ProjectID = '+CAST(@project_id AS VARCHAR)
PRINT @SQLString
EXEC (@SQLString)

-----------------------------------

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-08-13 : 06:49:50

Do the printed query look ok?
You can tell by copy and paste the printed query and exeucute it in a query window.
Also no need to CAST @ProjectID since it already is VARCHAR.

And as a last step you should add this line for safety

SET @SQLString =@SQLString+ '; SET IDENTITY_INSERT ' + QUOTENAME(@dest_db) + '.dbo.RouteSteps OFF'

E 12°55'05.25"
N 56°04'39.16"

steelers75
Starting Member

2 Posts

Posted - 2008-08-13 : 09:06:42

Peso,

The printed query looks OK (direct from the "Messages" pane):
SET IDENTITY_INSERT [fbtest].dbo.RouteSteps ON; INSERT INTO [fbtest].dbo.RouteSteps (RouteStepID,RouteID,StepNumber,UserID,StepName,DueDays,StepPercent,
NumProcessed,NextRouteID,UserList,StepType,FieldNumber,FieldValue,UpdateFieldNumber,
UpdateFieldValue,RouteDirection,SetNextDueDate,DueHours,StatusFlag,RouteWait,DueField,
NotifyEmail,RejectToStep,PromptComplete,PromptReject,PromptUser,ShowButtonComplete,
ShowButtonReject,VotePercent,UpdateFieldValueOld,Script,Authenticate,Description,
ShowButtonReassign) SELECT RouteStepID,RouteSteps.RouteID,StepNumber,UserID,StepName,DueDays,StepPercent,
NumProcessed,NextRouteID,UserList,StepType,FieldNumber,FieldValue,UpdateFieldNumber,
UpdateFieldValue,RouteDirection,SetNextDueDate,DueHours,StatusFlag,RouteWait,DueField,
NotifyEmail,RejectToStep,PromptComplete,PromptReject,PromptUser,ShowButtonComplete,
ShowButtonReject,VotePercent,UpdateFieldValueOld,Script,Authenticate,[Description],
ShowButtonReassign FROM [fbdev].dbo.RouteSteps, [fbdev].dbo.Routes WHERE [fbdev].dbo.RouteSteps.RouteID = [fbdev].dbo.Routes.RouteID AND [fbdev].dbo.Routes.ProjectID = 15
Msg 207, Level 16, State 1, Line 6
Invalid column name 'ShowButtonReassign'.
Msg 102, Level 15, State 1, Line 3
Incorrect syntax near '.'.

The EXEC command for the SP looks like this (and like I said, my other 22 dynamic SQLs like this one are working great):

exec dbo.sp_ProjectCopy 'fbdev','fbtest',15

I'm CASTing the project ID because it's an INT, not a VARCHAR like bhuvnesh.dogra posted; that wasn't my code in his post.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-08-13 : 09:34:55

the error suggests you dont have a column by name ShowButtonReassign. are you sure you've it in table?

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources