[dba-SQLServer] SP works sometimes

Quite right, Doris. But I think there is something else missing. The last part makes no sense, I think:

where creation_date>"07/01/2006" and '+ at id
 
This makes no sense. It could have been a simple typo slip-up, but if not then how is this going to parse into anything sensible? The AND is going to make any such statement incomprehensible.

A larger question: Why bother dynamically creating a SQL statement to do this thing, when you could quite easily create a variant of this sproc that drops the params into the statement, rather than constructing the statement and executing it? Perhaps I'm having a senior moment, but I can't fathom the need to do it this way. You don't have to build a statement just because you're doing a nested SELECT.

Or perhaps I'm missing something. It wouldn't be the first time.

Arthur

CREATE PROCEDURE [Select_Records_1] 
( @ID nvarchar(255)  
)
AS
INSERT INTO tblHaier_ID
(case_id, Criteria)
SELECT Case_id
declare @sql as varchar(1000)
declare @nsql as nchar(3000)
set @sql='insert into tblHaier_ID(case_id,Criteria) SELECT case_id, 
"'+ at ID+'" as Criteria FROM tblCase where creation_date>"07/01/2006" and 
'+ at id
set @nsql=@sql
set nocount on
exec sp_executesql @nsql
set nocount off


----- Original Message ----
From: Doris Manning <mikedorism at verizon.net>
To: dba-sqlserver at databaseadvisors.com
Sent: Monday, October 23, 2006 6:21:39 PM
Subject: Re: [dba-SQLServer] SP works sometimes

Your problems lie in your mixing of single quotes (') and double quotes (").
You don't use double quotes (") in T-SQL statements.  Dates in SQL are
surrounded in single quotes (').  

I've also found it helpful to preface all strings with the N designator to
be sure they are interpreted properly.

Doris Manning


_______________________________________________
dba-SQLServer mailing list
dba-SQLServer at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/dba-sqlserver
http://www.databaseadvisors.com