execute multi level dynamic sproc

Question

Post reply

execute multi level dynamic sproc

curious_sqldba

SSC-Dedicated

Points: 36494
More actions
March 3, 2016 at 9:28 am

#308964

Basically when i execute below batch sql it will generate another set of sql statement. How do i actually also execute the end generated statement? Basically how do i execute ( exec @sql), if that makes sense
declare @sql NVARCHAR(MAX)
DECLARE @templogin NVARCHAR(MAX)
Set @templogin='domain\user1'
SELECT @sql = 'exec [DBA].[dbo].[CopyAllLoginsUsers] @loginholder= ' + @templogin
exec @sql

Viewing 5 posts - 1 through 4 (of 4 total)

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

Luis Cazares SSC Guru Points: 183706 More actions · Answer 1

Why are you doing that when you can do this

DECLARE @templogin NVARCHAR(128);

Set @templogin='domain\user1';

exec [DBA].[dbo].[CopyAllLoginsUsers] @loginholder= @templogin;

Especially when using NVARCHAR(MAX), you're exposed to SQL injection.

Use parametrized queries whenever possible. For dynamic SQL, it's preferable to use sp_executesql.

Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

How to post data/code on a forum to get the best help: Option 1 / Option 2

curious_sqldba SSC-Dedicated Points: 36494 More actions · Answer 2

Luis Cazares (3/3/2016)
Why are you doing that when you can do this
DECLARE @templogin NVARCHAR(128);
Set @templogin='domain\user1';
exec [DBA].[dbo].[CopyAllLoginsUsers] @loginholder= @templogin;
Especially when using NVARCHAR(MAX), you're exposed to SQL injection.
Use parametrized queries whenever possible. For dynamic SQL, it's preferable to use sp_executesql.

Even if i do that i still need to be able to execute output of other execute

Luis Cazares SSC Guru Points: 183706 More actions · Answer 3

You can't execute a result set, you need to assign the text into a variable. Please, be very careful with SQL injection as this could be a recipe for disaster if not done correctly.

To store a full result set in a variable, use the technique explained in here: http://www.sqlservercentral.com/articles/comma+separated+list/71700/

You could use it inside your procedure or you could use an output parameter to execute afterwards.

I'm not posting any code because I'm afraid of the consequences.

Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

How to post data/code on a forum to get the best help: Option 1 / Option 2

curious_sqldba SSC-Dedicated Points: 36494 More actions · Answer 4

Luis Cazares (3/3/2016)
You can't execute a result set, you need to assign the text into a variable. Please, be very careful with SQL injection as this could be a recipe for disaster if not done correctly.
To store a full result set in a variable, use the technique explained in here: http://www.sqlservercentral.com/articles/comma+separated+list/71700/
You could use it inside your procedure or you could use an output parameter to execute afterwards.
I'm not posting any code because I'm afraid of the consequences.

This will just be to sync logins and will be just used once, app won't use this.Thanks