Dynamic select statement creation

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

General SQL Server Forums

New to SQL Server Programming

Dynamic select statement creation

Author

Topic

razeena
Yak Posting Veteran

54 Posts

Posted - 2012-02-28 : 04:47:39

Hi,
I have written a sp which passes part of the tablename as argument.Could you please tell how to modify the below sp so as to
retrieve the records for the select statement?
eg:
If I execute like this,
exec usp_select_items '_audit'

I would like to get the resulting record of the query
select * from dbo.mytable_audit
----------------------------

CREATE PROCEDURE usp_select_items
@tblName varchar(15)
AS
BEGIN

SET NOCOUNT ON;
-- Insert statements for procedure here
declare @qry varchar(200)

print 'mytable'+@tblName
set @qry = 'select * from dbo.mytable'+@tblName

print @qry

END
GO
------------------------------

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2012-02-28 : 04:59:44

Hi Razeena,

Though it is possible to do it in SQL server using dynamic queries, passing table name to stored proc and running a query in a way you have written is a complete No-No. If you use dynamic query in this way, you wont be able to get any performance benefits that SQL server offer.

Having said that, you can use EXECUTE statement to run the query dynamically:

EXEC(@qry)

Harsh Athalye
http://www.letsgeek.net/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources