How to declare Dynamic Variable names

D V Krishna Reddy

7y932

Hi,

I need to how to declare dynamic variable names from table in a stored procedure i am using like below .but it wont work can anyone help me out .

WHILE @i < 6 --- (select Count(*) from List_Of_SMSC_Gateways where IsDeleted=0 )

BEGIN

select @gwayName= Gateway_Name from (SELECT ROW_NUMBER() OVER (ORDER BY Gateway_Name Asc) AS Rno, Gateway_Name

from List_Of_SMSC_Gateways where IsDeleted=0 ) as x where x.Rno=@i

set @sql=@sql+' Declare @'+@gwayName+'_reseller_Count Numeric = 0 , @'+@gwayName+'_smcountry_count Numeric = 0;'

set @sql=@sql+' Declare @Smsc_'+@gwayName+'_Total_count Numeric=0;'

set @sql=@sql+' Declare @Resller_'+@gwayName+'_Total_count Numeric=0;'

set @sql=@sql+' Declare @Total_'+@gwayName+'_cnt Numeric=0;'

set @i=@i+1

END;

execute(@sql)

and need to use this variable across stored procedure

thanks in advance

2 Replies

Know the answer? Post it — somebody with the same question will find it here.

D V Krishna Reddy7y ago
need to declare variables not table column names...my requirement is need to declare dynamic variables based on table data in a stored procedure and use them across stored procedure
0
Sonu Gupta7y ago
Hi,

you create like this --

CREATE PROCEDURE dynamic_tbl
@ColumnCount int=0
AS
BEGIN
Declare @ColumnName nvarchar(10)='col_'
Declare @count int =1
IF(@ColumnCount>0)
BEGIN
IF (EXISTS (SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA = 'dbo' AND TABLE_NAME = 'dyn_tbl'))
BEGIN

DROP TABLE dyn_tbl
END
CREATE TABLE [dbo].[dyn_tbl]
( col_1 varchar(20) )
SET @count=@count +1;
WHile(@ColumnCount>=@count)
BEGIN
SET @ColumnName='col_'+CONVERT(varchar(2),@count)
EXEC ('ALTER TABLE dyn_tbl ADD ['+@ColumnName +'] varchar(20)')
SET @count=@count +1;
END
END
END
0