How can make the following query dynamic so I can send the sql as a parameter? Dynamic Query?


I want to pass,
@sql= "Selecet col1, col1 from myTable orderby col1"  
Or If needed split into two sql parameter, or any which way to generalize it.  So I can pass various sqls select statements?

I want to know how to make it into dynamic query so it can work?  I am using msql 8 express edition.
thanks



CREATE PROCEDURE dbo.GetProductsPaged
            @StartRowIndex int,
            @PageSize int = 50

AS

SET NOCOUNT ON ;

WITH    PagingCTE AS (
      SELECT      col1,
            col2,
            ROW_NUMBER() OVER (ORDER BY Col1) ROW            -- Change col1 as appropriate
      FROM      myTable
      ORDER BY
            col1
             )
SELECT  col1,
        col2
FROM    PagingCTE
WHERE   Row BETWEEN @StartRowIndex AND @StartRowIndex + @PageSize - 1
goodkAsked:
Who is Participating?
 
SharathData EngineerCommented:
try this.
CREATE PROCEDURE dbo.GetProductsPaged
            @TableName varchar(100),
            @BaseQuery varchar(max),
            @SortFields varchar(max),
            @StartRowIndex int,
            @PageSize int = 50
AS 

SET NOCOUNT ON ;

declare @sql varchar(max)

set @sql = ';WITH    PagingCTE AS (
      SELECT      col1,
            col2,
            ROW_NUMBER() OVER (ORDER BY Col1) ROW        
      FROM      myTable)
SELECT  col1,
        col2
FROM    PagingCTE
WHERE   Row BETWEEN ' + convert(varchar,@StartRowIndex) + ' AND ' + convert(varchar,@StartRowIndex) + ' + ' + convert(varchar,@PageSize) + ' - 1'
exec(@sql)

Open in new window

0
 
SharathData EngineerCommented:
Why do you want a dynamic sql when the task can be done without dynamic sql?
Also remove the ORDER BY clause from CTE definition and put it outside when querying the CTE.
0
 
Ephraim WangoyaCommented:

You better break it into three parts
Select fields, tablename and orderby

CREATE PROCEDURE dbo.GetProductsPaged
            @TableName varchar(100),
            @Fields varchar(max),
            @SortFields varchar(max),
            @StartRowIndex int,
            @PageSize int = 50



Try this
CREATE PROCEDURE dbo.GetProductsPaged
            @TableName varchar(100),
            @BaseQuery varchar(max),
            @SortFields varchar(max),
            @StartRowIndex int,
            @PageSize int = 50
AS 

SET NOCOUNT ON ;

declare @sql varchar(max)

set @sql = 'WITH    PagingCTE AS ( SELECT ' + @Fields  +
            ' ROW_NUMBER() OVER (ORDER BY ' + @SortFields + ') ROW ' +   
            ' FROM  ' + @TableName +
            ' ORDER BY ' + @SortFields + ')'

set @sql = @sql + 'SELECT ' + @Fields  +
                  ' FROM PagingCTE' +
                  ' WHERE Row BETWEEN @StartRowIndex AND @StartRowIndex + @PageSize - 1'
Exec(@sql)

Open in new window

0
Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

 
Anthony PerkinsCommented:
>>Why do you want a dynamic sql when the task can be done without dynamic sql? <<
I did try.  See here:
http://www.experts-exchange.com/Programming/Languages/.NET/ASP.NET/Q_26865616.html
As you can see I was not successful.
0
 
SharathData EngineerCommented:
I don't know the background of this question. The asker wants to pass the SQL as parameter to SP. If that is the case, he/she need dynamic sql. But my question is if the sql to be queried is static, what is the necessity of  dynamic sql?
0
 
Ephraim WangoyaCommented:

I suppose the author intends to use the SP for different tables.
Thats the only reason I can come up with
0
 
goodkAuthor Commented:
@Sharath_123:  I see your point.

So should I just execute the sql in my program (asp.net using c#) as my sql is different depending on the user and I can easily modify the string?

sql =
"WITH    PagingCTE AS (
      SELECT      col1,
            col2,
            ROW_NUMBER() OVER (ORDER BY Col1) ROW        
      FROM      myTable
      ORDER BY
            col1
             )
SELECT  col1,
        col2
FROM    PagingCTE
WHERE   Row BETWEEN @StartRowIndex AND @StartRowIndex + @PageSize - 1";

Is the above syntex ok? for sql execution? thanks
0
 
goodkAuthor Commented:
I got confused when some experts said that I need a dynamic sql to generalized the solution.  Thanks all, now I understand how this works.

This is pretty straight forward to generalized, thanks again.

WITH    PagingCTE AS (
      SELECT      InCredit,
            PayedBy,
            ROW_NUMBER() OVER (ORDER BY PayedBy) ROW        
      FROM      AceData)
SELECT  InCredit,
        PayedBy
FROM    PagingCTE
WHERE   Row BETWEEN 2900 AND 3000
0
 
goodkAuthor Commented:
thanks
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.