Dynamic Stored Procedure - SQL Server Forums

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

Transact-SQL (2000)

Dynamic Stored Procedure

Author

Topic

nhamdan
Starting Member

19 Posts

Posted - 2007-05-09 : 05:02:54

Hi All,

As known its recommended to use stored procedures when executing on database for perfermance issue. I am thinking to create 4 stored procedures on my database (spSelectQuery, spInsertQuery, spUpdateQuery, spDeleteQuery)

that accept any query and execute it and return the result, rather than having a number of stored procedures for all tables?

create PROCEDURE spSelectQuery
(
@select_query nvarchar(500)
)
as
begin

exec sp_executesql @select_query, N'@col_val varchar(50) out', @col_val out

end

Is this a good approach design, or its bad???

Thanks all

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2007-05-09 : 05:14:12

No, that's not correct approach.

If you generalize your procedure by passing query and using dynamic sql to execute it, you are missing the whole point of having stored procs.

Basically, SPs perform well over ad-hoc queries is because their execution plans are cached and can be reused by optimizer later. But in case of dynamic sql, this is not the case.

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

mwjdavidson
Aged Yak Warrior

735 Posts

Posted - 2007-05-09 : 05:21:26

Not to mention the security implications!

Mark

nhamdan
Starting Member

19 Posts

Posted - 2007-05-09 : 05:33:15

Hi harsh_athalye,

So its better if i create a 4 stored procedures for each table, for example table name customer

spCustomersSelect, spCustumersUpdate, spCustumersInsert, spCustumersDelete

But for example if my select sp accepts 5 parameters as columns? so i have to build the query that selects from table as recieved parameters? could this considered as Dynamic and will effect index??

or i have to create also sp for each select creteria?

Thanks

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2007-05-09 : 05:54:30

Can you post the spCustomersSelect procedure code along with the parameters?

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

nhamdan
Starting Member

19 Posts

Posted - 2007-05-09 : 06:06:49

I havent started writing the stored procedures, but still deciding what is best way to write them with minimum code & best performance.

But this is example of what i mean:

create PROCEDURE spSelectCustomer
(
@cust_id bigint,
@cust_name char(50)
)
as
begin
declare @strSql varchar(500);

select @strsql = N'select * from custumers'
if @cust_id is not null
begin
set @strSql = @strSql + " where cust_id"=@cust_id
end
if @cust_name is not null
begin
set @strSql = @strSql + " where cust_name"=@cust_name
end
exec sp_executesql @strsql, N'@col_val varchar(50) out', @col_val out

end

GO

------------------------------------------------------------------

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2007-05-09 : 06:08:36

This is leading to SQL Injection
Dont pass object name or query as parameters
www.sommarskog.se/dynamic_sql.html for more info on Dynamic sql

Madhivanan

Failing to plan is Planning to fail

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2007-05-09 : 06:10:58

No need for writing separate statements for each parameter, nor do you need dynamic sql. Here is an example of how you can write same proc in more simpler way:

create PROCEDURE dbo.spSelectCustomer
(
	@cust_id bigint = NULL,
	@cust_name char(50) = NULL
)
as

select * from custumers
Where (@cust_id is NULL or cust_id = @cust_id) and (@cust_name is NULL or cust_name = @cust_name)
GO

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

nhamdan
Starting Member

19 Posts

Posted - 2007-05-09 : 08:37:50

Mr. Harsh,

Your way seems much practical and good, this is for select procedure, insert straight forward, delete i will use same query you gave me. But incase of Update, suppose i have procedure for update and accepets the 3 parameter @cust_id, @cust_name, @cust_age, and sometimes i want to update @cust_name alone, or cust_name along with age ... etc. how can i do that without using the dynamic queries and with your same approach in last post.

Many thanks for your help.

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2007-05-09 : 08:43:38

You can make use of COALESCE() there:

UPDATE Customers
SET 
	cust_name = Coalesce(@cust_name, cust_name),
	age = Coalesce(@age, age)
Where
	cust_id = @cust_id

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

nhamdan
Starting Member

19 Posts

Posted - 2007-05-09 : 09:10:30

Mann ,, thanks alot i have learned too many things from you ,, its all new to me :)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources