dynamic sql - Inserting into a Table

[Ann Marie]

Hi There,
I am trying to write dynamic sql which will insert a row into a table.
However, I cannot seem to get the syntax correct.

I will be passing the name of one of the fields to be added and the value
for that field.

If anyone has any suggestions, they would be appreciated.

CREATE PROCEDURE dbo.Add_Daily_Activity_Stats (
@Field_Name VARCHAR(25),
@Team_Id INTEGER,
@Total INTEGER
)
AS

EXEC ("INSERT INTO Daily_Activity_Stats
(
Date,
Team_Id,
'+@Field_Name+'
)
VALUES
(
GETDATE(),
@Team_Id,
@Total
)
)"

[Gary]

Ann Marie try soemthing like this

CREATE PROCEDURE dbo.Add_Daily_Activity_Stats (
@Field_Name VARCHAR(25),
@Team_Id INTEGER,
@Total INTEGER
)
AS
declare @sql varchar(255)
set @sql = 'INSERT INTO Daily_Activity_Stats (Date, Team_Id, ' + @Field_Name +
&#39 VALUES (GETDATE(), ' + convert(varchar,@Team_Id) + ',' + convert(varchar,@Total) + ' )'
exec @sql


Gary


------------
Ann Marie at 6/22/01 4:51:24 AM

Hi There,
I am trying to write dynamic sql which will insert a row into a table.
However, I cannot seem to get the syntax correct.

I will be passing the name of one of the fields to be added and the value
for that field.

If anyone has any suggestions, they would be appreciated.

CREATE PROCEDURE dbo.Add_Daily_Activity_Stats (
@Field_Name VARCHAR(25),
@Team_Id INTEGER,
@Total INTEGER
)
AS

EXEC ("INSERT INTO Daily_Activity_Stats
(
Date,
Team_Id,
'+@Field_Name+'
)
VALUES
(
GETDATE(),
@Team_Id,
@Total
)
)"

[phil mc]

Try putting the SQL statement into a variable first then exec'ing the variable as follows :-

Declare @vchSQLText varchar(500)

Select @vchSQLText='INSERT INTO Daily_Activity_Stats (Date,Team_Id,'
Select @vchSQLText=@vchSQLText+@Field_Name+&#39 VALUES (GETDATE(),'
Select @vchSQLText=@vchSQLText+@Team_Id+','+@Tota l+&#39)'

exec (@vchSQLText)


------------
Ann Marie at 6/22/01 4:51:24 AM

Hi There,
I am trying to write dynamic sql which will insert a row into a table.
However, I cannot seem to get the syntax correct.

I will be passing the name of one of the fields to be added and the value
for that field.

If anyone has any suggestions, they would be appreciated.

CREATE PROCEDURE dbo.Add_Daily_Activity_Stats (
@Field_Name VARCHAR(25),
@Team_Id INTEGER,
@Total INTEGER
)
AS

EXEC ("INSERT INTO Daily_Activity_Stats
(
Date,
Team_Id,
'+@Field_Name+'
)
VALUES
(
GETDATE(),
@Team_Id,
@Total
)
)"

[Ann Marie]

Gary, that did the trick i just had to have brackets around the exec
exec (@sql)

Thanks very much for your help
Ann Marie


------------
Gary at 6/22/01 5:54:53 AM

Ann Marie try soemthing like this

CREATE PROCEDURE dbo.Add_Daily_Activity_Stats (
@Field_Name VARCHAR(25),
@Team_Id INTEGER,
@Total INTEGER
)
AS
declare @sql varchar(255)
set @sql = 'INSERT INTO Daily_Activity_Stats (Date, Team_Id, ' + @Field_Name +
&#39 VALUES (GETDATE(), ' + convert(varchar,@Team_Id) + ',' + convert(varchar,@Total) + ' )'
exec @sql


Gary


------------
Ann Marie at 6/22/01 4:51:24 AM

Hi There,
I am trying to write dynamic sql which will insert a row into a table.
However, I cannot seem to get the syntax correct.

I will be passing the name of one of the fields to be added and the value
for that field.

If anyone has any suggestions, they would be appreciated.

CREATE PROCEDURE dbo.Add_Daily_Activity_Stats (
@Field_Name VARCHAR(25),
@Team_Id INTEGER,
@Total INTEGER
)
AS

EXEC ("INSERT INTO Daily_Activity_Stats
(
Date,
Team_Id,
'+@Field_Name+'
)
VALUES
(
GETDATE(),
@Team_Id,
@Total
)
)"