August 28, 2008 at 10:29 pm
Hi,
I am not quite sure how to do this but it seems someone with experience with stored procs would think this is simple.
I want to send in a column name dynamically to a stored proc to do an update on that field.
For example, I have a 'User' table, and if a user chooses to update his first name in the ui, i send that info to the proc
so my parameters to the stored proc, i assume, would be something like
@ColumnName
@ColumnValue
so the final sql would be denerated dynamically to ultimately say.
Update Users set '@ColumnName' = '@ColumnValue where UserId = @userid
or for example:
Update Users set FirstName = 'Joe' where UserId = 2
does this make sense?.
the problem is that not all data column types are strings, some may be datatimes or integers
any help would be greatly appreciated.
Thanks!!
Doug
August 28, 2008 at 11:28 pm
Hi,
Here you can do this by 2 ways.
1. pass the data type of the column to stored procedure, and create dynamic update statements.
2. you can check datatype of column using system views, like..
SELECT Data_Type from INFORMATION_SCHEMA.COLUMNS
WHERE Table_Name='tableName' and column_name='columnName'
August 28, 2008 at 11:34 pm
ok thanks for the reply.
so for example 1,
could you give me an example of how i would construct the stored procedure ?
thanks, im a rookie.
August 29, 2008 at 12:50 am
You can try another way.
Supose you have this table:
TABLE1
(UserID Integer,
FirstName Varchar(30),
SecondName Varchar(30),
BirthDay Datetime,
Salary DECIMAL(9,2)
)
You can make a store procedure to update only the parameters you receive using the CASE function in something like this:
Create procedure spUpdateUser
@pUserID integer,
@pFirstName varchar(30),
@pSecondName varchar(30),
@pBirthDay datetime,
@pSalary decimalo(9,2)
AS
UPDATE TABLE1
SET
FirstName = CASE @pFirstName WHEN NULL THEN FirstName ELSE @pFirstName END,
SecondName = CASE @pSecondName NULL THEN SecondName ELSE @pSecondName END,
Salary = CASE @pSalary WHEN NULL THEN Salary ELSE @pSalary END
WHERE
UserID = @pUserID
GO
Using the CASE Function you can update only the fiels you are receiving in the call.
Example:
spUpdateUser 12, null, null, '1980-06-06', 33333.33
August 29, 2008 at 1:04 am
You can use case, which will ease your problem. But when fields are more you can write dynamic query like this
you will pass 4 parameters to stored procedure, say
@ColumnName
@ColumnValue
@DataType
then you have to create a dynamic query like this,
DECLARE @SQL VARCHAR(8000)
SET @SQL='UPDATE USERS SET '
IF (LOWER(@DataType) IN ('int','smallint','float'))
--u can add some more data types
BEGIN
SET @SQL=@SQL+@ColumnName+'='+@ColumnValue +' WHERE UserId='+@UserId
END
ELSE IF (LOWER(@DataType) IN ('datetime','varchar','char')
-- u can add more data types
BEGIN
SET @SQL=@SQL+@ColumnName+'='''+@ColumnValue +''' WHERE UserId='+@UserId
END
EXEC (@SQL)
August 29, 2008 at 10:12 am
this is great!! thanks for all of your input!
August 29, 2008 at 7:23 pm
Perfect target for SQL injection!
If you think you product may be interesting for somebody one day you better:
- check that @ColumnName matches one of COLUMN_NAMEs in
INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = 'TableName'
- use sp_executesql instead of EXEC and supply @ColumnValue as a parameter to this procedure.
_____________
Code for TallyGenerator
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply