Pass a "Begin...End" Block from ASP

Is it okay to pass a Begin...End block to Sql Server from an ASP web page?
I
have a situation where one of my tables contains the column names that I nee
d
to select from another table. I have always used two separate select
statements (with two separate trips to the db) to get the values I need, but
I recently found that I can accomplish the same thing by passing a
Begin...End block like this...
begin
declare @.col_list varchar(8000)
select @.col_list = coalesce(@.col_list + ', ', '') +
approverlabel from approvers
where formid=6 order by approverorder
exec('select ' + @.col_list + ' from formconfigs where pid=2701')
end
Is there a reason why this should not be done? I realize this would be
better if it was implemented in a stored procedure.Yes this would be best in a stored procedure so it can reuse the query plan.
But in any case you don't need a BEGIN - END. If you send it as one batch
it will work fine.
Andrew J. Kelly SQL MVP
"creed1" <creed1@.discussions.microsoft.com> wrote in message
news:75660A09-951F-4D34-88BE-BFC26E13FED7@.microsoft.com...
> Is it okay to pass a Begin...End block to Sql Server from an ASP web page?
> I
> have a situation where one of my tables contains the column names that I
> need
> to select from another table. I have always used two separate select
> statements (with two separate trips to the db) to get the values I need,
> but
> I recently found that I can accomplish the same thing by passing a
> Begin...End block like this...
> begin
> declare @.col_list varchar(8000)
> select @.col_list = coalesce(@.col_list + ', ', '') +
> approverlabel from approvers
> where formid=6 order by approverorder
> exec('select ' + @.col_list + ' from formconfigs where pid=2701')
> end
> Is there a reason why this should not be done? I realize this would be
> better if it was implemented in a stored procedure.