Stored Procedures - 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 2005 Forums

Transact-SQL (2005)

Stored Procedures

Author

Topic

BeamBrain
Starting Member

10 Posts

Posted - 2009-05-19 : 06:48:35

This is probably a daft question, but is it possible to pass an entire select statement into a stored procedure.

reason i ask is beacuse i have a number of queries in dynamic sql which i want to convert to sp's. However by definition dynamic sql can be

SELECT *
from t_table

OR

SELECT *
from t_table
where ...
and ....
and ...

which means i have to define multiple sp's ??

so can i do

exec sp_name ('SELECT * FROM t_table WHERE .....')

robvolk
Most Valuable Yak

15732 Posts

Posted - 2009-05-19 : 07:49:09

quote:
This is probably a daft question, but is it possible to pass an entire select statement into a stored procedure

Not a daft question, but definitely a daft usage. You'd completely negate the purpose of a stored procedure with this approach.

Can you explain in more detail what you're trying to accomplish? I imagine you have a dynamic WHERE clause, varying criteria or some such.

BeamBrain
Starting Member

10 Posts

Posted - 2009-05-19 : 08:09:04

Yes exactly, i have a dynamic where clause. So depending on user form input i could have

select *
from table

OR

select *
from table
where dep = '06'

or

select *
from table
where dep = '06'
and type = 1

or

select *
from table
where type = 1

etc.etc. there are many many variations....

i discovered i could do this...

CREATE PROCEDURE getRecords ( @DymanicSQL varchar(100) ) AS

DECLARE @SQLStatement varchar(2000)
SELECT @SQLStatement = @DymanicSQL
EXEC(@SQLStatement)

exec getRecords "SELECT whatever i want"

but from what you are syaing this is pointless ??

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2009-05-19 : 08:49:58

Many approaches are listed here
http://www.sommarskog.se/dyn-search-2005.html

Madhivanan

Failing to plan is Planning to fail

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-05-19 : 11:06:46

cant you just use conditional logic in where like


select *
from table
where (dep = '06' or condition)
and (type = 1 or condition)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources