Re: use of INSERT with Dynamic SQL
From: Hari (hari_prasad_k_at_hotmail.com)
Date: 02/23/04
- Next message: Suzanne: "Help needed with IIF syntax"
- Previous message: Paul: "Re: string in a query"
- In reply to: Edi Fellmann: "use of INSERT with Dynamic SQL"
- Next in thread: Edi Fellmann: "Re: use of INSERT with Dynamic SQL"
- Reply: Edi Fellmann: "Re: use of INSERT with Dynamic SQL"
- Messages sorted by: [ date ] [ thread ]
Date: Mon, 23 Feb 2004 08:05:04 +0530
Hi,
Pls use the below piece of code format,
declare @sql nvarchar(1000)
set @SQL =('INSERT '+@MyTable+'(ID, Name, Year) VALUES ('+@ID+', '+@Name+',
'+@Year+')')
exec sp_executesql @sql
Thanks
Hari
MCDBA
"Edi Fellmann" <anonymous@discussions.microsoft.com> wrote in message
news:24430DB5-FB10-4876-B5FB-74D43B758969@microsoft.com...
> I need to execute with INSERT to a table which name changes.
> I supose the only way is to use dynamic SQL.
> First we create the MyTable table.
> The spInsertMyTable inserts the values, no problems.
> The spInsertMyTableDynamic does not work. I send the error message.
> Is there a way to execute an INSERT statemente with dynamic SQL?
> Thanks you for any help.
>
>
> CREATE TABLE MyTable
> (
> ID int NOT NULL
> Primary Key,
> Name varchar(50) NULL,
> Year varchar(50) NULL
> )
>
> GO
>
>
> CREATE /*ALTER*/ PROCEDURE spInsertMyTable
> (
> @ID varchar(50),
> @Name varchar(50),
> @Year varchar(50)
> )
> AS
> INSERT MyTable(ID, Name, Year)
> VALUES (@ID, @Name, @Year)
> GO
>
> EXEC spInsertMyTable
>
> @ID = '13',
> @Name ='Test',
> @Year = '2004'
>
> /*
> CREATE/*ALTER*/ PROCEDURE spInsertMyTableDynamic
> (
> @ID varchar(50),
> @Name varchar(50),
> @Year varchar(50),
> @MyTable varchar(50)
> )
> AS
>
> DECLARE @SQL varchar(1000)
>
> SELECT @SQL =('INSERT '+@MyTable+'(ID, Name, Year)
> VALUES ('+@ID+', '+@Name+', '+@Year+')')
>
> PRINT @SQL
> EXEC @SQL
> --GO
>
> EXEC spInsertMyTableDynamic
> @ID = '13',
> @Name ='Test',
> @Year = '2004',
> @MyTable ='MyTable'
>
> */
> /*
> INSERT MyTable(ID, Name, Year)
> VALUES (13, Test, 2004)
> Server: Msg 2812, Level 16, State 62, Line 21
> Could not find stored procedure 'INSERT MyTable(ID, Name, Year)
> VALUES (13, Test, 2004)'.
> */
- Next message: Suzanne: "Help needed with IIF syntax"
- Previous message: Paul: "Re: string in a query"
- In reply to: Edi Fellmann: "use of INSERT with Dynamic SQL"
- Next in thread: Edi Fellmann: "Re: use of INSERT with Dynamic SQL"
- Reply: Edi Fellmann: "Re: use of INSERT with Dynamic SQL"
- Messages sorted by: [ date ] [ thread ]
Relevant Pages
|
