I'm really having a hard time with the problem that I'm encountering, so this is my very first time to ask something in any forum. I guess this is the best sql forum that I've been to. Anyway, here is my dilemma.

Of course this will run, but how can I get the value of 1 if it exists and 0 if it doesn't.

Using "print' will just write the value, I've tried using "return" but it doesn't work and even instead of using the "print/return" I tried "set @returnval = 1 else set @returnval = 0"; which returns again an error.

I also tried this:set @returnval = exec(@sql) -- of course it wont work

Is there any workaround this problem? Thank for your time and effort. Please help.

Maybe a silly question, but why on earth does schema need to be an input parameter?

This is exactly the same as saying the table name must be an input parameter....or the columnname.. etc..

The schemaname is part of the object's qualified name, and shouldn't be needed to be handled as a parameter. If you could get rid of this requirement, then the whole thing would become much easier for you. :)

although you may no like them, seperate logins are a simple solution to this problem. give them all the same passwod and let your app connect as the company's user and implicitly access their schema-scoped objects. objects that are common for all companies would remain part of the dbo schema.

"Because the database will be handling different companies, with their own schema."That is the wrong solution to the problem. Have one database for each company and each login has access to only one database.

Be sure to setup a separate ODBC name and DNS aliases for each company and so that if you decide to move one of the database to a different server, only the IP of the DNS alias needs to change.

This is not leading down a path of success and successful implementation. At minimum you should have logins that segregate schemas. This way you can maintain objects for each company. What happens if a customer wants a stored procedure to operate differently than the one everyone else is using? Then your dynamic solution will not work and you will have to create a new procedure anyway.

The best solution is to have different databases for each company, or as stated before a default schema set for each company login. This allows for seperation of objects and increased security, and allows for greater manageability.

Tried Jeff Moden's suggestion to use "context switching" but I cannot get this to work based on three schemas and a dbo schema stored procedure, where the schema of the table is not specified. Any suggestions?