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?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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
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
10 Tips to Protect Your Business from Ransomware

Did you know that ransomware is the most widespread, destructive malware in the world today? It accounts for 39% of all security breaches, with ransomware gangsters projected to make $11.5B in profits from online extortion by 2019.

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
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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
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
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Query Syntax

From novice to tech pro — start learning today.