Eric Barro
ebarro at verizon.net
Tue Feb 6 09:40:36 CST 2007
Mark, You need to use CHAR(39) for the single quotes. CREATE PROCEDURE [Select_Records_1] ( @ID nvarchar(255) ) AS declare @sql as varchar(1000) Set @sql ='insert into tblBrita_ID(case_id,Criteria) SELECT case_id, ' + CHAR(39) + 'test' + CHAR(39) Set @sql = @sql + ' As Criteria FROM tblCase where case_id=1' exec (@sql) --Eric -----Original Message----- From: dba-sqlserver-bounces at databaseadvisors.com [mailto:dba-sqlserver-bounces at databaseadvisors.com] On Behalf Of Mark A Matte Sent: Tuesday, February 06, 2007 7:23 AM To: dba-sqlserver at databaseadvisors.com Subject: Re: [dba-SQLServer] SQL in SP failing Arthur, I've pretty much given up on understanding this one. Below is the SP in question. This is not the final version I use...but a simplified version to show the problem: CREATE PROCEDURE [Select_Records_1] @ID nvarchar(255) AS declare @sql as varchar(1000) declare @nsql as nchar(3000) set @sql='insert into tblBrita_ID(case_id,Criteria) SELECT case_id, "test" As Criteria FROM tblCase where case_id=1' set @nsql=@sql set nocount on exec sp_executesql @nsql set nocount off ********************* This works...sometimes...I think I need the double quotes because "test" is just some text I put in. If I don't need the doublequotes how to I show that test is NOT a field. To get this to work...I remove all criteria and the word test(replace with a field)...and running the query that calls the SP...if it runs...I add some criteria back in...and keep doing this until I have rebuilt the SP. Finally it will run...so I save and all is good. But the next day I change just the table name...try to run it...and it fails...I don't get it??? Then I just start modifying until I get back to where it started....I am confused. Thanks, Mark A. Matte >From: artful at rogers.com >Reply-To: dba-sqlserver at databaseadvisors.com >To: dba-sqlserver at databaseadvisors.com >Subject: Re: [dba-SQLServer] SQL in SP failing >Date: Mon, 5 Feb 2007 14:59:04 -0800 (PST) > >Missed this earlier. The double quotes are your problem. > >Arthur Fuller >Technical Writer, Data Modeler, SQL Sensei Artful Databases >Organization www.artfulsoftware.com > > > > >----- Original Message ---- >From: Mark A Matte <markamatte at hotmail.com> >To: dba-sqlserver at databaseadvisors.com >Sent: Thursday, November 30, 2006 2:13:44 PM >Subject: [dba-SQLServer] SQL in SP failing > > >Hello All, > >Any idea why the following SQL will fail in an SP? > >'insert into tblBrita_ID(case_id,Criteria) SELECT case_id, "test" As >Criteria FROM tblCase where case_id=1' > >The error is "Invalid column name 'test'." It doesn't always >fail...normally 'test' is a parameter passed to the SP...I have >modified it to the above for demo purposes...but using just the word >'test' as above still gives the same error. > >Thanks, > >Mark A. Matte >_______________________________________________ >dba-SQLServer mailing list >dba-SQLServer at databaseadvisors.com >http://databaseadvisors.com/mailman/listinfo/dba-sqlserver >http://www.databaseadvisors.com > _________________________________________________________________ Search for grocery stores. Find gratitude. Turn a simple search into something more. http://click4thecause.live.com/search/charity/default.aspx?source=hmemtaglin e_gratitude&FORM=WLMTAG _______________________________________________ dba-SQLServer mailing list dba-SQLServer at databaseadvisors.com http://databaseadvisors.com/mailman/listinfo/dba-sqlserver http://www.databaseadvisors.com -- No virus found in this incoming message. Checked by AVG Free Edition. Version: 7.1.411 / Virus Database: 268.17.28/672 - Release Date: 2/6/2007