escaping a string

hi guys

please help me. i've never user stored procs before but here is my problem.

this is only what i am allowed to display but it shows my problem

declare @.suite varchar(10),@.company varchar(1)
set @.suite = 'brutus'

set @.company = 'A'
exec ('insert into tot (SuiteName,Company) values ('+@.suite+','+@.company+')')

when i exec the query it says that "brutus" is an invalid column name. i know that i need to insert a extra ' but i don't know how or what is the escape character. please help me.

Hi,

this should be accomplishedby this one here:

exec ('insert into tot (SuiteName,Company) values ('''+@.suite+''','''+@.company+''')')

But I would rather prefer not using dynamic sql in this case:

insert into tot (SuiteName,Company) values (@.suite,@.company)

HTH, Jens Suessmeyer.

http://www.sqlserver2005.de
|||

thanks that helps but when i do this

1. exec ('alter table tot add '+@.UnitT+' varchar(3) DEFAULT '' WITH VALUES')

2. alter table tot add @.UnitT varchar(3) DEFAULT ' ' WITH VALUES

not one work. in 1 i get the same problem and in 2 it tells met there is a problem with syntax before @.unit

|||

No, therefore you would need dynamic SQL, that will not function with the second opion I posted.

HTH, Jens Suessmeyer.

http://www.sqlserver2005.de

|||

OK THANKS

i used your first solution with the ''' multiple quotes and it works perfect. thanks

Errors and Stored Procs

Ok, I've read somewhere(which I'm looking for again :\ ) that said that there are errors like DeadLock that kills the execution of a stored proc and there are other errors that do not necessarily kill the rest of the execution of the stored proc. Is that true? If so does anyone have any links I can read. What I'm seeing is a bad id in the foreign key and I think what is happening is that there was a unique constraint error on the first insert but the stored proc continued executing and used the bad id later on in the stored proc.

I do know I can use the @.@.error and will start using it but I need more proof to agree or not agree with my theory.

Thanks ahead of time for any information you can give me either way.

DMWYour assumption/theory is correct. The batch executes till the end or the first logically reachable RETURN.|||Thank you. I just wrote a script that proved that to me. If anyone else is interested...

/************Execute to create the tables****************/
--create test table
CREATE TABLE mytesttable
(
theid int UNIQUE NONCLUSTERED,
thedate datetime
)

--Insert values into the table
insert into mytesttable
(theid, thedate) values
(1, '1/1/2004')
insert into mytesttable
(theid, thedate) values
(2, '2/1/2004')
insert into mytesttable
(theid, thedate) values
(3, '3/1/2004')

/************************************************** */

/************Execute to create the stored proc**************/
create procedure spMytest

as
declare @.theid int
declare @.err1 int
declare @.err2 int

begin transaction thetest
--Force the unique constrant to happen
select @.theid = max(theid) from mytesttable

insert into mytesttable
(theid, thedate) values
(@.theid, '4/1/2004')
set @.err1 = @.@.error
commit transaction thetest

update mytesttable set thedate = getdate() where theid = @.theid
select @.err1

/************************************************** */

/*****************Run the next lines to watch the fun********/

--Run the stored Proc
exec spMytest
--look at the results
select * from mytesttable
/************************************************** */



Hope this helps someone else

DMW