Dynamic SQL with XML schema?

  • Thread starter Thread starter Dot net work
  • Start date Start date
D

Dot net work

I am using dynamic SQL inside my stored procedure because I need to
use a variable with the TOP keyword.

When I drag this stored proc on to my XML schema file inside VS.NET,
the schema is not generated, which I think is understandable.

Is there something that I can do to have a dynamic SQL based stored
procedure, and somehow use an xml schema, because I really like the
fact that with an xml schema variable you can refer to the datatable
column names at design time, such as:

MyXMLSchema.MyRow.MyCol

Thank you,
Regards, dnw.
 
With SQL Server, you can avoid the use of the TOP keyword by using ROWCOUNT:

Declare @MaxRows int -- could be declared as a sproc paramater also
Set @MaxRows = 2
SET ROWCOUNT @maxrows
Select * from pubs..authors Order by au_id desc -- retrieves only two rows
SET ROWCOUNT 0 -- resets rowcount back to retrieving all rows
 
Thanks a lot, that's a good idea.
-dnw.

Jim Hughes said:
With SQL Server, you can avoid the use of the TOP keyword by using ROWCOUNT:

Declare @MaxRows int -- could be declared as a sproc paramater also
Set @MaxRows = 2
SET ROWCOUNT @maxrows
Select * from pubs..authors Order by au_id desc -- retrieves only two rows
SET ROWCOUNT 0 -- resets rowcount back to retrieving all rows
Click to expand...
 
Back
Top