dynamic sql
-
I run this dynamic SQL:
DECLARE @DB varchar(25)
SET @DB = 'pmidba'
DECLARE @Tablename varchar(50)
SET @TableName = 'test'
DECLARE @strSQL varchar(8000)
SET @strSQL = 'Create table '+@DB+'.dbo.'+@TableName+' ([clmnt_idno] varchar (255) NULL)'
EXEC @strSQL
and get this:
Server: Msg 203, Level 16, State 2, Line 7
The name 'Create table pmidba.dbo.test ([clmnt_idno] varchar (255) NULL)' is not a valid identifier.
What am I doing wrong?
-
never mind...forgot the parens around @strSQL...
-
I thought it was
exec sp_executesql @strSQL
-
DECLARE @DB varchar(25)
SET @DB = 'tempdb'
DECLARE @Tablename varchar(50)
SET @TableName = 'test12'
DECLARE @strSQL nvarchar(4000)
SET @strSQL = 'Create table '+@DB+'.dbo.'+@TableName+' ([clmnt_idno] varchar (255) NULL)'
exec sp_executesql @strSQL
This may solve ur problem.
Thanks & Regards,
9989069383
Katakam. -
If you use the sp_executesql stored procedure, then yes, that will work too. Need to be careful with dynamic SQL as it's not for every situation. See link: http://www.sommarskog.se/dynamic_sql.html
-
...and if any of your variables can be populated by user data you should take steps to prevent potential career altering SQL injections...
PeteK
I have CDO. It's like OCD but all the letters are in alphabetical order... as they should be.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply