SQL Server Performance Forum – Threads Archive

Error with the Procedure

Hi All, I was trying to write a simple stored procedure.Here it is CREATE PROCEDURE Test(@tableName nvarchar(10),
@fieldName nvarchar(10),
@newValue nvarchar(1000),
@oldValue nvarchar(1000))
AS –DECLARE @tableName nvarchar(10)
–DECLARE @newValue nvarchar(1000) DECLARE @SQLString VARCHAR(1000)
DECLARE @S2 NVARCHAR(1000) SET @SQLString = ‘ update @tableName set @[email protected] where @[email protected]‘
— convert variable from VARCHAR to NVARCHAR
Select @S2 = CAST(@SQLString as NVarchar(1000))
EXECUTE sp_executesql @S2
Go Test ‘tbl_test’,’name’,’Raaz’,’Ram’ This stored procedure takes tablename,fieldname,newvalue,oldvalue as parameter and update the table with a new value. When i am excuting the storedprocedure,It gives the following error. Server: Msg 137, Level 15, State 2, Line 1
Must declare the variable ‘@tableName’.
Server: Msg 137, Level 15, State 1, Line 1
Must declare the variable ‘@newValue’. Please help me to resolve the error. Thanks Surjit

---

Change as followes
SET @SQLString = ‘ update’ + @tableName + ‘set’ + @fieldName + ‘=’ + @newValue + ‘where’ + @fieldName + ‘=’ + @oldValue or use sp_executesql —————————————-
http://dineshasanka.blogspot.com/

---

CREATE PROCEDURE Test
@tableName nvarchar(10),
@fieldName nvarchar(10),
@newValue nvarchar(1000),
@oldValue nvarchar(1000) AS
DECLARE @sqlstring nvarchar(1000)
SET @sqlstring = ‘UPDATE ‘ + @tableName + ‘ SET ‘ + @fieldName + ‘= ”’ + @newValue +
”’ WHERE ‘ + @fieldName + ‘=”’ + @oldValue + ”” EXECUTE sp_executesql @sqlstring
Go Test ‘tbl_test’,’name’,’Raaz’,’Ram’
This will work out. Try it..

---

Whay are you passing object names as parameters?
Read this
http://www.sommarskog.se/dynamic_sql.html Madhivanan Failing to plan is Planning to fail

---

Thanks a lot to all of you.It worked perfectly fine. Yes, there is security issue due to Sql injection while we pass objects as parameter. But for my application it is not that much concerned.Thanks Madhivanan,the article is very informative. Thanks again. Surjit

---

]]>