Dynamic SQL - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Dynamic SQL

Author

Topic

Roddie
Starting Member

2 Posts

Posted - 2010-08-04 : 04:02:04

Where am I getting it wrong? Please assist. I am failing to insert @count into the variable column @DeptColumn.

SELECT RIGHT(Rtrim(IDNum),2)as DistrictOfOrigin,
COUNT([EmployeeNum]) as NoOfEmployees,DeptName
into DBA.dbo.EmployeeDistrictCount
FROM [Absalom].[dbo].[Personnel]
where Termination='0'
GROUP BY RIGHT(Rtrim(IDNum),2),DeptName
order by DistrictOfOrigin

declare @DistrictCode nvarchar(10)
declare @DeptName varchar(8000)
declare @sql varchar(8000)
declare @count int

declare @DeptColumn varchar(8000)

declare emp_cursor CURSOR
FOR
select DistrictOfOrigin,ltrim(rtrim(NoOfEmployees)), DeptName from DBA.dbo.EmployeeDistrictCount
OPEN emp_cursor
FETCH NEXT FROM emp_cursor INTO @DistrictCode,@count,@DeptName
WHILE (@@FETCH_STATUS=0)
BEGIN
set @DeptColumn=upper(@DeptName)

select @sql='insert into DBA.dbo.EmployeeDistrict (DistrictCode,[' + @DeptColumn + ']) values('''+@DistrictCode+''','''+@count+''')'
exec (@sql)

FETCH NEXT FROM emp_cursor INTO @DistrictCode,@count,@DeptName
END
CLOSE emp_cursor
DEALLOCATE emp_cursor

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2010-08-04 : 04:46:05

convert @count to string before concatenate in @sql


select @sql = 'insert into DBA.dbo.EmployeeDistrict (DistrictCode,[' 
            + @DeptColumn + ']) values('''
            + @DistrictCode + ''',''' 
            + convert(varchar(10), @count) + ''')'
exec (@sql)

KH
[spoiler]Time is always against us[/spoiler]

Roddie
Starting Member

2 Posts

Posted - 2010-08-04 : 05:47:05

Thanks KH, worked like a charm!!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources