Dynamic SQL vs Parameterised query

Question

Is this stored procedure considered Dynamic SQL or a Parameterised query?

CREATE PROCEDURE [dbo].[my_dodgy_sp]
    @varchar1 varchar(50),
    @varchar2 varchar(50)
AS
BEGIN
    ...

    EXEC [dbo].[my_really_special_sp] @varchar1 @varchar2;
END

Extra chocolate donuts with cherries on top if you can tell me whether this is Dynamic / Parameterised:

CREATE PROCEDURE [dbo].[my_super_dodgy_sp]
    @varchar1 varchar(50),
    @varchar2 varchar(50),
    @stored_procedure_name sysname
AS
BEGIN
    ...

    EXEC @stored_procedure_name @varchar1 @varchar2;
END

To make the super dodgy sp less dodgy, you could add some validation to make sure that @spname is 'legitimate'. — MatBailie
– MatBailie, Commented Jan 7, 2012 at 1:45
This is where I get confused. Since super dodgy is just using parameters, this possibly is a parameterised query in which case you can't inject SQL code. So its not dodgy at all and you don't need validation. — icc97
– icc97, Commented Jan 7, 2012 at 1:52
You can't inject code with EXEC @sp @param. You can only supply a reference to a different SP. Which is subtly different. I don't quite agree with dknaack's answer, so I've added my own. I hope it helps :) — MatBailie
– MatBailie, Commented Jan 7, 2012 at 2:02
Great - that answers the actual question I was trying to get at. But I'll go away and attempt some SQL Injection of my own to confirm. I had added in a check against sys.procedures (as in your first comment) but only to prevent SQL Injection! — icc97
– icc97, Commented Jan 7, 2012 at 2:22

dknaack · Accepted Answer · 2012-01-07 01:16:51Z

2

EXEC [dbo].[my_really_special_sp] @varchar1 @varchar2;

Is not a Parameterised query, it is a normal call of a stored procedure.

It's depend on the content of [my_really_special_sp] if this will result in a Parameterised query.

Please provide more information, i would like to help you much more.

answered Jan 7, 2012 at 1:16

dknaack

60.7k28 gold badges166 silver badges211 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

icc97 Over a year ago

Thanks @dknaack, sorry for being vague, but I'm trying to figure out if there are any serious issues with my programming style. Generally my_really_special_sp will be something like UPDATE [my_table] SET [field1] = @varchar1 WHERE [field2] = @varchar2

icc97 Over a year ago

I have a version of it working yes and there's no manipulation. Is UPDATE [my_table] SET [field1] = @varchar1 WHERE [field2] = @varchar2 a parameterised query?

dknaack Over a year ago

You should use the first approach for better readability, better testing and the most important thing... less calls (better performance).

icc97 Over a year ago

Yeah I do, 99% of the time. But I'm still confused if the UPDATE statement in my previous comment is considered 'parameterised'.

dknaack Over a year ago

Assuming i understand what you mean ;) UPDATE [my_table] SET [field1] = @varchar1 WHERE [field2] = @varchar2 is Parameterised

MatBailie · Accepted Answer · 2012-01-07 02:06:21Z

"Dynamic SQL" refers to building up a SQL Query String programatically. Such as adding joins, building up a where clause, etc.

Parameterised Queries are SQL Query Strings that contain variables, the values of which are supplied separately from the SQL Query String.

Neither of your examples fit these descriptions because they are both simple T-SQL calls within stored procedures.

It may seem pedantic, but if your application calls 'EXEC [dbo].[my_really_special_sp] @varchar1 @varchar2', then that is a parameterised query.

And if your SP calls sp_executesql 'EXEC [dbo].[my_really_special_sp] @var1 @var2', @var1 = 1, @var2 = 10 then...

sp_executesql is T-SQL call
'EXEC [dbo].[my_really_special_sp] @var1 @var2' is your parameterised query
@var1 = 1, @var2 = 10 are your parameters

The important point is that your examples are pre-compiled statements in an SP. The examples I tried to explain are strings that are passed to the SQL Server to parse, compile and execute.

If that string is made up programatically piece by piece, it's dynamic sql.

If that string contains variable references that are supplied separately, it is parameterised.

I hope that helps, though I can see that it may seem subjective.

As for your programming style. Your second SP has a minor 'vulnerability', in that if a user has access to it, they have access to all other SPs with the same signature, even if that user doesn't natively normally have access. This may be intentional, and/or you may validate the @spname parameter to close the vulnerability. Other than that, there is nothing I can see that can be faulted.

Brilliant, I love pedantic, it solves lots of confusion :) This is by far the clearest description of the difference.

Collectives™ on Stack Overflow

Dynamic SQL vs Parameterised query

2 Answers 2

5 Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

5 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related