Stored Procedure - Insert with table name from parameter
First of all hi for all,
I m trying to make insert stored procedure with parameters, i want to send table name and insert values with parameters, i m trying that below stored procedure but it gives syntax eror to me what shoul i do to correct it ?
set
ANSI_NULLSONset
QUOTED_IDENTIFIERONGO
ALTER
PROCEDURE [dbo].[insertalepform]-- Add the parameters for the stored procedure here
@type
nvarchar(15),@talepbrmnoint,@birimisteksayibigint,@birimistektarihdatetime,@aciklama
nvarchar(50),@onayint,@seviyeint,@talepformnobigint,@taleptarihdatetime AS
BEGIN
SETNOCOUNTON;exec
('INSERT INTO [BelediyeWork].[dbo].['+@type+'TalepFormu]([TalepBirimNo], [BirimistekSay?s?], [BirimistekTarihi], [Ac?klama], [Onay]
,[Seviye], [TalepFormNo], [TalepTarih])
VALUES ('
+@talepbrmno+','+@birimisteksayi+','+@birimistektarih+','+@aciklama+','
+@onay+','+@seviye+','+@talepformno+','+@taleptarih+') ')END
[4251 byte] By [
AKINAL] at [2007-12-22]
EXEC will not process a parameterized command. Also, you need to
convert everything to string to concatenate. Finally, I'd check to
ensure your data is set up correctly. Alternatively, look at
sp_executesql.
Try:
set ANSI_NULLS ONset QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[insertalepform]
-- Add the parameters for the stored procedure here
@type nvarchar(15), @talepbrmno int, @birimisteksayi bigint, @birimistektarih datetime,
@aciklama nvarchar(50), @onay int, @seviye int, @talepformno bigint, @taleptarih datetime
AS
BEGIN
DECLARE @sqlCmd NVARCHAR(500)
SET NOCOUNT ON;
SET @sqlCmd ='INSERT INTO [BelediyeWork].[dbo].['+@type+'TalepFormu]
([TalepBirimNo], [BirimistekSay?s?], [BirimistekTarihi], [Ac?klama], [Onay],[Seviye], [TalepFormNo], [TalepTarih])
VALUES
('+(convert(varchar,@talepbrmno))+','+(CONVERT(Varchar,@birimisteksayi))+','+(convert(varchar,@birimistektarih))+','+@aciklama+'
,'+(convert(varchar,@onay))+','+(convert(varchar(@seviye))+','+(convert(varchar,@talepformno))+','''+@taleptarih+''')'
--debug view
select @sqlCmd
exec (@sqlCmd )
END
Hi,
first of all I want to make you aware of using dynamic SQL, you will have to take care of all implications and danger of that:
http://www.sommarskog.se/dynamic_sql.html
If you really want to use dynamic sql, I would first put that into a variable which can be printed out by using the PRINT command for debugging purposes. If you had done so, you would see that you will have to doublequote your values:
VALUES (''+@talepbrmno+ '',
HTH, Jens Suessmeyer.http://www.sqlserver2005.de
 GethWho wrote: |
EXEC will not process a parameterized command. Also, you need to convert everything to string to concatenate. Finally, I'd check to ensure your data is set up correctly. Alternatively, look at sp_executesql.
Try:set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[insertalepform]
-- Add the parameters for the stored procedure here
@type nvarchar(15), @talepbrmno int, @birimisteksayi bigint, @birimistektarih datetime,
@aciklama nvarchar(50), @onay int, @seviye int, @talepformno bigint, @taleptarih datetime
AS
BEGIN
DECLARE @sqlCmd NVARCHAR(500)
SET NOCOUNT ON;
SET @sqlCmd ='INSERT INTO [BelediyeWork].[dbo].['+@type+'TalepFormu]
([TalepBirimNo], [BirimistekSay?s?], [BirimistekTarihi], [Ac?klama], [Onay],[Seviye], [TalepFormNo], [TalepTarih])
VALUES ('+(convert(varchar,@talepbrmno))+','+(CONVERT(Varchar,@birimisteksayi))+','+(convert(varchar,@birimistektarih))+','+@aciklama+'
,'+(convert(varchar,@onay))+','+(convert(varchar(@seviye))+','+(convert(varchar,@talepformno))+','''+@taleptarih+''')'
--debug view
select @sqlCmd
exec (@sqlCmd )
END
|
|
i tried this one but i ve a problem i cant convert datetimes and nvarchars, to stings
and also how can i use sp_executesql for this?
http://www.sommarskog.se/dynamic_sql.html and thanks for this link very useful
youdo need to have them all in the same format. Look up convert in BOL,
specifically around the date time convert, to decide what format you
want the datetime in. Alternatively, if you are inserting into a
datetime field, then the way I listed, with the extra '' should work,
iirc. What did the select @sqlcmd generate, or were there formatting
error's?
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[insertalepform]
-- Add the parameters for the stored procedure here
@type nvarchar(15), @talepbrmno int, @birimisteksayi bigint, @birimistektarih datetime,
@aciklama nvarchar(50), @onay int, @seviye int, @talepformno bigint, @taleptarih datetime
AS
BEGIN
DECLARE @sqlCmd NVARCHAR(500)
SET NOCOUNT ON;
SET @sqlCmd ='INSERT INTO [BelediyeWork].[dbo].['+@type+'TalepFormu]
([TalepBirimNo], [BirimistekSay?s?], [BirimistekTarihi], [Ac?klama], [Onay],[Seviye], [TalepFormNo], [TalepTarih])
VALUES ('+(convert(nvarchar,@talepbrmno))+','+(CONVERT(nVarchar,@birimisteksayi))+','+(convert(nvarchar,@birimistektarih))+','+@aciklama+'
,'+(convert(nvarchar,@onay))+','+(convert(nvarchar(@seviye))+','+(convert(nvarchar,@talepformno))+','''+(convert(nvarchar,@taleptarih,112))+''')'
--debug view
select @sqlCmd
exec (@sqlCmd )
END
Jens is completely correct. I addressed your specific issue, but you
may want to revisit the design to decide why it is that you are using
variables to decide the table you are inserting into...
| GethWho wrote: |
Jens is completely correct. I addressed your specific issue, but you may want to revisit the design to decide why it is that you are using variables to decide the table you are inserting into...
|
|
my program creating new tables for each years e.g. 2005Table, 2006Table, 2007Table... and i want to make insert queries on this tables, i can do this on client side easily but it ll cause some preformance problems, thats why i want to solve it on server side, i thought i could solve it with stored procedures
You just overcooked your system. Use sp can improve system performance, but that is not always true. A compiled sp with static SQL statement definately will. A sp use dynamic SQL statement may not, because SQL may not be able to use cached copy of the sp, since it sees different stored procedure from time to time. In your case, it would not do better than sending the insert statement from your application directly.
You may avoid using dynamic query in your sp by if statement:
If @type = '2005'
insert 2005table ......
else if @type = '2006'
insert 2006table .....
While, that does not give you much flexibility.
If you are creating yearly tables then you can use a partitioned view (creating view with SELECT UNION ALL of all tables) and use it to manipulate the data. You need to define the proper CHECK constraints for the partition(s) elimination to work correctly. But this will allow you to keep your SP code simpler and static in nature. See Books Online for more details on how to use partitioned views. If you are on SQL Server 2005 you can use the partitioned tables feature. There is also a white paper on the new partitioning feature. Otherwise, what you are trying to do with dynamic SQL has lot of issues - security, manageability and performance.
Umachandar, can you please provide a link to the white paper on partitioning in SQL Server 2005? I haven't been able to locate it.
Thanks!
Chris
