Stored Procedure, T-SQL, Worked in Server 7 but not in SQL2K - Baffled!

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Stored Procedure, T-SQL, Worked in Server 7 but not in SQL2K - Baffled!

Author

Topic

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2002-03-19 : 11:27:16

Peter writes "Here is the SP:

CREATE PROCEDURE sp_Test
@p_Test varchar(100)
AS
DECLARE @sSQL varchar(255)
SET NOCOUNT ON
SELECT @sSQL = 'SELECT url, sitename, type FROM Table1 WHERE type = 02 AND ' + @p_Test + ' IS NOT NULL'
EXEC (@sSQL)
GO

OK, to start, the problem with this lies in the "type" field. This field will contain one of five possible pieces of data:
01
02
03
01 02
01 03
the type field is varchar(5)

When it's run, I get the following error:
[Microsoft][ODBC SQL Server Driver][SQL Server]Syntax error converting the varchar value '03 01' to a column of data type int.

When run in Query Analyzer and debugged, this SP starts to return rows until it hits the first record with either '01 02' or '01 03' data in it. In the case of this example it was '03 01'.

This SP was first created and worked perfectly under Server 7. It was copied into SQL2K (with syntax intact) and the above error is the result. My questions are: why; how do I fix; and what has changed so much between the versions that this query will not function??

I hope you find this question tough enough. If not I'll try harder next time!

thanks.
Peter
Win 98 SE 4.10.2222A
SQL2K V3 8.00.194"

Jay99

468 Posts

Posted - 2002-03-19 : 11:44:39

Are you sure this ran in 7?

Your WHERE type = 02 should be WHERE type = '02' (you will need to excape your quotes) since you doing a string comparison. What is @p_Test supposed to be?

What are you trying to do? It seams that this:


create proc sp_test
    @p_test varchar(100)
as
set nocount on
select url, sitename, type
from table1
where type = '02' and @p_test is not null
go

Will perform the same thing without the dynamic sql? Pretty sure this has nothing to do with versions . . .

Jay
<O>

PCY
Starting Member

2 Posts

Posted - 2002-03-19 : 15:58:01

Jay (et al)
First, it did work in S7, but while playing with it in 2K, I discovered you were right, type = '02' is what was required.

The parameter is fed a database column name. As I understand it a dynamic query is required so the entire query string fires at once. I beleive this to be true in 2k as well. I tried to remove the executable lines and run a straight SELECT statement as suggested, but it ignored the "@p_Test IS NOT NULL", returning only records where type LIKE 02.

Regardless, I've hammered down the problem to a syntax error. Here's what I've got:

CREATE PROCEDURE sp_Test
@p_Test varchar(100)
AS
DECLARE @sSQL varchar(255)
SET NOCOUNT ON
SELECT @sSQL = 'SELECT url,sitename,type FROM Table1
WHERE Type LIKE ' + '%02%' + ' AND ' + @p_Test + ' IS NOT NULL'
EXEC (@sSQL)
GO

(NOTE: there are plus signs around '%02%' and @p_Test. They did not show up when I previewed.)

Running this as a straight select statement against the database, it works. However, this stored procedure generates a syntax error at '02'. I've played with the syntax at length, but can't find the solution.

Any thoughts??

efelito
Constraint Violating Yak Guru

478 Posts

Posted - 2002-03-19 : 16:03:24

Type is a varchar(5) then your comparison must be in quotes like so.

CREATE PROCEDURE sp_Test
@p_Test varchar(100)
AS
DECLARE @sSQL varchar(255)
SET NOCOUNT ON
SELECT @sSQL = 'SELECT url,sitename,type FROM Table1
WHERE Type LIKE ''' + '%02%' + ''' AND ' + @p_Test + ' IS NOT NULL'
EXEC (@sSQL)
GO

Jeff Banschbach
Consultant, MCDBA

Jay99

468 Posts

Posted - 2002-03-19 : 16:13:11

quote:

...
The parameter is fed a database column name. As I understand it a dynamic query is required so the entire query string fires at once. I beleive this to be true in 2k as well. I tried to remove the executable lines and run a straight SELECT statement as suggested, but it ignored the "@p_Test IS NOT NULL", returning only records where type LIKE 02.
...

My bad, I misread the query on the first pass. Yeah, in this case, you would need dynamic SQL.

Its always useful, when debugging, to print out your @sSQL before you execute it. It pretty easy to get the various quotes and escaped quotes mixed up . . .

Jay
<O>

PCY
Starting Member

2 Posts

Posted - 2002-03-20 : 11:07:33

Jeff and Jay - thank you very much!! That did it!
Peter
[~=p=~]

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources