| | |
| | |
Terms of Agreement:
By using this article, you agree to the following terms...
1) You may use
this article in your own programs (and may compile it into a program and distribute it in compiled format for languages that allow it) freely and with no charge.
2) You MAY NOT redistribute this article (for example to a web site) without written permission from the original author. Failure to do so is a violation of copyright laws.
3) You may link to this article from another website, but ONLY if it is not wrapped in a frame.
4) You will abide by any additional copyright restrictions which the author may have placed in the article or article's description. |
Execute SQL on Multiple Tables/Columns–New feature in MS SQL SERVER 2000
Guys & Gals, for better readability download the attached word file
The base idea of this article is to give leverage to run SQL Statement or Stored Procedure or User defined Function or set of SQL Statements on every/required tables and on every/required columns in the Database.
There would have been a requirement like you wanted to add/edit/delete/search data in column(s) on more than one table using a single SQL statement, and you ended up with writing a procedure/function for achieving it. Here you now equipped with SP_execSQLonDB stored procedure that runs the SQL Statement(s)/Procedure/Function on every finding of required tables/columns in the Database.
Algorithm
1)Accept Table name filter, Column name filter, SQL, Include_NTI (i.e. whether to include Ntext, Text, Image data types during stored procedure execution, usually these data types gives error during data comparison) parameters.
2)Check whether to include TEXT, NTEXT and IMAGE data types. Check whether to include Table and column filters.
3)Fetch a Result Set that contains the Table and Column names with associated owner name by querying on SQL SERVER System Tables based on the input parameters.
4)Loop through the Result Set
4.1)Execute the supplied SQL for each row in the result set
Script
Note: Script creates objects under “DBO” owner. If you want it on different owner, change it accordingly.
CREATE PROCEDURE SP_execSQLonDB
(@TABLENAME VARCHAR(50),
@COLUMNNAME VARCHAR(50),
@SQL NVARCHAR(4000),
@INCLUDE_NTI CHAR(1) = 'N')
AS
BEGIN
--Variable Declaration
DECLARE @strSQL NVARCHAR(4000)
DECLARE @SQL2 NVARCHAR(4000)
DECLARE @sTableName VARCHAR(200)
DECLARE @sColumnName VARCHAR(200)
--Check whether to include TEXT, NTEXT, IMAGE data types
SET @INCLUDE_NTI = UPPER(LTRIM(RTRIM(@INCLUDE_NTI)))
IF @INCLUDE_NTI NOT IN ('N', 'Y')
SET @INCLUDE_NTI = 'N'
--Construct a cursor to get the list of Table/Column Names according to the @TABLENAME and @COLUMNNAME parameters.
SET @strSQL = N'DECLARE TabColCursor CURSOR FOR SELECT RTRIM(LTRIM(SU.NAME)) + ''.'' + LTRIM(RTRIM(SO.NAME)), SC.NAME FROM SYSOBJECTS SO INNER JOIN SYSCOLUMNS SC ON SO.ID = SC.ID INNER JOIN SYSUSERS SU ON SO.UID = SU.UID WHERE SO.XTYPE = ''U'' '
--Filter out Text/NText/Image data types if it is not included
IF @INCLUDE_NTI = 'N'
--In SysColumns sytem table XTYPE column corresponds to Column Data Type
SET @strSQL = @strSQL + ' AND SC.XTYPE NOT IN (35, 99, 34) '
--Add the TABLE(S) name i.e. filter if it is supplied
IF @TABLENAME IS NOT NULL AND ltrim(rtrim(@TABLENAME)) <> ''
BEGIN
SET @TABLENAME = REPLACE(@TABLENAME, ', ', ',')
SET @strSQL = @strSQL + ' AND (SO.NAME LIKE ''' + REPLACE(@TABLENAME, ',', ''' OR SO.NAME LIKE ''') + ''')'
END
--Add the COLUMN(S) name i.e. filter if it is supplied
IF @COLUMNNAME IS NOT NULL AND ltrim(rtrim(@COLUMNNAME)) <> ''
BEGIN
SET @COLUMNNAME = REPLACE(@COLUMNNAME, ', ', ',')
SET @strSQL = @strSQL + ' AND (SC.NAME LIKE ''' + REPLACE(@COLUMNNAME, ',', ''' OR SC.NAME LIKE ''') + ''')'
END
--Execute the constructed "Cursor Declaration" string
EXECUTE sp_executesql @strSQL
IF @@ERROR > 0
BEGIN
PRINT 'Error while declaring the Cursor. Please check out the parameters supplied to the Procedure'
RETURN -1
END
--Database Transaction.
BEGIN TRANSACTION gDatabaseTrans
--Open the cursor
OPEN TabColCursor
--Fetch the Table, Column names to variables
FETCH NEXT FROM TabColCursor
INTO @sTableName, @sColumnName
--Execute the SQL statement supplied in @SQL parameter on every row of Cursor's data
WHILE @@FETCH_STATUS = 0
BEGIN
--Construct SQL2 to Execute supplied @SQL
--by replacing @TABLENAME, @COLUMNNAME with running Table Name, Column Name of Cursor's data
SET @SQL2 = @SQL
SET @SQL2 = REPLACE(@SQL2, '@TABLENAME', @sTableName)
SET @SQL2 = REPLACE(@SQL2, '@COLUMNNAME', @sColumnName)
--Execute the constructed SQL2
EXECUTE sp_executesql @SQL2
--Check for errors
IF @@ERROR <> 0
BEGIN
--On Error, Destroy objects, Rollback transaction
--Return -1 as UNSUCCESSFUL flag
PRINT 'Error occurred'
DEALLOCATE tabcolcursor
ROLLBACK TRANSACTION gDatabaseTrans
RETURN -1
END
--Process Next Row of Cursor
FETCH NEXT FROM TabColCursor
INTO @sTableName,@sColumnName
END
--Destroy CURSOR object
DEALLOCATE tabcolcursor
--Procedure executed properly. Commit the Transaction.
--Return 0 as SUCCESSFUL flag
COMMIT TRANSACTION gDatabaseTrans
RETURN 0
END
Parameters
Parameter Name Description
@TABLENAME Table Name filter. Delimit with comma for multiple table names.
@COLUMNNAME Column Name filter. Delimit with comma for multiple column names.
@SQL A SQL Statement or set of SQL statements
@INCLUDE_NTI Whether to include NText, Text, Image data type columns or not.
Returns
Returns 0 on successful execution
Returns –1 on unsuccessful execution
Examples
1. This example uses NORTHWIND database.
This example updates data of all the tables where the column name contains “Name” in their names. The data being changed here is “Ltd.” to “LIMITED” by searching all the tables plus only those columns that contains word “NAME” in their names.
EXEC SP_execSQLonDB
'', --No filter for Table. Perform on all the tables.
'%NAME%', --Only those columns that contains string "Name" in their names.
'UPDATE @TABLENAME SET @COLUMNNAME = REPLACE(@COLUMNNAME, ''Ltd.'', ''LIMITED'')
WHERE @COLUMNNAME LIKE ''%Ltd.''', --Update SQL. @COLUMNNAME, @TABLENAME represents
--Column Name and Table Name of running in the loop
'N' --Dont include NTEXT, TEXT, IMAGE data types
2. This example uses NORTHWIND database.
This example checks the existence of “QUICK-stop” in all the tables whose column names contains “NAME”. This is an example of executing a set of SQL statements. A temporary table created before Executing SP_execSQLonDB. For SQL input parameter a set of SQL statements is supplied which basically checks row count for “QUICK-Stop” string, if the count is more than one then inserts the found Table Name, Column Name and Row Count in temporary table ##TMP1
CREATE TABLE ##TMP1 (TABLE_NAME VARCHAR(200), COLUMN_NAME VARCHAR(200), ROW_COUNT INT)
EXEC SP_execSQLonDB
'',
'%NAME%',
'DECLARE @ICOUNT AS INT
SELECT @ICOUNT = COUNT(1) FROM @TABLENAME WHERE @COLUMNNAME = ''QUICK-Stop''
IF @ICOUNT > 0
INSERT INTO ##TMP1 SELECT ''@TABLENAME'', ''@COLUMNNAME'', @ICOUNT',
'N'
3. Example to supply Stored procedure as SQL Statement for SP_execSQLonDB.
This example executes USP_DeptStats stored procedure on all the tables that starts with “Employee” plus columns that starts with “DEPT”. The @TABLENAME, @COLUMNNAME in the SQL parameters corresponds to running Table Name and Column Name respectively as per the result set of Cursor declaration.
EXEC SP_execSQLonDB
'EMPLOYEE%',
'DEPT%',
'EXEC USP_DeptStats ''@TABLENAME'', ''@COLUMNNAME''',
'N'
4. Example to supply multiple Table and Column names to @TABLENAME, @COLUMNNAME input parameters. This will execute “USP_EMPLOYEE_PF” procedure on all the tables whose name starts with either “Employee” or “PF”, on all the columns whose name is either “SALARY” or name containing word “AMOUNT”.
EXEC SP_execSQLonDB
'EMPLOYEE%, PF%',
'SALARY, %AMOUNT%',
'EXEC USP_EMPLOYEE_PF',
'N'
Expansion
SP_execSQLonDB can be expanded like.
Include Data type filter by introducing one more Input parameter, include it in Cursor declaration part using SYSCOLUMNS table XTYPE COLUMN.
Change SP to perform SQL execution only on finding of Table names, avoid execution on every column.
Usage
Due to Technical standards and ethics you may not choose this option to Edit/Add data in this fashion on “Live” version, but you can always use this for statistical/analytical purpose. In Development version you can try any new things/approaches, even adding/editing/deleting can be tried.
Conclusion
The cause for this article was a question posted in MSDN Newsgroup under “sqlserver.Programming” section asking for single UPDATE SQL for updating the column in many tables. The MS SQL Server 2000 is provided with enormous number of System tables/functions/procedures/etc. that makes life easy to give solution to critical problems. Try to explore all the features/specialties of SQL Server 2000; it may hit your mind while you thinking about the solution for requirements/problems.
Raghavendra Narayana
Raghavendra.Narayana@thomson.com
Senior Software Engineer,
Thomson Financial, Bangalore
www.thomson.com/financial
| |  Download article
Note: Due to the size or complexity of this submission, the author has submitted it as a .zip file to shorten your download time. Afterdownloading it, you will need a program like Winzip to decompress it.
Virus note:All files are scanned once-a-day by Planet Source Code for viruses,but new viruses come out every day, so no prevention program can catch 100% of them.
FOR YOUR OWN SAFETY, PLEASE:
1)Re-scan downloaded files using your personal virus checker before using it.
2)NEVER, EVER run compiled files (.exe's, .ocx's, .dll's etc.)--only run source code.
If you don't have a virus scanner, you can get one at many places on the net including:McAfee.com
|
Terms of Agreement:
By using this article, you agree to the following terms...
1) You may use
this article in your own programs (and may compile it into a program and distribute it in compiled format for languages that allow it) freely and with no charge.
2) You MAY NOT redistribute this article (for example to a web site) without written permission from the original author. Failure to do so is a violation of copyright laws.
3) You may link to this article from another website, but ONLY if it is not wrapped in a frame.
4) You will abide by any additional copyright restrictions which the author may have placed in the article or article's description. |
Other 9 submission(s) by this author
| | | | | Report Bad Submission | | | | | Your Vote! | |
See Voting Log | | | | Other User Comments | 9/18/2003 12:20:34 PM:Murilo C. M. Filho
Very good procedure. Help me a lot on
my Database.
Higly recommended!
| | | Add Your Feedback! | Note:Not only will your feedback be posted, but an email will be sent to the code's author in your name.
NOTICE: The author of this article has been kind enough to share it with you. If you have a criticism, please state it politely or it will be deleted.
For feedback not related to this particular article, please click here.
| |
|
