September 10, 2002 at 5:12 pm
I have a stored procedure in microsoft sql server that works, but I need to change it. Here is a simplified verson (probably with some errors):
create procedure proc_name
@name as char(30)
@address as char(30)
@date datetime
as
insert into mytable
(name, address, date)
values(@name, @address, @date)
go
I want to change myTable to a variable (so I can use many tables with the same structure) that will be passed with the other variables, but when I do so I get an error about TableVariable being undeclared. I've tried exec and it solved that problem, but it doesn't like the insert parameters (i acctually have about 40), particularily the date. I will recive an error about 'syntax error converting datetime to string' or something similar.
I've spent days on this decivingly simple problem, any suggestions?
September 10, 2002 at 5:26 pm
You need to build some dynamic sql. Try something like this:
create procedure proc_name
@name as char(30)
@address as char(30)
@date datetime
as
declare @cmd varchar(100)
set @cmd = 'insert into mytable ' +
'(name, address, date) ' +
'values(' + @name + ',' +
@address + ',' + @date + ')'
-- use this line to debug syntax errors
-- print @cmd
-- process command
exex(@cmd)
-------------------------
Gregory Larsen, DBA
If you looking for SQL Server Examples check out my website at http://www.geocities.com/sqlserverexamples
Gregory A. Larsen, MVP
September 10, 2002 at 5:35 pm
Here is one that is works a little better:
ALTER procedure proc_name
@name as char(30),
@address as char(30),
@date datetime
as
declare @cmd varchar(100)
set @cmd = 'insert into mytable ' +
'(name, address, date) ' +
'values(''' + rtrim(@name) + ''',''' +
rtrim(@address) + ''',''' + rtrim(cast(@date as char)) + ''')'
-- use this line to debug syntax errors
print @cmd
-- process command
exec(@cmd)
-------------------------
Gregory Larsen, DBA
If you looking for SQL Server Examples check out my website at http://www.geocities.com/sqlserverexamples
Gregory A. Larsen, MVP
September 10, 2002 at 6:27 pm
I have tried that, without success. It will also be passed in with the @name and other paramaters, so redefining it shouldn't be nessicary. I am pretty sure my issue is with quotes or something similar. I don't have it right in front of me though.
What I have looks kinda like this (but with more variables):
@cmd='INSERT INTO'+@tableName+'(name, address, date) VALUES' +@name+ ','+ @address+','+@date+')'
exec(@cmd)
If I cast the date to char as suggested, will I have problems when it is inserted into the datetime field in the table?
September 10, 2002 at 7:32 pm
To answer you question, using chars to insert into a date field should not be a problem (when used like this). Actually, when you pass dates from a client application, you actually send a string. Although it most likely does not anything good or bad, it works. So that's cool.
Try to insert NULL values instead of the parameters ( for all the fields that accept NULL). Basically, find the simplest INSERT SQL you can get by placing NULLs everywhere. This should work. If it doesn't, check the number of parameters...
Next, try to replace each NULL by the corresponding variable, one at a time, until you get to your error. This should help you identify you syntax error.
Herve
Herve Roggero
hroggero@pynlogic.com
MCDBA, MCSE, MCSD
SQL Server Database Proxy/Firewall and Auditing
September 11, 2002 at 4:17 am
Try this.
@cmd='INSERT INTO ' + @tableName + ' (name, address, date) VALUES ''' + @name + ''',''' + @address + ''',''' + CONVERT(varchar,@date,101) + ''')'
exec(@cmd)
Notes:
First you need to make sure you have a space before and after the table name so the query can be parse by the Query Manager.
Next to submit a char or varchar value you need to encompass with ' to demark, when build a string you must double ' to get a single ' returned and since you are concatinating the value of a variable you add a third ' to break out and into the string.
Finally, as for the date look in SQL BOL at CONVERT which here my example returns the varchar value in mm/dd/yyyy format but you can pick another more fitting format from the list in SQL BOL by changin 101 to the appropriate value, again same thing as before when concatenating in the value.
"Don't roll your eyes at me. I will tape them in place." (Teacher on Boston Public)
September 11, 2002 at 9:27 am
I've changed the exec string to the format in the previous post.
I am now getting the error: Syntax error converting the varchar value ' ',' ' to column of data type int.
I broke it into two strings and then concatanted them. I've also tried it as one big string. The only problem I could think of is that my first value to be inserted is an int. I tried casting the int to a varchar and recived the same error message, but with diffrent types and a diffrent string.
any suggestions?
September 11, 2002 at 9:43 am
Print out your dynamice sql string just prior to having it executed. Take that string and try to run it in QA. Sound like your dynamic SQL is slightly wrong. Sounds like maybe you put qoutes around your int field in the values clause.
Or possibly you are not converting your Dynamic parms that are ints to string a string variable when building your dynamic SQL command.
Hope this helps. Posting your T-SQL would give me a little better idea of what was wrong.
-------------------------
Gregory Larsen, DBA
If you looking for SQL Server Examples check out my website at http://www.geocities.com/sqlserverexamples
Gregory A. Larsen, MVP
September 11, 2002 at 11:49 am
I think I've made some progress.
Here's a sample.
set @cmd= 'INSERT INTO '+@myTable+' (ID, Date, Name) VALUES ( '+cast(@ID as varchar)+','''+convert(Varchar, @Date)+''','''+@Name+''')'
I'm using the same format of quotes for int, decimal and float as with the @ID paramater.
I'm still getting an error that says: error converting nvarchar to numeric. Would this occur when it is acctually put in the DB?
I can't cut and paste acctual error messages and code because they are on physically separate machines and the code is too long to re-type.
September 11, 2002 at 12:33 pm
Here I took your sample code, and put some other code around it as a working sample. How does this code differ from yours?
create table mytable (
id int,
name char(10),
date datetime)
declare
@id int,
@name varchar(10),
@date as datetime,
@cmd as varchar(1000),
@mytable as varchar(10)
set @id = 1
set @name ='abc'
set @date = '2002-09-11'
set @mytable = 'mytable'
set @cmd= 'INSERT INTO '+@myTable+' (ID, Date, Name) VALUES ( '+cast(@ID as varchar)+','''+convert(Varchar, @Date)+''','''+@Name+''')'
print @cmd
exec (@cmd)
drop table mytable
-------------------------
Gregory Larsen, DBA
If you looking for SQL Server Examples check out my website at http://www.geocities.com/sqlserverexamples
Gregory A. Larsen, MVP
September 11, 2002 at 1:08 pm
I think the main diffrence is that my values are being passed into a stored procedure instead of being declared. That wouldn't cause my errors.
I just got off lunch though, so if the way you did it works, I'll double check my statement. I even have bit paramaters to deal with. I'll try treating as numeric values.
Thanks so far though.
September 11, 2002 at 2:02 pm
Thanks for all the help!!
I found the problem was that it wanted me explicitly to cast everything to a varchar. I hadn't been because everything I had read told me it would be unnessicary. It works now.
Thank you very much!!
September 17, 2002 at 11:31 am
Another strategy: use always a same view to insert data, but change the view underlaying table:
declare @n varchar(80)
set @n='mytable'
exec('drop view abc')
exec('create view abc as select * from '+@n)
insert abc (name, address, date) values(@name, @address, @date)
set @n='mytable2'
exec('drop view abc')
exec('create view abc as select * from '+@n)
insert abc (name, address, date) values(@name, @address, @date)
set @n='mytable3'
exec('drop view abc')
exec('create view abc as select * from '+@n)
.
.
.
The only advantage: NO MORE DATATYPE casting, and easy to use in a loop
quote:
I have a stored procedure in microsoft sql server that works, but I need to change it. Here is a simplified verson (probably with some errors):create procedure proc_name
@name as char(30)
@address as char(30)
@date datetime
as
insert into mytable
(name, address, date)
values(@name, @address, @date)
go
I want to change myTable to a variable (so I can use many tables with the same structure) that will be passed with the other variables, but when I do so I get an error about TableVariable being undeclared. I've tried exec and it solved that problem, but it doesn't like the insert parameters (i acctually have about 40), particularily the date. I will recive an error about 'syntax error converting datetime to string' or something similar.
I've spent days on this decivingly simple problem, any suggestions?
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply