Stored Procedure problem >>
-
Hay , I edit a stored procedure to select a Name column from various tables as the next :
Create PROCEDURE [dbo].[SelectName] @table_name sysname, @sName nvarchar(50) AS exec ( ' SELECT * FROM ' + @table_name + ' WHERE Name =' + @sName )then when i execute it and pass the next valriables@table_name = 'emp' @sName = 'NameTest'the next error message appearInvalid column name 'NameTest'what the error here ,, thanksjooooo
-
Hay , I edit a stored procedure to select a Name column from various tables as the next :
Create PROCEDURE [dbo].[SelectName] @table_name sysname, @sName nvarchar(50) AS exec ( ' SELECT * FROM ' + @table_name + ' WHERE Name =' + @sName )then when i execute it and pass the next valriables@table_name = 'emp' @sName = 'NameTest'the next error message appearInvalid column name 'NameTest'what the error here ,, thanksjooooo
If you add a print statement to your stored procedure, such as: print ' SELECT * FROM ' + @table_name + ' WHERE Name =' + @sName and execute it, you'll see this as output: SELECT * FROM emp WHERE Name =NameTest Msg 207, Level 16, State 1, Line 1 Invalid column name 'NameTest'. From there you can see that it's trying to compare the Name column to what it interprets as another column name - not a text literal. You need to put the literal in quotes, so your procedure would need to look like: exec ( ' SELECT * FROM ' + @table_name + ' WHERE Name =''' + @sName + '''') That being said, that code is dangerous, and you should read up on SQL injection, e.g. SQL Injection Attacks and Some Tips on How to Prevent Them[^] Scott
-
Hay , I edit a stored procedure to select a Name column from various tables as the next :
Create PROCEDURE [dbo].[SelectName] @table_name sysname, @sName nvarchar(50) AS exec ( ' SELECT * FROM ' + @table_name + ' WHERE Name =' + @sName )then when i execute it and pass the next valriables@table_name = 'emp' @sName = 'NameTest'the next error message appearInvalid column name 'NameTest'what the error here ,, thanksjooooo
Err, the table 'emp' doesn't have a column 'NameTest'? You want to try and avoid that dynamic sql stuff. No query plans and not safe.
Regards, Rob Philpott.
-
If you add a print statement to your stored procedure, such as: print ' SELECT * FROM ' + @table_name + ' WHERE Name =' + @sName and execute it, you'll see this as output: SELECT * FROM emp WHERE Name =NameTest Msg 207, Level 16, State 1, Line 1 Invalid column name 'NameTest'. From there you can see that it's trying to compare the Name column to what it interprets as another column name - not a text literal. You need to put the literal in quotes, so your procedure would need to look like: exec ( ' SELECT * FROM ' + @table_name + ' WHERE Name =''' + @sName + '''') That being said, that code is dangerous, and you should read up on SQL injection, e.g. SQL Injection Attacks and Some Tips on How to Prevent Them[^] Scott
Thanks for reply I use print as you said <code>print ' SELECT * FROM ' + @table_name + ' WHERE Name ="' + @sName + '"' it's ok no errors ,, but no rows are returned ,, for eg
@sName = "Jooo"this name is already exit but no rows return ,, I test the same statement but with another column type <code>print ' SELECT * FROM ' + @table_name + ' WHERE ID =' + @iID@iID = 1it's ok and return the the required row, what the diff between nvarchar and int column to get the int and nvarchar nojooooo
-
Thanks for reply I use print as you said <code>print ' SELECT * FROM ' + @table_name + ' WHERE Name ="' + @sName + '"' it's ok no errors ,, but no rows are returned ,, for eg
@sName = "Jooo"this name is already exit but no rows return ,, I test the same statement but with another column type <code>print ' SELECT * FROM ' + @table_name + ' WHERE ID =' + @iID@iID = 1it's ok and return the the required row, what the diff between nvarchar and int column to get the int and nvarchar nojooooo
The print statement that I mentioned was just to help you debug what was happening. Did you also change your exec statement as I recommended? The difference between the nvarchar and int columns is that to find something in the nvarchar column you need to specify a text literal, which needs to be in quotes. If it's not in quotes it's going to interpret it as another column name. To find something in the int column you specify a numeric literal, which doesn't need to be in quotes, and the numerical literal won't be interpreted as a column name. Scott