simple question about stored procedures 2

[OhioSteve]

I want to create a stored procedure with a varchar input parameter. The parameter will be a view's name. I want the sp to return the results of "Select * from " & [view name]

Some years ago, I recall writing a sp that took a String and executed it as sql statement. I want to use this technique again. I want to initialize a String with the value "Select * from " & [view name]. Then I want the machine to treat the string as a command to execute.

Can you refresh my memory on the syntax??

 

[PWise]

exec Select * from [" & @viewname & "]"

BTW google Sql injection

 

[RiverGuy]

You need to use single quotes instead of double quotes. For example:

DECLARE @S VARCHAR(255)
DECLARE @TableName VARCHAR(100)
SELECT @TableName = 'INFORMATION_SCHEMA.TABLES'
SELECT @S = 'SELECT * FROM ' + @TableName
EXEC (@S)

But as pwise said, watch out for sql injection. For your particular problem, the dynamic sql approach would probably be my last resort for the solution. Other ideas would be to hard code in IF statements if the number of possible views to select from aren't that great...or you could union the views together in a derived table with an extra virtual column denoting the view name, and select from that derived table. Performance might not be that great if you have lots of views though. Better yet if you are executing this sproc from within an application, let the application handle the view selection.

 

[OhioSteve]

After reading your posts, I was able to write the sp. It is very short:

SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER OFF
GO
CREATE PROCEDURE [dbo].[sp_myproject_test01]
@viewName varchar(50)
AS
exec ('select * from ' + @viewName)

Both of your posts helped me, so you both get stars. Thanks alot!

 

[OhioSteve]

Your concern is that a nefarious user could pass in the names of system tables and learn about my schema. Is that correct?

 

[RiverGuy]

It can get a lot worse than that. You can send multiple statements for SQL Server to execute, and if the account which logs into your connection string has privileges, a user could type in a view name, followed by a semi colon, and then type in a command to drop objects, delete data, etc.

It might not be an issue if you are using a drop down instead of a text box, but sql injection is best handled without having the application's controls provide the security.

 

[SQLSister]

The concern is that a nefarious user could delete the data in your tables or drop them entirely or change the data. Read these if you are going to use dynamic SQl

http://www.sommarskog.se/dynamic_sql.html

http://www.unixwiz.net/techtips/sql-injection.html

http://en.wikipedia.org/wiki/SQL_injection

"NOTHING is more important in a database than integrity." ESquared

 

[OhioSteve]

After reading more about the subject, I agree with your concerns. I will rethink this task. Thanks again for your advice!