SQL stored procedure question - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

SQL stored procedure question

Author

Topic

benmartin101
Starting Member

2 Posts

Posted - 2015-05-06 : 14:30:15

Is it possible to have an entire sql select statement as the input variable to a stored procedure? I want the stored procedure to execute the select statement.

ie.

exec sp_SomeFunc 'select * from table1 where id=1'

It may sound weird, but I have my reason for wanting to do it this way. Is this possible? if so, how do I implement this inside the stored procedure? Thanks.

MichaelJSQL
Constraint Violating Yak Guru

252 Posts

Posted - 2015-05-06 : 14:35:38

You can do that - but it is not a great idea because it exposes your system up to just about anything -- anything!!!!!!!

that being said:
CREATE PROC PassSQL
@SQL nvarchar(max)
AS
BEGIN

EXEC sp_executeSQL @SQL

END

https://technet.microsoft.com/en-us/library/ms175170(v=sql.105).aspx

Kristen
Test

22859 Posts

Posted - 2015-05-06 : 14:37:23

Sort of ...

you can pass the SQL as a Text String parameter e.g. NVARCHAR(MAX)

You can then execute that within the SProc. You can use


EXEC (@MySQLParameter)

but MUCH better would be to use parameterised SQL and sp_ExecuteSQL like this:


EXEC sp_ExecuteSQL @MySQLParameter,
                   N'@Param1 varchar(999), @Param2 int, ...',
                   @Param1 = @Param1,
                   @Param2 = @Param2,
                   ...

you would need to, also, pass @Param1, @Param2, ... to your SProc (or declare and originate values for them there).

sp_ExecuteSQL will avoid issues with SQL Injection, and its query plans will be reused - whereas with EXEC it is likely that every query will make a new query plan, which will NOT scale well and is likely to perform very badly on a database of any size.

With sp_ExecuteSQL your @MySQLParameter would be something like:


SELECT Col1, Col2, ...
FROM MyTable
WHERE Col1 = @Param1 AND COl2 = @Param2, ...

and NOT like this


SELECT Col1, Col2, ...
FROM MyTable
WHERE Col1 = 'FooBar' AND COl2 = 123, ...

because it is that parameterisation which allows the Query Optimiser to reused the cached query plan.

benmartin101
Starting Member

2 Posts

Posted - 2015-05-06 : 15:15:22

Thanks you for the replies. It works.

Kristen
Test

22859 Posts

Posted - 2015-05-07 : 05:01:41

Yeah, but have you done it in a way that is "safe"?

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources