Dynamic SQL Results to datagrid

[bsummers]

This is my first time using dynamic sql. My goal is to populate a datagrid with a stored procedure that uses a incoming parameter that equals a column name in the table. I have 2 problems.
1. I can get each sql statment to execute independently with the correct results. However, when I try a UNION ALL statement to put all the results together I get errors.
2. I don't know how to get the results into a data grid. When I try to execute the stored procedure, the result is the sql statement, not the data. I'm sure there is something missing in the execute statement.
My code is posted below.

@intExample As int,
@vchZip1 as varchar(5),
@vchZip2 as varchar(5),
@vchZip3 as varchar(5),
@vchZip4 as varchar(5),
@vchZip5 as varchar(5)

AS

Declare @sqlresult nvarchar(1000)
declare @sql1 Nvarchar(1000)
declare @sql2 Nvarchar(1000)
declare @sql3 Nvarchar(1000)
declare @sql4 Nvarchar(1000)
declare @sql5 Nvarchar(1000),
@col sysname

select @col= @vchZip1
set @sql1 = 'select vchCompanyName, vchWebsite, vchAMRating, [' + @col + ']as Rate1 FROM tblPCompAutoRates b
Inner join tblPCompPasswords
on b.vchNAICNumber = tblPCompPasswords.vchNAICNumber
WHERE intExample = ' + @intexample + '
And b.dtEffectiveDate <= GetDate()
and b.dtEffectiveDate in
(Select Max(dtEffectiveDate) From tblPCompAutoRates a
Where a.vchNAICNumber = b.vchNAICNumber And a.dtEffectiveDate <= GetDate()
and a.intExample = ' + @intExample + ')'

select @col= @vchZip2
set @sql2 = 'select vchCompanyName, vchWebsite, vchAMRating, [' + @col + ']as Rate2 FROM tblPCompAutoRates b
Inner join tblPCompPasswords
on b.vchNAICNumber = tblPCompPasswords.vchNAICNumber
WHERE intExample = ' + @intexample + '
And b.dtEffectiveDate <= GetDate()
and b.dtEffectiveDate in
(Select Max(dtEffectiveDate) From tblPCompAutoRates a
Where a.vchNAICNumber = b.vchNAICNumber And a.dtEffectiveDate <= GetDate()
and a.intExample = ' + @intExample + ')'

select @col= @vchZip3
set @sql3 = 'select vchCompanyName, vchWebsite, vchAMRating, [' + @col + ']as Rate3 FROM tblPCompAutoRates b
Inner join tblPCompPasswords
on b.vchNAICNumber = tblPCompPasswords.vchNAICNumber
WHERE intExample = ' + @intexample + '
And b.dtEffectiveDate <= GetDate()
and b.dtEffectiveDate in
(Select Max(dtEffectiveDate) From tblPCompAutoRates a
Where a.vchNAICNumber = b.vchNAICNumber And a.dtEffectiveDate <= GetDate()
and a.intExample = ' + @intExample + ')'

select @col= @vchZip4
set @sql4 = 'select vchCompanyName, vchWebsite, vchAMRating, [' + @col + ']as Rate4 FROM tblPCompAutoRates b
Inner join tblPCompPasswords
on b.vchNAICNumber = tblPCompPasswords.vchNAICNumber
WHERE intExample = ' + @intexample + '
And b.dtEffectiveDate <= GetDate()
and b.dtEffectiveDate in
(Select Max(dtEffectiveDate) From tblPCompAutoRates a
Where a.vchNAICNumber = b.vchNAICNumber And a.dtEffectiveDate <= GetDate()
and a.intExample = ' + @intExample + ')'

select @col= @vchZip5
set @sql5 = 'select vchCompanyName, vchWebsite, vchAMRating, [' + @col + ']as Rate5 FROM tblPCompAutoRates b
Inner join tblPCompPasswords
on b.vchNAICNumber = tblPCompPasswords.vchNAICNumber
WHERE intExample = ' + @intexample + '
And b.dtEffectiveDate <= GetDate()
and b.dtEffectiveDate in
(Select Max(dtEffectiveDate) From tblPCompAutoRates a
Where a.vchNAICNumber = b.vchNAICNumber And a.dtEffectiveDate <= GetDate()
and a.intExample = ' + @intExample + ')'

set @sqlresult=(@sql1+ ' UNION ALL ' + @sql2+ ' UNION ALL ' + @sql3+ ' UNION ALL ' + @sql4+ ' UNION ALL ' +@sql5)
exec sp_executesql @sqlresult

[h4ng4r18]

In dynamic SQL you are building string. You have to cast any non-string datatypes.

' + @intexample + '

becomes

' + CAST(@intexample as varchar(5)) + '

Personally - I would Declare a varchar variable and cast into it once at the top.