CODEWITHSUNDEEP
sqlstored-proceduresdynamic-sql
icc97
icc97

Reputation: 12843

Dynamic SQL vs Parameterised query

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

Upvotes: 2

Views: 642

Answers (2)

MatBailie
MatBailie

Reputation: 86775

"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.

Upvotes: 1

dknaack
dknaack

Reputation: 60516

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.

Upvotes: 2

Related Questions