Stored Procedure Case Statement Help 1

[ease20022002]

Hi, I am trying to write a stored procedure that has an input parameter. That input parameter will determine which INSERT script I want to run in the stored procedure.

The problem I am having is I can't seem to figure out how to wrap the Insert Statements in the Case statements without getting errors. I always get an error where it says there is an error near CASE and END with the syntax below. Also, I've tried the Case statement with and without the ELSE in there and it still doesn't work. Here is what I have:

CREATE PROCEDURE spInsertActivities (
	-- Add the parameters for the stored procedure here
	@ImportTable Varchar(20) --Variable to indicate from which Import Table the Insert is coming from	
	)

AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;
	
	Case 
		When @ImportTable = 'CognosExtract' Then
			
			INSERT INTO [FinancialDB].[dbo].[Activities]
			   ([Activity])
				
			Select Activity
			From CognosExtract a
			Where Not Exists
					(
					Select Activity
					From Activities b
					Where b.Activity = a.Activity
					)
			Group By a.Activity	
		
		When @ImportTable = 'ActualsExtract'
			
			INSERT INTO [FinancialDB].[dbo].[Activities]
			   ([Activity])
				
			Select Activity
			From ActualsExtract a
			Where Not Exists
					(
					Select Activity
					From Activities b
					Where b.Activity = a.Activity
					)
			Group By a.Activity	
	End 

END
GO

Any help would be greatly appreciated.

Thanks

 

[markros]

I think in this particular case you over-done yourself - there was no need for Sp_ExecuteSQL at all in this case - you're not using parameters, you're using Table and Columns dynamically.

Is there a business case for this or it would be better to directly use table names and fields names (even if you would have few more SPs)?

 

[ease20022002]

Markros, I would have considerably more stored procedures as I am trying to get unique values inserted into the dimension tables in the star schema db structure where this SP resides. So there are roughly 30 Dimension tables and I don't want to create 30 separate stored procedures, and maintain them.

The reason why I over did the dynamic sql is b.c of the SQL Intrusion threat that was posted before. I assume it is better to be safe than sorry.

Thanks for the response.

 

[markros]

In your case you're still concatenating - not sure that Sp_ExecuteSQL is necessary / prevents from SQL injection attack.

 

[Emtucifor]

If you don't want to maintain 30 stored procedures, may I suggest another way to avoid dynamic SQL... sort of?

I have done this before and it's been very successful. Put template code in a Script table that uses placeholders for columns or tables such as INSERT Script (ScriptName, ScriptText) VALUES ('InsertDimensionTable', 'INSERT INTO dbo.{TableName} ...').

Then build a stored procedure that drops and creates all the stored procedures you need. Loop through every table and column necessary and build dynamic SQL with some strategic replaces.

SELECT @InsertDimensionSQL = ScriptText FROM Script WHERE ScriptName = 'InsertDimensionTable'
...
WHILE 1 = 1 BEGIN
   SELECT @SQL = Replace(@InsertDimensionSQL, '{TableName}', @TableName)
   ...
   EXEC (@SQL)
   -- Exit loop if no more tables/columns
END

Perhaps you don't want to invest the kind of time to build something like this. But, the reasons that I find this preprocessing technique so valuable are:

1. The objects are really created and sit out there and no dynamic SQL is used, and each one can be fully examined for correctness.
2. It's easier to maintain SQL code that's in a table, than SQL code that's broken up into pieces inside of single quotes inside a stored procedure. For one thing, it's much easier to handle single quotes. (Don't update the script from Management Studio, in this one case take advantage of direct table editing!).
3. The risk of SQL injection is completely alleviated as long as rights to run the SP-creating SP are tightly controlled. All other calls are completely normal SQL with correct parameters.
4. Getting experience with this technique allows you to use it in other situations. I've used it in three separate situations, myself, and found it amazingly powerful.

 

[ease20022002]

Thanks, Emtucifor, I think once I have time to upgrade the process I will use what you have posted. Thank you again.

Paul

 

[JeffModen]

SELECT @InsertDimensionSQL = ScriptText FROM Script WHERE ScriptName = 'InsertDimensionTable'...WHILE 1 = 1 BEGIN   SELECT @SQL = Replace(@InsertDimensionSQL, '{TableName}', @TableName)   ...   EXEC (@SQL)   -- Exit loop if no more tables/columnsEND

How does that prevent an injection attack if @Tablename were to come from the outside world? How is it any different than concatenated dynamic SQL? Not a challenge here. I just don't see the difference and whould like to know.

--Jeff Moden
-------------------------------------------------------------------------------
"RBAR" is pronounced "ree-bar" and is a "Modenism" for "Row By Agonizing Row

 

[Emtucifor]

@TableName doesn't come from the outside world. Here, @TableName will come from a table that you populate yourself as a manual administrative task. Creating a new stored procedure to handle a new table is a simple process of inserting a new row to your DimensionTables table and then running your SP again. No non-admin person would be running this SP, ever. And no client code would be calling this SP, either.

 

[gmmastros]

Emtucifor,

What you're saying is.... you wrote code that writes the code for you. Only a developer would be able to run this. Right?

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[Emtucifor]

Exactly, George. The preprocessing is only done by admins or developers.

Using this technique I built a bolt-on component that populates history tables on every data change. While it was a lot of work to create, I can now reuse it over and over. I can add full history capacity to an entire database of hundreds or thousands of tables in about 10 minutes (not counting the time it takes for the initial snapshot data to be pushed to the history tables). I just add the "hist" schema, script out the two tables (one with a trigger) and one SP, and then run the SP. The tables get auto-populated with all the table names and column names in the database, and then I do an update on the table list to set active those tables that I actually want history on. Boom: full history.

If columns are added or removed, a single run of the SP automatically adds new columns and corrects the history triggers to add new columns or ignore old ones. The limitations of my tool right now are: tables must have a primary key, no support for text or image data types, no support for multi-column primary keys. But the SP simple leaves alone any tables with these problems and informs you what it is doing. It also doesn't handle data type changes automatically, but come on: you can't go around changing data types in a database and expect to do no work at all somewhere. But any DDL change against a data table can easily be applied to the history table, too.

ALTER TABLE Blah ALTER COLUMN FullName varchar(64) -- used to be varchar(32)
ALTER TABLE BlahHistory ALTER COLUMN FullName varchar(64) -- not so hard... and the trigger doesn't even need to change.

I didn't feel confident about being able to automagically handle edge cases where, say, a column that used to have text in it but now has only numbers is converted to a numeric data type. The history table still has text in that column, so someone has to decide what to do (best is probably to rename the history table column to ColumnName_Old and then add the new numeric data type ColumnName). But everything is STILL way easier than trying to manage all this without the preprocessing trigger- and table- creating SP.

 

[JeffModen]

What you're saying is.... you wrote code that writes the code for you. Only a developer would be able to run this. Right?

Ah... I get it. Thanks.

--Jeff Moden
-------------------------------------------------------------------------------
"RBAR" is pronounced "ree-bar" and is a "Modenism" for "Row By Agonizing Row