Need to Execute this ASAP! - 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)

Need to Execute this ASAP!

Author

Topic

jonasalbert20
Constraint Violating Yak Guru

300 Posts

Posted - 2004-11-05 : 01:48:44

Please see this query of mine...

declare @aidalou as varchar(800)
set @aidalou = 'Select * from tblAidaLou'

exec ExecuteTheQuery @aidalou

Is there a way to execute a string on my storedproc?
I have a purpose why i want this idea. Any help would be
much appreciated.

Want Philippines to become 1st World COuntry? Go for World War 3...

jen
Master Smack Fu Yak Hacker

4110 Posts

Posted - 2004-11-05 : 02:00:32

declare @aidalou as nvarchar(800)set @aidalou = 'Select * from tblAidaLou'

... in query pass the variable @aidalou
exec sp_executesql @aidalou

though this is a bad idea, coz you won't get the best execution plan for this, also considering security (sql injection), erroneous sql statements passed, just to mention a few.

--------------------
keeping it simple...

jonasalbert20
Constraint Violating Yak Guru

300 Posts

Posted - 2004-11-05 : 02:43:10

thnx jen for the response...

Im creating a procedure searching for a specified field. I have 20 fields which i have to query
in a common table named tblAidaLou.

example
exec SearchFromThisField 'TheFieldName', 'StringToSearch'

My code goes like this...

------------------------------------------------------------
if @TheFieldName = 'field1'
begin
select * from tblAidalou where ac = 1 and field1 = 'ffdsfsdfdsf'
end

if @TheFieldName = 'field2'
begin
select * from tblAidalou where ac = 1 and field1 = 'rgj'
end
and so on and so forth upto 20 fields of if.
------------------------------------------------------------

Now my idea instead of having so many if conditions, i
just want to concatenate the fields by specifying it on the parameters of the
stored proc and instead of so may sql statement i can put it on a single
concatenated statement.

Thus my new proc now is....

------------------------------------------------------------
create procedure sp_FindThis
@TheField as nvarchar(100),
@StringToSearch as nvarchar(500)
as
declare @aidalou as nvarchar(800)
set @aidalou = 'select * from tblAidalou where ac = 1 and ' + @TheField + ' = ' + @StringToSearch

exec sp_executesql @aidalou
go
------------------------------------------------------------

is this a poor execution plan? Pleas scold me if necessary.

Tnx...

Want Philippines to become 1st World COuntry? Go for World War 3...

jen
Master Smack Fu Yak Hacker

4110 Posts

Posted - 2004-11-05 : 02:54:23

you can "improve" by,
specifying which fields you want instead of *
just pass the where clause as one statement instead of field and texttosearch

but i'm surprised the dsql vigillantes here haven't "scolded" you for using dsql...

--------------------
keeping it simple...

jonasalbert20
Constraint Violating Yak Guru

300 Posts

Posted - 2004-11-05 : 04:09:40

Please scold me here guys.....

Want Philippines to become 1st World COuntry? Go for World War 3...

robvolk
Most Valuable Yak

15732 Posts

Posted - 2004-11-05 : 07:13:29

Here's an example(s) of a much better way to do what you're looking for:

http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=42153

No dynamic SQL necessary, and it's actually easier and cleaner to write.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources