execute sql command which is stored in table from procedure

execute sql command which is stored in table from procedure

Post by nicholas.gadac » Tue, 09 Sep 2003 06:21:12



i have a simple table which has a field called sql_command.

i would like to have a query which runs all of the
sql_commands, i would also like to do a simple search and
replace on the contents of the field.

example:

sql_command
---------------
select top 400 * from property

i would like to execute the string as
select * from property.

i cannot get the syntax correct for this, is it even
possible? thanks in advance for any help.

nicholas.gadacz

 
 
 

execute sql command which is stored in table from procedure

Post by Uri Diman » Tue, 09 Sep 2003 07:42:40


nicholas
1)
exec ('select * from table')
2)
execute sp_executesql




Quote:

> i have a simple table which has a field called sql_command.

> i would like to have a query which runs all of the
> sql_commands, i would also like to do a simple search and
> replace on the contents of the field.

> example:

> sql_command
> ---------------
> select top 400 * from property

> i would like to execute the string as
> select * from property.

> i cannot get the syntax correct for this, is it even
> possible? thanks in advance for any help.

> nicholas.gadacz


 
 
 

execute sql command which is stored in table from procedure

Post by Vishna » Tue, 09 Sep 2003 06:46:23


Nicholas

what you can do is store the query in a variable and
execute the same .




Please refer BOL for articles on sp_executesql and
execute command from BOL

http://www.sqlteam.com/item.asp?ItemID=4599

Vish

Quote:>-----Original Message-----

>i have a simple table which has a field called
sql_command.

>i would like to have a query which runs all of the
>sql_commands, i would also like to do a simple search
and
>replace on the contents of the field.

>example:

>sql_command
>---------------
>select top 400 * from property

>i would like to execute the string as
>select * from property.

>i cannot get the syntax correct for this, is it even
>possible? thanks in advance for any help.

>nicholas.gadacz
>.

 
 
 

execute sql command which is stored in table from procedure

Post by Steve Kas » Tue, 09 Sep 2003 06:59:19


Nicholas,

  If this is not production code, you could consider the undocumented
procedure xp_execresultset.  Here's an example:

use tempdb
go

create table sqlCommands (
  i   int identity(1,1) primary key,
  sql nvarchar(4000)
)
go

insert into sqlCommands values (N'Select top 2 OrderID from Orders order
by CustomerID desc')
insert into sqlCommands values (N'Select top 4 CustomerID from Customers
order by CompanyName')
go

exec master..xp_execresultset N'select sql from tempdb..sqlCommands',
N'Northwind'
go

drop table sqlCommands

-- Steve Kass
-- Drew University
-- Ref: ACA15359-5760-46BB-A861-89FBA129D785


>i have a simple table which has a field called sql_command.

>i would like to have a query which runs all of the
>sql_commands, i would also like to do a simple search and
>replace on the contents of the field.

>example:

>sql_command
>---------------
>select top 400 * from property

>i would like to execute the string as
>select * from property.

>i cannot get the syntax correct for this, is it even
>possible? thanks in advance for any help.

>nicholas.gadacz

 
 
 

1. I am attempting to execute a stored procedure as the sql query for a data

I am attempting to execute a stored procedure as
the sql query for a data transformation from sql
into a text file. The stored procedure I am
calling uses temp tables (#temp1, #temp2, etc.)
to gather results from various tables and
queries. When I try to execute this sp, I get
'Error Source: Microsoft OLE DB Provider for SQL
Server Error Description: Invalid Object
name "#temp1.

Has anyone ever faced this problem before, if so
please do advise.
I'd really appreciate your help.

Thanks in advance and regards
Xega

Sent via Deja.com http://www.deja.com/
Before you buy.

2. ORA-12545 - Error Not Consistent Across Network

3. SQL server ADO.command.execute stored procedure does not return a records

4. IRC Channel NOW open!

5. Command.Execute fails to return Recordset from SQL stored procedure

6. Upsizing Access 97 Tables

7. Stored Procedure execute twice through command object (2 spid in SQL)

8. VFP6 Class Browser Addins

9. accessing stored procedure return value using ADO (2.0) command execute method

10. error while executing store procedure command

11. Executing a Stored Procedure from a command line

12. ? Executing a Stored Procedure With Output Parameters and A Resul tset using a Command Object

13. calling stored procedures that execute many commands before the final select