sql query
-
hi, i m using this store procedure in my project ALTER procedure getkeyname(@city sysname,@key varchar(50)) as exec ('select distinct a.keywordname,a.keywordid from keyword As a INNER JOIN ' + @city+ ' As b on a.keywordid=b.keywordid and a.keywordname like ' + @key) but i got an error like.. invalid column name that i entered in @key parameter i want to pass my table name dynamically and parameter name but it accept only @city as a table name parameter and it treat @key as Colunm name like @city but i want to pass @key as parameter. please help me
no knowledge in .net
-
hi, i m using this store procedure in my project ALTER procedure getkeyname(@city sysname,@key varchar(50)) as exec ('select distinct a.keywordname,a.keywordid from keyword As a INNER JOIN ' + @city+ ' As b on a.keywordid=b.keywordid and a.keywordname like ' + @key) but i got an error like.. invalid column name that i entered in @key parameter i want to pass my table name dynamically and parameter name but it accept only @city as a table name parameter and it treat @key as Colunm name like @city but i want to pass @key as parameter. please help me
no knowledge in .net
Dyamic SQL - Something you have to be very careful in constructing because it is so easy to make a mistake and punch a big massive security hole in your database. The way you have constructed the SQL make SQL Server interpret @key as a column name. You have to pass @key as a parameter. Actually, you should look up
sp_executesqlas it provides more protection against attack. Now, as a further defence against attack you must check that @city relates to a real table first. You can do this by checking againstINFORMATION_SCHEMA.TABLESlike this:IF EXISTS (SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = @city)
BEGIN
-- The table exists, we can run the dynamic SQL
END
ELSE
BEGIN
-- The table does not exist. Perform error processing.
ENDRecent blog posts: * Introduction to LINQ to XML (Part 1) - (Part 2) - (part 3) My website | Blog
-
hi, i m using this store procedure in my project ALTER procedure getkeyname(@city sysname,@key varchar(50)) as exec ('select distinct a.keywordname,a.keywordid from keyword As a INNER JOIN ' + @city+ ' As b on a.keywordid=b.keywordid and a.keywordname like ' + @key) but i got an error like.. invalid column name that i entered in @key parameter i want to pass my table name dynamically and parameter name but it accept only @city as a table name parameter and it treat @key as Colunm name like @city but i want to pass @key as parameter. please help me
no knowledge in .net
@key must be in quotes. ALTER procedure getkeyname(@city sysname,@key varchar(50)) as exec ('select distinct a.keywordname,a.keywordid from keyword As a INNER JOIN ' + @city + ' As b on a.keywordid=b.keywordid and a.keywordname like ''' + @key + '''')