Should I put Dynamic SQL in a SP

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

SQL Server Development (2000)

Should I put Dynamic SQL in a SP

Author

Topic

motokevin
Starting Member

36 Posts

Posted - 2002-12-20 : 12:55:06

let's use the following example:

Create Procedure sp_GetTopRows

@NumRows Integer

As

Declare @GetTopRows VarChar(500)
Set @GetTopRows = 'Select Top ' + @NumRows + ' From MyTable'
Exec(@GetTopRows)

Go

(not sure if my syntax is correct)

Since the query in the stored procedure is not parsed until each time it runs, is there any advantage/performance gain by making this a stored procedure, rather than just running the query on it's own as a command. I am calling the procedure/query from ASP.

macka
Posting Yak Master

162 Posts

Posted - 2002-12-20 : 13:06:27

I personally prefer to use the SET ROWCOUNT like this:


Create Procedure sp_GetTopRows 

@NumRows Integer 

As 

SET ROWCOUNT  @NumRows 

SELECT * 
FROM MyTable

SET ROWCOUNT  0

Go

Take a look in Books Online (BOL) for more information.

macka.

There are only 10 types of people in the world - Those who understand binary, and those who don't.

motokevin
Starting Member

36 Posts

Posted - 2002-12-20 : 13:23:36

what i am actually going to do is something like this:

'select top ' + @outertop + ' * from mytable where id in (select top ' + @innertop + ' id from mytable where ...)'

so unless there is a way to set a separate rowcount for both my inner and outer query, i think i am stuck doing dynamic SQL.

So my main question is, does putting my dynamic SQL into a SP improve the performance at all, or is it going to be the same as if run the command without a procedure?

mohdowais
Sheikh of Yak Knowledge

1456 Posts

Posted - 2002-12-20 : 13:57:17

quote:

'select top ' + @outertop + ' * from mytable where id in (select top ' + @innertop + ' id from mytable where ...)'

Yup looks like you are stuck with dynamic sql. I dont think you will gain any real advantage over using a command (instead of calling the SP), but I think this should add a small boost:

sp_executesql(@GetTopRows) instead of EXEC(@GetTopRows)

OS

ValterBorges
Master Smack Fu Yak Hacker

1429 Posts

Posted - 2002-12-20 : 17:28:20

You could also try to see if it's faster.

SET ROWCOUNT @NumRows1

INSERT INTO @TableVariableA
SELECT * FROM
TableA
WHERE
yourcriteria

SET ROWCOUNT @NumRows2

SELECT *
FROM TableA A INNER JOIN @TableVariableA B ON A.ID = B.ID

SET ROWCOUNT 0

Or you can also write a udf that takes in the criteria and number of rows and returns a table.
Next use the same idea as above to join what the udf returns to itself.

Edited by - ValterBorges on 12/20/2002 18:09:01

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources