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