Error message: Invalid column name 'cnt3' in my SP

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)

Error message: Invalid column name 'cnt3' in my SP

Author

Topic

reddymade
Posting Yak Master

165 Posts

Posted - 2004-11-24 : 10:53:12

I am getting this error: i am putting in the where condition that contract no = 'cnt3'
but when i execute the stor proc, it is showing the error saying invalid column cnt3.

error message:

Server: Msg 207, Level 16, State 3, Line 1
Invalid column name 'cnt3'.

****Using the following in the query analyzer to execute my stored proc**

DECLARE @ProgNO nvarchar(50)
DECLARE @ProjNO nvarchar(50)
DECLARE @ContractNO nvarchar(50)

SET @ProgNO = ''
SET @ProjNO = ''
SET @ContractNO = 'cnt3'

EXECUTE USP_Searchrecords2 @ProgNO, @ProjNO, @ContractNO

*******************Stored procedure code******************
CREATE PROCEDURE dbo.USP_Searchrecords2
(
@ProgNO nvarchar(50),
@ProjNO nvarchar(50),
@ContractNO nvarchar(50)
)
AS

DECLARE @SQL varchar(1000)
DECLARE @SQL1 varchar(2000)

set @SQL = 'SELECT CNID,progno,projno,contractno,cntitle FROM Tab_ccsnetcn '

If @ProgNO IS NOT NULL and @ProgNo <> ''
BEGIN
set @SQL1 = 'WHERE ProgNO=' + @ProgNO
END

If @ProjNO IS NOT NULL and @ProjNo <> ''
BEGIN
If @SQL1 IS NOT NULL and @sql1 <> ''
BEGIN
set @SQL1 = @SQL1 + ' and ProjNO=' + @ProjNO
END
ELSE
BEGIN
set @SQL1 = ' WHERE ProjNO=' + @ProjNO
END
END

If @ContractNO IS NOT NULL and @ContractNO <> ''
BEGIN
If @SQL1 IS NOT NULL and @SQL1 <> ''
BEGIN
set @SQL1 = @SQL1 + ' and ContractNO=' + @ContractNO
END
ELSE
BEGIN
set @SQL1 = ' WHERE ContractNO=' + @ContractNO
END
END

EXEC (@SQL+@SQL1)

GO

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2004-11-24 : 11:30:28

you need to change
ContractNO=' + @ContractNO
to
ContractNO=''' + @ContractNO + '''

because those variables are strins and string need to be in single quotes

Go with the flow & have fun! Else fight the flow

jen
Master Smack Fu Yak Hacker

4110 Posts

Posted - 2004-11-25 : 02:04:14

same? http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=42941

--------------------
keeping it simple...

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources