Stripeman
11 Aug 2008, 11:41 AM
I didn't know where else to post this because its not directly related to EXT. But I thought upldating a database column via variable or even updating a table via variable was not a common find. I searched and searched for this solution and found only one. (REF: http://vyaskn.tripod.com/sql_server_search_and_replace.htm) We took this example, simplified it and created our own Stored Procedure.
I hope this is the right spot. Feel free to move it if it don't belong here.
USE [AUTHENTICATION] //DB name
GO
/****** Object: StoredProcedure [dbo].[usp_UpdateOneUserColumn] Script Date: 08/08/2008 16:03:52 ******/
SET ANSI_NULLS ON //all comparisons against a null value evaluate to UNKNOWN
GO
SET QUOTED_IDENTIFIER ON //identifiers can be delimited by double quotation marks, and literals must be delimited by single quotation marks
GO
-- =============================================
-- Author: <Greg M and Terry R>
-- Create date: <08/08/2008 16:03:52>
-- Description: <Update column/table via variable>
-- =============================================
CREATE PROCEDURE [dbo].[usp_UpdateOneUserColumn] /or UPDATE PROCEDURE [USER].[Stored Proceedure Name]
-- Add the parameters for the stored procedure here
@userid nvarchar(20),
@tablename nvarchar(128),
@col nvarchar(128),
@val nvarchar(1000)
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
DECLARE @SQL NVARCHAR(4000)
BEGIN
SET @SQL = 'UPDATE ' + @tablename + ' SET ' + @col + ' = ''' + @val + ''' WHERE userid = ''' + @userid + ''''
EXEC(@SQL)
END
END
//TO use:
EXEC dbo.usp_UpdateOneUserColumn '0239523622', 'tbl_name', 'col_username', 'Remsikt' //User ID, Table name, Column, and product type (value)
//Please note order: the arguments have to be in order in which is specified by the definitions above
//IE: @userid nvarchar(20),
// @tablename nvarchar(128),
// @col nvarchar(128),
// @val nvarchar(1000)
I hope this is the right spot. Feel free to move it if it don't belong here.
USE [AUTHENTICATION] //DB name
GO
/****** Object: StoredProcedure [dbo].[usp_UpdateOneUserColumn] Script Date: 08/08/2008 16:03:52 ******/
SET ANSI_NULLS ON //all comparisons against a null value evaluate to UNKNOWN
GO
SET QUOTED_IDENTIFIER ON //identifiers can be delimited by double quotation marks, and literals must be delimited by single quotation marks
GO
-- =============================================
-- Author: <Greg M and Terry R>
-- Create date: <08/08/2008 16:03:52>
-- Description: <Update column/table via variable>
-- =============================================
CREATE PROCEDURE [dbo].[usp_UpdateOneUserColumn] /or UPDATE PROCEDURE [USER].[Stored Proceedure Name]
-- Add the parameters for the stored procedure here
@userid nvarchar(20),
@tablename nvarchar(128),
@col nvarchar(128),
@val nvarchar(1000)
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
DECLARE @SQL NVARCHAR(4000)
BEGIN
SET @SQL = 'UPDATE ' + @tablename + ' SET ' + @col + ' = ''' + @val + ''' WHERE userid = ''' + @userid + ''''
EXEC(@SQL)
END
END
//TO use:
EXEC dbo.usp_UpdateOneUserColumn '0239523622', 'tbl_name', 'col_username', 'Remsikt' //User ID, Table name, Column, and product type (value)
//Please note order: the arguments have to be in order in which is specified by the definitions above
//IE: @userid nvarchar(20),
// @tablename nvarchar(128),
// @col nvarchar(128),
// @val nvarchar(1000)