/* ------------------------ 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:

My Blog List

  • काश - काश मुझे भी पीने की आदत होती,मैं कब का मुर्दा हो गया होता। छुटकारा मिलता आज के आतंकवाद से, किसी संतान भूमि में सो गया होता। मेरा एतबार कौन करेगा, मैंने मुर...
    2 months ago
  • काश - काश मुझे भी पीने की आदत होती,मैं कब का मुर्दा हो गया होता। छुटकारा मिलता आज के आतंकवाद से, किसी शमशान भूमि में सो गया होता। मेरा एतबार कौन करेगा, मैंने मुर...
    2 months ago
  • Kumaon University Nainital B.Ed entrance exam test result 2012 - कुमाऊँ विश्वविधालय, नैनीताल (उत्तराखण्ड)
    10 years ago