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

9 Dec 2005 6:48 PM

creed1

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.

9 Dec 2005 6:54 PM

Andrew J. Kelly

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

Show quote

"creed1" <cre***@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.