Correct syntax for dynamic sql using t-sql procedure

[jwalacha]

Using a stored procedure that takes input parameters, I would like to do the following:

Insert into table1(datetimestamp, seqno, loc, val) VALUES
(@datetimestamp, 1, @loc1, @val1)
Insert into table1(datetimestamp, seqno,loc, val) VALUES
(@datetimestamp, 2, @loc2, @val2)
Insert into table1(datetimestamp, seqno, loc, val) VALUES
(@datetimestamp, 3, @loc3, @val3)
.
.
.
where @datetimestamp, @loc1, @loc2, @loc3, @val1, @val2, @val3 are input values to the stored procedure.

I would like to create a loop within the stored procedure so that I dont have to write individual insert statements. I have tried using the exec() command but have not been successful with the correct syntax.

Please help. thanks.

Jay

 

[checkai]

if it's only 3 statements, I wouldn't do a loop...how many variables are you going to end up passing in?

"...we both know I'm training to become a cagefighter...see what happens if you try 'n hit me..."

 

[gmmastros]

How many are we talking about here? Could you send in a list of loc's and val's as comma delimited strings. You could then use a splitter udf and do all the inserts in 1 statement. If you are interested in this approach, let me know and I will advise accordingly.

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[jwalacha]

I am planning on populating close to 300 rows (320 to be exact) with this stored procedure.
Thanks.

 

[jwalacha]

...as to the question George posed, currently, I am planning on receiving the vals and locs as individual IN parameters to the stored procedures. (The stored procedure is being called by a third party application - RSsql)
I am, therefore, not sure about sending the list of locs and vals as a comma delimited string. I would like to see if there is an alternate solution before I have to try this approach.
Thanks,
Jay.

 

[gmmastros]

So, here you go....

First, create this UDF.

Create Function dbo.Split(@CommaDelimitedFieldNames Varchar(8000), @CharToFind Char(1) ) 
Returns @Tbl_FieldNames Table (Position Integer Identity(1,1), FieldName VarChar(100)) As 

Begin 
 Set @CommaDelimitedFieldNames = @CommaDelimitedFieldNames + @CharToFind

 Declare @Pos1 Int
 Declare @pos2 Int
 
 Set @Pos1=1
 Set @Pos2=1

 While @Pos1<Len(@CommaDelimitedFieldNames)
 Begin
 Set @Pos1 = CharIndex(@CharToFind, @CommaDelimitedFieldNames,@Pos1)
 Insert @Tbl_FieldNames Select Cast(Substring(@CommaDelimitedFieldNames,@Pos2,@Pos1-@Pos2) As VarChar(100))
 Set @Pos2=@Pos1+1
 Set @Pos1 = @Pos1+1
 End 
 Return
End

Then, create this stored procedure. You should rename it to something that makes more sense.

Create Procedure LoadMyData
	@DateTimeStamp DateTime,
	@Locs VarChar(8000),
	@Vals VarChar(8000)
AS
SET NOCOUNT ON

[green]-- Create a table Variable to hold Loc's[/green]
Declare @LocsTable Table(Id Integer, Loc Integer)

Insert Into @LocsTable(Id, Loc)
Select Position, FieldName From dbo.Split(@Locs, ',')

[green]-- Create a table variable to hold vals[/green]
Declare @ValsTable Table(Id Integer, Val VarChar(100))
Insert Into @ValsTable(Id, Val)
Select Position, FieldName From dbo.Split(@Vals, ',')

[green]-- Setup the result table.  You'll need to replace this with your real table[/green]
Declare @Table1 Table(DateTimeStamp DateTime, SeqNo Integer, Loc Integer, Val VarChar(100))

[green]-- Make sure you replace @Table1 with your real table.[/green]
Insert Into @Table1(DateTimeStamp, SeqNo, Loc, Val)
Select @DateTimeStamp,
		L.Id,		
		L.Loc,
		V.Val
From	@LocsTable L
		Inner Join @ValsTable V On L.Id = V.Id

[green]-- Just to view the changes.[/green]
Select * From @Table1

Then, you can use it like this...

LoadMyData '8/31/2005', '6,9,8,2,12,9', 'a,b,c,d,e,f'

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[jwalacha]

thanks George for your prompt response.
I don't yet know if I have the capability of receiving the vals and locs in the comma delimited file format. Can you also provide me with an alternate solution?
thanks.
Jay.

 

[gmmastros]

I am planning on populating close to 300 rows (320 to be exact) with this stored procedure.

How are you passing this data in to the stored procedure? I assume that you have 1 parameter for the DateTimeStamp. Do you have 320 parameters for the loc variables and another 320 parameters for the val variables?

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[jwalacha]

I actually receive the datetimestamp value as year, month, day, hours, minutes and seconds that I convert into a datetime format within the stored procedure. But for simplicity sake, it is ok to assume that I get datetimesamp as one entity.

And yes, I have 320 parameters for loc values and 320 parameters for val values.

I am naming the IN parameters as follows:
@loc1, @loc2, @loc3......@loc320 AND
@val1, @val2, @val3...@val320.

Thanks.

 

[jwalacha]

Both the location and value variables are defined as Numeric(7,3).

 

[gmmastros]

Sorry, I'm at a loss here. There are smarter guys than me around here and one of them may have a solution for you. Since I couldn't directly help you, and I feel bad about that, I took the liberty of writing some code that may help you create a stored procedure that has 640 input parameters and performs 320 inserts.

Before running this code, press CTRL-T within QA. This will set the results to be returned in 'text' mode instead of grid mode. You can press CTRL-D after it's done to reset the results in grid mode.

After running the code, you can copy/paste the results in to a QA window to create the stored procedure. You'll need to modify it a little to include the @datetimestamp variable. Hope this helps.

SET NOCOUNT ON
Declare @i Integer
Declare @Sql VarChar(1000)
Declare @Temp Table (Id Integer)

Set @i = 1
While @i < 321
  Begin
	Insert Into @Temp Values(@i)

	Set @i = @i + 1
  End

Declare @Out Table(Line VarChar(1000))

Insert into @Out Values('Create Procedure InsertManyValues')

Insert Into @Out
Select '    @Loc' + Convert(VarChar(10), Id) + ' Numeric(7,2),'
From @Temp

Insert Into @Out
Select '    @Val' + Convert(VarChar(10), Id) + ' Numeric(7,2),'
From @Temp

Update @Out 
Set Line = '    @Val320 Numeric(7,2)' 
Where Line = '    @Val320 Numeric(7,2),' 

Insert into @Out Values('AS')
Insert into @Out Values('Set Nocount On')

Insert Into @Out
Select 'Insert into table1(datetimestamp, seqno, loc, val) VALUES(@datetimestamp, ' + Convert(VarChar(10), Id) + ', @Loc' + Convert(VarChar(10), Id) + ', @val' + Convert(VarChar(10), Id) + ')'
From @Temp

Select * from @Out

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[jwalacha]

I thank George for his efforts and providing me with the code to create 320 insert statements. Although, I wouldn't want to have my client view a stored proc. with 320 insert statements, and I think you will agree.

I was hoping for a solution for something along the lines as this:
.
.
declare @temploc varchar(20)
declare @tempval varchar(20)
declare @sqlstmt varchar(200)
@declare @counter numeric(3)

@set @counter = 1

While @counter < 321
Begin

@temploc = '@loc' + cast(@counter as varchar)
@tempval = '@var' + cast(@counter as varchar)

SET @sqlstmt = 'INSERT INTO table1 (datetimestamp, seqno, loc, val) VALUES (@datetimestamp, @counter,' + @temploc + ',' + @tempval + ')'

exec(@sqlstmt)
End
.
.

..OR at least something to this effect. I would appreciate it if some Sql server guru can tell me if this is at all possible.

Awaiting all the help that I can get,
Jay.