/* ------------------------ My Meta Content Here SEO ------------------------ */

Pages

Main Menu




Friday, March 4, 2016

T-SQL: SQL SERVER CURSOR A Simple Example Using a Cursor

T-SQL: SQL SERVER CURSOR A Simple Example Using a Cursor

First Create Table

CREATE TABLE [dbo].[Tbl_Broadcast_Mktg](
       [Broadcast_Id] [int] IDENTITY(1,1) NOT NULL,
       [Broadcast_Campaign] [nvarchar](100) NULL,
       [Broadcast_Coupon] [int] NULL,
       [Broadcast_Custdb] [int] NULL,
       [Broadcast_Custdb_Flag] [nvarchar](50) NULL,
       [Broadcast_Date] [datetime] NULL,
       [SMS_count] [nvarchar](50) NULL,
       [total_sms_sent] [nvarchar](50) NULL,
       [total_broadcast_redemption] [nvarchar](50) NULL,
       [total_redemption_sale] [nvarchar](50) NULL,
       [total_redemption_percentage] [nvarchar](50) NULL,
       [broadcast_status_type] [nvarchar](50) NULL,
       [User_Id] [int] NULL,
       [Broadcast_Status] [int] NULL,
       [Db_type] [nvarchar](50) NULL,
       [tot_sms_count] [numeric](18, 0) NULL,
       [tot_targeted_sms_count] [numeric](18, 0) NULL,
       [tot_nontargeted_sms_count] [numeric](18, 0) NULL,
       [sms_api_code] [nvarchar](50) NULL,
       [sms_api_account] [nvarchar](50) NULL,
       [campaign_cost] [numeric](18, 3) NULL,
       [tot_sms_sent] [numeric](18, 0) NULL,
 CONSTRAINT [PK_Tbl_Broadcast_Mktg] PRIMARY KEY CLUSTERED(       [Broadcast_Id] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]
 ALTER TABLE [dbo].[Tbl_Broadcast_Mktg] ADD  CONSTRAINT [DF_Tbl_Broadcast_Mktg_Broadcast_Date]  DEFAULT (getdate()) FOR [Broadcast_Date]
ALTER TABLE [dbo].[Tbl_Broadcast_Mktg] ADD  CONSTRAINT [DF_Tbl_Broadcast_Mktg_Broadcast_Status]  DEFAULT ((1)) FOR [Broadcast_Status]

 

Here We Will Alter Few Nvarchar Columns In To Numeric Data Type Using CURSOR


 DECLARE @DBName nvarchar(50), @ColumnName nvarchar(50), @DataType nvarchar(50)         
 DECLARE DataType_cursor CURSOR    FOR              SELECT   DB_Name() 'DBName',  c.name 'ColumnName',    t.Name 'DataType'  FROM sys.columns c       INNER JOIN              sys.types t ON c.user_type_id = t.user_type_id       LEFT OUTER JOIN              sys.index_columns ic ON ic.object_id = c.object_id AND ic.column_id = c.column_id       LEFT OUTER JOIN              sys.indexes i ON ic.object_id = i.object_id AND ic.index_id = i.index_id       WHERE
              c.object_id = OBJECT_ID('Tbl_Broadcast_MKtg')              and c.name in('tot_sms_sent','total_broadcast_redemption','total_redemption_sale','campaign_cost')
              and t.Name <> 'numeric'
             OPEN DataType_cursor
 FETCH NEXT FROM DataType_cursor INTO @DBName, @ColumnName, @DataType
 WHILE @@FETCH_STATUS = 0        
BEGIN                    PRINT 'DBName : '+ convert(varchar(20),@DBName)+', ColumnName : '+@ColumnName+ ', DataType : '+convert(varchar(20),@DataType)
              SELECT @DBName, @ColumnName, @DataType                                          DECLARE @SQLQuery AS NVARCHAR(500)
              /* Build Transact-SQL String with parameter value */
              SET @SQLQuery = 'alter table      Tbl_Broadcast_MKtg alter column ' + @ColumnName + ' numeric'                           /* Execute Transact-SQL String */
              EXECUTE(@SQLQuery)
        FETCH NEXT FROM DataType_cursor INTO @DBName, @ColumnName, @DataType                           ENDCLOSE DataType_cursor
 DEALLOCATE DataType_cursor
 

Alter columns of a table in multiple database server using sp_msforeachdb cursor dynamic query 

DECLARE @AllTables table (DBName nvarchar(4000),ColumnName nvarchar(4000),DataType nvarchar(4000))
DECLARE @SQL   nvarchar(4000)
SET @SQL=' USE [?]
IF DB_ID(''?'') > 4    BEGIN        USE [?]               IF EXISTS (select * from sys.tables where name = N''Tbl_Broadcast_MKtg'')                           BEGIN                                  DECLARE @DBName nvarchar(50), @ColumnName nvarchar(50), @DataType nvarchar(50)                                         DECLARE DataType_cursor CURSOR    FOR                           SELECT   DB_Name() ''DBName'',  c.name ''ColumnName'',    t.Name ''DataType''  FROM sys.columns c                           INNER JOIN                                  sys.types t ON c.user_type_id = t.user_type_id                           LEFT OUTER JOIN                                  sys.index_columns ic ON ic.object_id = c.object_id AND ic.column_id = c.column_id                           LEFT OUTER JOIN                                  sys.indexes i ON ic.object_id = i.object_id AND ic.index_id = i.index_id                           WHERE                                  c.object_id = OBJECT_ID(''Tbl_Broadcast_MKtg'')                                  and c.name in(''tot_sms_sent'',''total_broadcast_redemption'',''total_redemption_sale'',''campaign_cost'')                                  and t.Name <> ''numeric''                                                      OPEN DataType_cursor                                         FETCH NEXT FROM DataType_cursor INTO @DBName, @ColumnName, @DataType                      WHILE @@FETCH_STATUS = 0                                    BEGIN                                                                                               DECLARE @SQLQuery AS NVARCHAR(500)                                  /* Build Transact-SQL String with parameter value */                                  SET @SQLQuery = ''alter table     Tbl_Broadcast_MKtg alter column '' + @ColumnName + '' numeric''                                                                   /* Execute Transact-SQL String */                                  EXECUTE(@SQLQuery)                                                                   SELECT @DBName, @ColumnName, @DataType                            FETCH NEXT FROM DataType_cursor INTO @DBName, @ColumnName, @DataType                                                                   END                     CLOSE DataType_cursor                     DEALLOCATE DataType_cursor              END    END             'INSERT INTO @AllTables (DBName,ColumnName,DataType)
    EXEC sp_msforeachdb @SQLSET NOCOUNT OFF
SELECT * FROM @AllTables ORDER BY 1
  


1 comment:


  1. All are saying the same thing repeatedly, but in your blog I had a chance to get some useful and unique information, I love your writing style very much, I would like to suggest your blog in my dude circle, so keep on updates.



    SAP training in Chennai
    SAP ABAP training in Chennai
    SAP FICO training in Chennai
    SAP MM training in Chennai
    SAP SD training in Chennai
    SAP HR training in Cheennai

    ReplyDelete

Recent Posts

My Blog List