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

 

[gmmastros]

The case statement cannot be used to control the flow of logic. Instead, you need to use an IF statement.

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;
    
    If @ImportTable = 'CognosExtract'
        Begin
            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    
        End
    If @ImportTable = 'ActualsExtract'
        Begin     
            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

-George

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

 

[ease20022002]

George,

Thanks a lot!

 

[JeffModen]

The code is identical except for the FROM clause. I guess I don't understand why someone wouldn't just create a bit of dynamic SQL for this so that you don't have to maintain two copies of the code in the future. Since it smacks of a batch job, it's not like you have to worry about the very, very short recompile the dynamic SQL will cause. In fact, the recompile might actually be a benefit in that it eliminates any chance of a previous execution plan being used when just EXEC is used.

Granted, the current code is short and easy to maintain even if two copies of the code are included.

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

 

[ease20022002]

Jeff, I was actually going to enclose FROM clause with IF statements around and add a different FROM clause for each import table.

That way nothing is duplicated. What do you think?

Can you give me an example of Dynamic SQL that could do the same function?

Thank you for your time and any information provided.

Paul

 

[ease20022002]

Nevermind, Jeff, I am reading up on it now. I have a vb programming backroung and I understand what is needed to be done. Pretty cool stuff.

Thanks,

Paul

 

[gmmastros]

Dynamic SQL can be great, or it can be awful (worse than awful). If you plan on using dynamic SQL, you should at least be aware of the security implications, especially SQL Injection.

I STRONGLY encourage you to read this:

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

-George

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

 

[gmmastros]

In this case, suppose you wrote this code:

            Set @SQL ='
              INSERT INTO [FinancialDB].[dbo].[Activities]
                 ([Activity])
                
              Select Activity
              From [' + @ImportTable + '] a
              Where Not Exists
                    (
                    Select Activity
                    From Activities b
                    Where b.Activity = a.Activity
                    )
              Group By a.Activity'

            Exec (@SQL)

Also suppose someone (somehow) ran your stored procedure passing in the value:

a; Drop Table user;

Notice that this is less than your 20 character limit on the parameter value. The semi-colons will allow your single statement to be parsed as multiple statements. The first and last parts will probably fail, but the middle part Drop Table User may actually succeed. What a mess you would have then.

In this case, it's pretty easy to protect yourself from dynamic sql by only allowing certain values. Like this:

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;

    Declare @SQL VarChar(8000)
	
    [!]If @ImportTable In ('CognosExtract','ActualsExtract')[/!]
        Begin

            Set @SQL ='
              INSERT INTO [FinancialDB].[dbo].[Activities]
                 ([Activity])
                
              Select Activity
              From [' + @ImportTable + '] a
              Where Not Exists
                    (
                    Select Activity
                    From Activities b
                    Where b.Activity = a.Activity
                    )
              Group By a.Activity'

            Exec (@SQL)


        End

END
GO

Note that the "real" code... the important part... is only run if the input parameter exists in a list of acceptable parameter values.



-George

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

 

[Emtucifor]

ease20022002,

You may find it productive to read Mixed-Up Statement Types, which is somewhat of a primer on expressive vs. procedural statements. Reading it could help you in the future to solve this kind of problem yourself without having to ask for assistance.

 

[markros]

Would you please re-post it as a blog in LTD? I can not read it in its current way - the background is too hard to read for my eyes - I can only read blogs written on white background.

 

[Emtucifor]

Urk, that's going to take some editing... In the meantime, can you hit Ctrl-A to read it?

I'll try to do this soon.

 

[JeffModen]

ease20022002 (TechnicalUser) 9 Oct 09 8:25
Nevermind, Jeff, I am reading up on it now. I have a vb programming backroung and I understand what is needed to be done. Pretty cool stuff. Thanks,Paul

Paul,

The others are correct... athough you shouldn't be afraid to use it properly, you need to be a bit careful with dynamic SQL to avoid "SQL INJECTION".

To do that, read up on sp_ExecuteSQL to parameterize the table names and use the QUOTENAME function to encapsulate the table names in square brackets.

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

 

[Emtucifor]

Jeff,

Could you give an example of avoiding sql injection by parameterizing table names using sp_executesql? I'm curious as to what you mean.

 

[JeffModen]

Sure Erik... but in this case, the OP said...

Nevermind, Jeff, I am reading up on it now.

... and I thought I'd give him a couple of clues as to what to look for instead of depriving him of the pleasure.

Now, don't you have some test code to write to prove some unreliable code or something? ;-)

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

 

[Emtucifor]

I asked because you can't pass table names using sp_executesql unless you're still just concatenating them into the string. You can never do "SELECT * FROM @tablename" no matter how you slice it. So I don't see sp_executesql really helping in parameterizing table names. That's all.

 

[markros]

I agree here. Was curious to see, what Jeff meant. Of course, for the parameter we want to use SP_ExecSQL, but we can not use it for tablename or column name.

 

[JeffModen]

Emtucifor,

Ah... understood. Hang on a minute while I get my foot out of my mouth... ;-)

You and Markros are correct... you can't do much about things like table names in dynamic SQL except to simply concatenate them and I apologize because, having reread my post, I see that it's misleading in that area. The "parameterization" would come from the stored procedure that would be used as a wrapper/carrier for the dynamic SQL. To keep that from becoming a source of SQL Injection, it would be good to wrap it in the QUOTENAME function (for starters).

My humble apologies for the bit of confusion I've cause on this.

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

 

[ease20022002]

I just made another post and saw everyone posting re: dynamic SQL. I am going to read the links you guys posted and thanks for taking the time to reply. Much appreciated.

Also, I did use the dynamic sql in my stored proc and I parameterized all field names and concatenated all in the @sqlcommand variable. Thanks again, Jeff.

Paul

 

[Emtucifor]

I just have to say again: avoid dynamic sql wherever possible. Bend over backwards to avoid it.

It sounds really scary that you are passing in column names to a stored procedure.

 

[ease20022002]

Emtucifor,

Here is what I have done after reading gmmastros link to dynamic sql and using sp_executesql, basically following the QuoteName() rule and the dbo rule. I no longer use the EXEC to execture the @sql line. Here is my code:

DECLARE @sql    nvarchar(Max),
					@params nvarchar(Max)
			
			SET @sql = 'INSERT INTO dbo.' + QuoteName(@InsertTable) + ' 
							(' + QuoteName(@InsertField) + ')
							Select a.' + QuoteName(@SelectField) + ' 
							From dbo.' + QuoteName(@SelectTable) + ' a
							Where Not Exists
								(
								Select b.' + QuoteName(@InsertField) + ' 
								From dbo.' + QuoteName(@InsertTable) + ' b
								Where b.' + QuoteName(@InsertField) + ' = a.' + QuoteName(@SelectField) + ' 
								)
							Group By a.' + QuoteName(@SelectField) + ''
			
			SELECT @params = '@InsertTable Varchar(50), ' +
							'@InsertField Varchar(50), ' +
							'@SelectTable Varchar(50), ' +
							'@SelectField Varchar(50)'			

			EXEC sp_executesql @sql, @params, @InsertTable, @InsertField, @SelectTable, @SelectField

Thanks for everyone's help.
Paul