exec(select...), how supress the output?

Hi
I have a dynamically constructed sql query that I want to execute, e.g.
exec('select * from ' + @.tablename)

(1) Can I suppress the output somehow if this returns no values?
(2) Can I use the result of this query in another query somehow? e.g.
select
(3) Can I control the size of the columns in the output somehow

Thanks
F(foldface@.yahoo.co.uk) writes:
> I have a dynamically constructed sql query that I want to execute, e.g.
> exec('select * from ' + @.tablename)
> (1) Can I suppress the output somehow if this returns no values?

EXEC ('IF EXISTS (SELECT * FROM ' + @.tablename ' + ') SELECT * FROM ' +
@.tablename)

> (2) Can I use the result of this query in another query somehow? e.g.
> select

INSERT #tmp (...)
EXEC('...')

> (3) Can I control the size of the columns in the output somehow

This question is unclear. Output columns from SQL Server does not
really have any size, but that is up to the client tool you use.

But if you are using Query Analyzer, and want some nice output there,
you can use convert(varchar(n), ...) where n is the size of your choice.

--
Erland Sommarskog, SQL Server MVP, sommar@.algonet.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||> > (1) Can I suppress the output somehow if this returns no values?
> EXEC ('IF EXISTS (SELECT * FROM ' + @.tablename ' + ') SELECT * FROM ' +
> @.tablename)

worked this out in the end but as I'm using google for news access from
work I couldn't reply to my own mail. Thanks anyway

> > (2) Can I use the result of this query in another query somehow? e.g.
> > select
> INSERT #tmp (...)
> EXEC('...')

sorry, don't understand this? Can you elaborate?

> > (3) Can I control the size of the columns in the output somehow
> But if you are using Query Analyzer, and want some nice output there,
> you can use convert(varchar(n), ...) where n is the size of your choice.

create table #TempTable (id int)
insert #TempTable Values (convert(varchar(50), 2))
select * from #TempTable

I know this is meant to convert values but what exactly should I be seeing
here? I am talking about Query Analyser|||(foldface@.yahoo.co.uk) writes:
>> > (2) Can I use the result of this query in another query somehow? e.g.
>> > select
>>
>> INSERT #tmp (...)
>> EXEC('...')
> sorry, don't understand this? Can you elaborate?

You can save the output from an EXEC() statement in a temp table, and
then use the temp table in the next query.

>> > (3) Can I control the size of the columns in the output somehow
>>
>> But if you are using Query Analyzer, and want some nice output there,
>> you can use convert(varchar(n), ...) where n is the size of your choice.
> create table #TempTable (id int)
> insert #TempTable Values (convert(varchar(50), 2))
> select * from #TempTable
> I know this is meant to convert values but what exactly should I be seeing
> here? I am talking about Query Analyser

I don't know exactly what you are trying to achieve, and you might be
better off if you explained more about your business requirements.

But the column width in QA depends on two things: the column name and the
data type. If you want control over the column width, all output columns
must be varchar. You cannot control the width of a float or an int column.

--
Erland Sommarskog, SQL Server MVP, sommar@.algonet.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.aspsql

exec statement

hi guys

maybe an easy one for you

in stored procedure I create follving select

@.cmd = 'select ' + @.column_name + 'from ticket_dump_datawarehouse '
execute (@.cmd)

problem is thant I want to gave return value from this select

something like
set @.return = execute(@.cmd)

but I recieve error

Incorrect syntax near the keyword 'execute'

Can I do that some other way?create temporary table out the and insert the result into that table
create table #t(colvalue <datatype>)

@.cmd = 'insert into #t (colvalue)
select ' + @.column_name + 'from ticket_dump_datawarehouse '
execute (@.cmd)

select * From #t

drop table #t

exec statement

hi guys

maybe an easy one for you

in stored procedure I create follving select

@.cmd = 'select ' + @.column_name + 'from ticket_dump_datawarehouse '
execute (@.cmd)

problem is thant I want to gave return value from this select

something like
set @.return = execute(@.cmd)

but I recieve error

Incorrect syntax near the keyword 'execute'

Can I do that some other way?return
which value?
Originally posted by mikosan
hi guys

maybe an easy one for you

in stored procedure I create follving select

@.cmd = 'select ' + @.column_name + 'from ticket_dump_datawarehouse '
execute (@.cmd)

problem is thant I want to gave return value from this select

something like
set @.return = execute(@.cmd)

but I recieve error

Incorrect syntax near the keyword 'execute'

Can I do that some other way?|||this is whole select.

sorry I forgot to finish it

@.cmd = 'select ' + @.column_name + 'from ticket_dump_datawarehouse where id = 10'

execute (@.cmd)

value from select statement = value in column @.column_name|||USE Northwind
GO

DECLARE @.sql varchar(8000), @.OrderId int

SELECT @.SQL = 'SELECT TOP 1 OrderId INTO myTable99 FROM Orders'

EXEC(@.SQL)

SELECT @.OrderId = OrderId FROM myTable99

SELECT @.OrderId
GO

DROP TABLE myTable99
GO

exec statement

Hi all

I used the

create procedure proc1

as

begin try

exec (

begin try

'select * from '+@.tablename1

if @.@.rowcount=0

raiseerror("hello",16,1)

'select * from '+@.tablename2

end try

begin catch

select error_message()

end catch)

end try

begin catch

end catch

in my stored procedure

beacause the name of tables not specified for me now

my questions:

1. Is there beter way than using exec (.... for running variable table name?

2. if i use the try catch in the exe statement with error_procedurename() i can not

get the name of the procedure (name1) and if any error happen in the exec statement the inner catch does not throw the error to the outer catch ?

and wheter i can get the error information of the inner try in the outer catch ?

3.if i want to raise error to catch condition with raiserror which severity and state is the best for my raiserror I know the severity should be between 11 and 19 but i dont know which number ?

thank you

The syntax of the EXEC statement is incorrect. To execute dynamic SQL, you need to pass a string or expression concatenating multiple strings. So if you do that it will work fine and you need just the try...catch at the SP level. However, why are you writing a generic SP like this that will select from any table? This has lot of problems in that the result set of the SP will change depending on the table and you need to account for that in the client-side. Additionally, you will be subjected to SQL injection attacks if you form the statement incorrectly and you need to grant SELECT permission on all the tables that you will potentially use for this to work for all users. Your raiserror statement is fine. You can trap it in the client.|||? What error are you trying to catch? The table not existing? If so, why not check first (SELECT * FROM sys.tables WHERE name=...)? That would probably make your error handling a bit simpler. There is no "better" way to handle having a table name passed in, but the question you might want to ask yourself is why you need to pass in a table name at all? If your db truly has so many tables that can be handled exactly the same way that you need to be able to pass in the name, perhaps you should generalize them into a single table and thereby eliminate the dynamic SQL altogether. -- Adam MachanicPro SQL Server 2005, available nowhttp://www..apress.com/book/bookDisplay.html?bID=457-- <Asal@.discussions.microsoft..com> wrote in message news:ed6e1c30-e9d8-4145-9634-4490b9e66a30@.discussions.microsoft.com... Hi all I used the create procedure proc1 as begin try exec ( begin try 'select * from '+@.tablename1 if @.@.rowcount=0 raiseerror("hello",16,1) 'select * from '+@.tablename2 end try begin catch select error_message() end catch) end try begin catch end catch in my stored procedure beacause the name of tables not specified for me now my questions: 1. Is there beter way than using exec (.... for running variable table name? 2. if i use the try catch in the exe statement with error_procedurename() i can not get the name of the procedure (name1) and if any error happen in the exec statement the inner catch does not throw the error to the outer catch ? and wheter i can get the error information of the inner try in the outer catch ? 3.if i want to raise error to catch condition with raiserror which severity and state is the best for my raiserror I know the severity should be between 11 and 19 but i dont know which number ? thank you|||

thanks for your replying

but what i want to do is not exactly like this code ,it is only like that structure,I want to execute the exec dynamic statement for 12 table that the statements of inside the exe are same for all 12 tables but the name of the tables are defined at run time out of the exec (in proc1) and the statements in the exec are not only select statement they are multy sql statements that get their variables from proc1.

thanks