how to write this dynamically - 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

SQL Server Development (2000)

how to write this dynamically

Author

Topic

PeterG
Posting Yak Master

156 Posts

Posted - 2002-11-13 : 16:03:00

I have the following lines in my SP:
if exists (select 1 from Element1_Codes where txtCostCenter=@code or txtDescription=@desc)
return 2
else
return 3

My problem is that my table has to be dynamically referenced, as the table number could change. My table ranges from 1 to 12.

My initial thought is to write a dynamic query(@sql) and call exec(@sql), but how?

Thanks for any help.

Peter

M.E.
Aged Yak Warrior

539 Posts

Posted - 2002-11-13 : 16:20:13

select @sql = 'select 1 from ' + @tablename + ' where txtcostcenter = ' + @code ' or txtdescription = ' +@desc
exec(@sql)

-----------------------
SQL isn't just a hobby, It's an addiction

PeterG
Posting Yak Master

156 Posts

Posted - 2002-11-13 : 16:29:08

where do i place my if exists clause?

nr
SQLTeam MVY

12543 Posts

Posted - 2002-11-14 : 05:33:32

something like

declare @sql nvarchar(1000)
select @sql = 'if exists (select * from Element' + convert(varchar(10), @tblnum) + '_Codes where txtCostCenter=''' + @code + ''' or txtDescription=''' + @desc + ''') select @i = 1 else select @i = 0'
exec sp_executesql @sql, N'@i int out', @i out

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

sanjay
Starting Member

3 Posts

Posted - 2002-11-19 : 01:06:42

Hello nr,
I have seen and used "sp_executesql" for dyanamic queries inside another stored procedure.
My confusion is that when we use sp_executesql do we get any advantages of performance of stored proc?
Means as the query is generated every time and all we do is to pass String to sp_executesql how does it parse , compile and makes the execution plan for it ? or is it equivalent of executing the SQL command from my VB program.

Thanks
Sanjay

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources