using variables as tablenames?

Question

using variables as tablenames?

Viewing 6 posts - 16 through 20 (of 20 total)

You must be logged in to reply to this topic. Login to reply

noeld SSC Guru Points: 96590 More actions · Answer 1

Ganesh,

In your case it sounds like a separate sp for each case and one wrapper sp would do the trick. On the Wrapper one make sure to put them in the order you would expect the maximum usage like:

Create proc usp1 (@mode int, @value int)

begin

end

Create proc usp2 (@mode int, @value int)

begin

end

Create proc usp3 (@mode int, @value int)

begin

end

...

Create proc usp_Wrapper (@mode int, @value int)

begin

IF @Mode = 0

exec usp0 @Mode

ELSE IF @Mode = 1

exec usp1 @Mode

ELSE IF @Mode = 2

exec usp2 @Mode

ELSE IF @Mode = 3

exec usp3 @Mode

end

remeber this is just an example not a working code

HTH

* Noel

babuganesh2000 Hall of Fame Points: 3123 More actions · Answer 2

Thank you.

Could you please explain me, how best the splitting of this procedure would add value in the performance.

My concerns:

Using dynamic sql:

1. Unnecessary maintenance of 5 more stored procedure is avoided

Like wise, splitting the stored procedure. What's a deal ?

Thanks,

Ganesh

Andy Warren SSC Guru Points: 119902 More actions · Answer 3

I think questions like these are when we have our best chance as peers and as a community to really make a difference. In my opinion it's 'easier' to answer a question where the answer is clearly 'do this', both in terms of solving the problem and in best (accepted) practices. Questions where the answer is not so clear - time for us all to think and grow.

Commenting in general rather on this particular question, I think it's a familiar theme to see users at less than master level in a particular language come up with some less than perfect techniques to solve a problem. Sometimes it's that they don't know that there is a 'better' way, maybe it's not even intuitive how you'd search help for that better way. In other cases the user is stuck with an ugly situation that would be costly or impossible to fix.

I think we want to make sure the reader asking the question knows/understands that what they are asking for might be less than optimum, even horrible as it were. If they don't - let's educate! If the situation can't be changed (is that really true?), then what's the best way out of a bad situation?

As pertains to this question in particular, dynamic sql is a tool available to us and it's worth mentioning injection attacks every time we see it about to be used, but lets extend that conversation with how to defend against it, not just throw out the technique altogether.

Hope everyone is having a great holiday!

Andy
SQLAndy - My Blog!
Connect with me on LinkedIn
Follow me on Twitter

noeld SSC Guru Points: 96590 More actions · Answer 4

Ganesh,

I will be very easy to translate the code to dynamic SQL but the apparent ease of manintenace can also be interpreted as too tightly coupled. yes it is true that you will maintain 5 sp instead of one but have you thought about what if one of them needs to be changed and you don't have to touch the other (People call it modularization )

There is some value also in that the procedures will be compiled using a minimum of overhead and optimized for the function the need to perform.

Once again, if you think you can encapsulate functionality that could be reusable maybe is a good idea to put that in a separated sp. The thing with dynamic sql that I have always found out is that if you have to use it , your best bet is probably at the client side and not at server side.

HTH

This is just my opinion, there could be others

* Noel

babuganesh2000 Hall of Fame Points: 3123 More actions · Answer 5

Noeld,

Since you are online now, Iam posting this question to you. Thanks for the info. I really get a good hold on the explaination by Celko. I appreciate it.

Using a query, I need to list the permission of a user using the system tables sysusers,syspermissions,sysxlogins. The output should be of this sort.

User database read write execute

user1 db1 y n n

Thanks,

Ganesh

noeld SSC Guru Points: 96590 More actions · Answer 6

Ganesh,

your output is missing objectName because in SQL permissions are assigned to objects within the database, not to the database itself. At the Database level you allow or deny access only.

To Answer your question, there are already posted several scripts on this site that you can benefit from. Have a look a this one from Frank Kalis that seem to have good feedback from the community. There are others but at least this will get you started.

HTH

* Noel