December 10, 2008 at 5:51 am
HI,
I've coded the following stored procedure which displays 'Msg 207 - Invalid column name 'ASSETGROUP'.' error when executed. The error is with the @TableName value, it appears that the program thinks that the value 'ASSETGROUP' is actually a column name?!?!
What is wrong with my syntax? I've tried wrapping several single quotes either side of @TableName but without sucess...
GO
Alter procedure [dbo].s_ProcSelectQuery
@ServerA nvarchar(50),
@DatabaseA nvarchar(50),
@TableName nvarchar(50)
as
--Current db
DECLARE @sql nvarchar(max)
select @sql =
'INSERT INTO Prepare.DB1_Table
(
Column_Id,
Table_Name,
Column_Name,
Data_Type,
Column_Length,
[Precision],
Scale,
Allow_Nulls
)
SELECT
C.[Column_Id],
T.[Name],
C.[Name],
S.[Name],
C.[Max_Length],
C.[Precision],
C.[Scale],
S.[allownulls]
from [' + @ServerA + ']' + '.[' + @DatabaseA + '].sys.tables T JOIN [' + @ServerA + ']' + '.[' + @DatabaseA + '].sys.columns C ON
C.[Object_Id] = T.[Object_Id]
JOIN [' + @ServerA + ']' + '.[' + @DatabaseA + '].sys.systypes S ON
CAST(S.xusertype AS INT) = CAST(C.[User_Type_Id] AS INT)
WHERE T.[Name] = [' + @TableName + '] AND
T.Schema_Id = 1' --@@@@ (1 = dbo.)
exec (@SQL)
go
If I run the the same code (not as a Stroed Proc) with the value 'ASSETGROUP' is executes fine:
SELECT
C.[Column_Id],
T.[Name],
C.[Name],
S.[Name],
C.[Max_Length], --If Nvarchar these are 'doubled-up'
C.[Precision],
C.[Scale],
S.[allownulls]
FROM sys.tables T JOIN sys.columns C ON
C.[Object_Id] = T.[Object_Id]
JOIN sys.systypes S ON
CAST(S.xusertype AS INT) = CAST(C.[User_Type_Id] AS INT)
WHERE T.[Name] = 'ASSETGROUP' AND
T.Schema_Id = '1'
Any ideas?
Thanks in advance,
Neal
December 10, 2008 at 5:58 am
Needs more quotes
CAST(S.xusertype AS INT) = CAST(C.[User_Type_Id] AS INT)
WHERE T.[Name] = ''' + @TableName + ''' AND
T.Schema_Id = 1' --@@@@ (1 = dbo.)
Square brackets denote identifiers, ie table or column names. Single quotes denote string values. The single quotes have to be escaped (ie doubled) for a quote to appear within the string, which is what you need.
What the dynamic SQL was building up is the following:
... JOIN sys.systypes S ON
CAST(S.xusertype AS INT) = CAST(C.[User_Type_Id] AS INT)
WHERE T.[Name] = [ASSETGROUP] AND ...
which is not the same as the bottom block of code.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
December 10, 2008 at 6:02 am
Hi Neal
The easiest way to debug dynamic SQL is to print the content of the variable, paste it into a window and attempt to execute it.
Printing your first version gives:
[font="Courier New"]INSERT INTO PREPARE.DB1_Table
(
Column_Id,
Table_Name,
Column_Name,
Data_Type,
Column_Length,
[Precision],
Scale,
Allow_Nulls
)
SELECT
C.[Column_Id],
T.[Name],
C.[Name],
S.[Name],
C.[Max_Length],
C.[Precision],
C.[Scale],
S.[allownulls]
FROM [Server].[DataBase].sys.tables T JOIN [Server].[DataBase].sys.columns C ON
C.[Object_Id] = T.[Object_Id]
JOIN [Server].[DataBase].sys.systypes S ON
CAST(S.xusertype AS INT) = CAST(C.[User_Type_Id] AS INT)
WHERE T.[Name] = [ASSETGROUP] AND
T.Schema_Id = 1
[/font]
which immediately shows up the problem.
Here's a fix:
WHERE T.[Name] = ''' + @TableName + ''' AND
Cheers
ChrisM
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
December 10, 2008 at 6:10 am
Thanks to both of you for your help!!
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply