Dynamic SQL - Evaluating String Execution 1

[JSpicolli]

I have a proc that takes a bunch of input params.
of these, are included @sOption1 thru @sOption5.

There is also a paramter @OptionCount that holds how many @sOption params contain input data.

Every @sOption param that has data needs to be inserted into a new row in te table.

In my proc I build a string variable that builds the exact SQL statement in a loop (one for each @soption). That variable is named @InsertString .
If I print it, it holds the correct text.
However, when I attempt to execute the variable string, I get an error "Must declare the variable @sOption1".

@sOption1 is an input param, so wtf????

Any help is greatly appreciatted...

Here is my SQL Code

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[apCreatePoll]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)

            drop procedure [dbo].[apCreatePoll]

GO

 

 

CREATE PROCEDURE apCreatePoll

(

   @sPollName varchar(50),
   @sPollDescription varchar(250),
   @dExpires smallDateTime,
   @nCategoryID int,
   @sOption1 nvarchar(200) = '',
   @sOption2 nvarchar(200) = '',
   @sOption3 nvarchar(200) = '',
   @sOption4 nvarchar(200) = '',
   @sOption5 nvarchar(200) = '',
   @OptionCount int
)

 

 As

   Set NoCount On

 Declare @iterator int
 Declare @ErrCount int
 Declare @NewPollID int
 Declare @OptionTag Nvarchar(200)
 Declare @InsertString Nvarchar(1000)
 Declare @sOptionValue Nvarchar(200)
 Declare @tempString Nvarchar(500)

 Set @ErrCount = 0
 Set @NewPollID = 0
 Set @iterator = 1

[COLOR=green]
 -- Insert Poll Master Data
[/color]

  INSERT INTO dbo.AD_POLL
   (sName, sDescription, dExpires, nCategoryID) 
  VALUES 
   (@sPollName, @sPollDescription, @dExpires, @nCategoryID)

 

    IF @@Error = 0
    BEGIN
       SELECT @NewPollID = SCOPE_IDENTITY()
    END

 
[COLOR=green]
 -- Add Poll Items
[/color]
  WHILE (@iterator <= @OptionCount)
  BEGIN

  Set @OptionTag = '@sOption' + Cast(@iterator as Varchar(50))

 Set @tempString = 'Set @sOptionValue = ' + @OptionTag
[COLOR=green]
  --EXEC sp_executeSql @tempString

   /*

     Server: Msg 137, Level 15, State 2, Line 1
     Must declare the variable '@sOption1'.

   */

 
  --EXEC @sOptionValue + '= @OptionTag'
  --EXEC sp_executesql @sOptionValue = @OptionTag
[/color]
 
[COLOR=blue]
  SET @InsertString ='INSERT INTO dbo.AD_POLL_ITEMS (nPollID, sOption) VALUES ' + '(' + Cast(@NewPollID as varchar(10)) + ', ' + @OptionTag + ')'
[/color]

                                    
  Print @InsertString
[COLOR=green]
  --Print @OptionTag
  --Print @sOptionValue
  --Print @tempString
  --Print @sPollName
[/color]
  EXEC sp_executesql @InsertString
  Print ' '
  Set @iterator = (@iterator + 1)

END

I call it with --

EXEC apCreatePoll 'Test', 'poll Test', '12-25-2004', 0, 'Opt 1', 'Opt 2', 'Opt 3', '', '', 3

 

[TheDrider]

When you execute a stored procedure, it runs as a batch. All variables you create are valid only for that batch.

When you exec() a string, that string executes as it owns separate batch, and variables created in the stored procedure will not be visible to the exec'ed string.


Instead try doing five conditional INSERT statements:

IF Len(@sOption1) > 0
INSERT INTO AD_POLL_ITEMS (nPollID, sOption)
VALUES (Convert(varchar,@NewPollID), @sOption1)

Repeat for the other 4 options. The explicit INSERT statements will also probably execute faster than a string building loop.

 

[JSpicolli]

Thanks, I had thought about a similar technique but thought there had to be a better way.

For some reason it just seems like a kludge to have to include a seperate insert for each input Param.

I guess that is just the OO developer in me...

Thanks for the suggestion, it is what I am implimenting now.