[dba-SQLServer] SQL in SP failing

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
 




More information about the dba-SQLServer mailing list