Recursive and Dynamic Stored Proc

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Recursive and Dynamic Stored Proc

Author

Topic

ramsesiitr
Starting Member

22 Posts

Posted - 2008-08-28 : 08:50:44

Hi all,
I have a problem with sql server 2005. I have a recursive stored procedure and it works fine. I want to make it dynamic (like, with a parameter and adding the conditions to the parameter). But I cant make it work. I really need help.

Here is the code I tried


declare @query nvarchar(500)
	select @query =' SELECT ''RowNumber'' = ROW_NUMBER() OVER(ORDER BY id DESC), 
               id ,name , surname, cprNumber,type
        FROM dbo.vi_person'
        select @query=@query+' where id=10'


;WITH getDynamic As
    (
         exec (@query)
    )

Thanks for help in advance...

bjoerns
Posting Yak Master

154 Posts

Posted - 2008-08-28 : 08:53:55

What's the error message? Is it just the missing blank between person and where?

ramsesiitr
Starting Member

22 Posts

Posted - 2008-08-28 : 09:12:19

No, there is a blank next to the where condition. I forgot to write it. The error message is:

Msg 156, Level 15, State 1, Procedure stp_dinamikSearch, Line 27
Incorrect syntax near the keyword 'exec'.
Msg 102, Level 15, State 1, Procedure stp_dinamikSearch, Line 28
Incorrect syntax near ')'.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-08-28 : 09:49:42

why dont you try putting CTE definition also inside dynamic query string?

declare @query nvarchar(500)
	select @query =';WITH getDynamic As
    ( SELECT ''RowNumber'' = ROW_NUMBER() OVER(ORDER BY id DESC), 
               id ,name , surname, cprNumber,type
        FROM dbo.vi_person'
        select @query=@query+' where id=10 )'



         exec (@query)

ramsesiitr
Starting Member

22 Posts

Posted - 2008-08-28 : 09:59:27

Thanks for the answer,

quote:

why dont you try putting CTE definition also inside dynamic query string?

declare @query nvarchar(500)
select @query =';WITH getDynamic As
( SELECT ''RowNumber'' = ROW_NUMBER() OVER(ORDER BY id DESC),
id ,name , surname, cprNumber,type
FROM dbo.vi_person'
select @query=@query+' where id=10 )'

exec (@query)

If the code above is what you mean by CTE, I already tried it aswell. It doesn't recognize the entity getDynamic.

The error messages for the code above are;

Incorrect syntax near 'id'.
Invalid object name 'getDynamic'.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-08-28 : 10:09:53

can i ask the need of CTE getDynamic here? is this the full query you're using?

ramsesiitr
Starting Member

22 Posts

Posted - 2008-08-28 : 10:17:28

No it is not, this is the part of the query that I think the error stays there.

the full query is;


ALTER PROCEDURE [PERSONEL].[stp_dynamicSearch] 
	-- Add the parameters for the stored procedure here
(	@PageNum int,
        @PageSize int,
        @name nvarchar(50)
)
AS
BEGIN
	declare @query nvarchar(500)
	select @query =' SELECT ''RowNumber'' = ROW_NUMBER() OVER(ORDER BY id DESC), 
               id ,name , surname, cprNumber,type
        FROM dbo.vi_person'
        If(@name is null)
         Begin
           select @query=@query+' where id=10'
         End
        Else
         Begin
           select @query=@query+' where name=@name'
         End

;WITH getDynamic As
    (
         exec (@query)
    )

    -- Query result

    SELECT * 
    FROM getDynamic
    WHERE RowNumber BETWEEN (@PageNum - 1) * @PageSize + 1 AND @PageNum * @PageSize            
    ORDER BY 'id' DESC

   
END

This is the first code that I posted here and the first errors I wrote are for that code. I am using this code in order to get records at a specific area. Like the first 10 records, or the records with id are between 120 and 140. I have a gridview with dynamic paging.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-08-28 : 10:35:33

why you need dynamic sql at all? this posted code can be written as below:-

ALTER PROCEDURE [PERSONEL].[stp_dynamicSearch] 
	-- Add the parameters for the stored procedure here
(	@PageNum int,
        @PageSize int,
        @name nvarchar(50)
)
AS
BEGIN
SELECT * 
FROM
(
  SELECT ROW_NUMBER() OVER(ORDER BY id DESC) AS RowNo, 
  id ,name , surname, cprNumber,type
  FROM dbo.vi_person
  where id=10
  AND (name=@name OR @name IS NULL)
)t
WHERE t.RowNo BETWEEN (@PageNum - 1) * @PageSize + 1 AND @PageNum * @PageSize            
ORDER BY t.id DESC

   
END

ramsesiitr
Starting Member

22 Posts

Posted - 2008-08-28 : 10:58:00

Thanks mate,
I needed to fill my gridview dynamically. So, I found that code which I asked about. But the code you wrote just works fine. Beside I have couple of other parameters I didnt posted here. I have to add them aswell. I can assign this code to a parameter then I can execute parameter.

That I was looking for.

Thanks again

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-08-28 : 11:00:17

quote:
Originally posted by ramsesiitr

Thanks mate,
I needed to fill my gridview dynamically. So, I found that code which I asked about. But the code you wrote just works fine. Beside I have couple of other parameters I didnt posted here. I have to add them aswell. I can assign this code to a parameter then I can execute parameter.

That I was looking for.

Thanks again

No worries
Glad that i could help you out

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources