Getting error when trying to exec 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)

Getting error when trying to exec SP

Author

Topic

reddymade
Posting Yak Master

165 Posts

Posted - 2004-11-23 : 16:29:10

Can you please help, i am trying to get this search stored proc. to work.
I am getting the following error:
Server: Msg 207, Level 16, State 3, Line 1
Invalid column name 'cnt3'.

when i am trying to use this to execute my stored proc from query analyzer.

DECLARE @ProgNO nvarchar(50)
DECLARE @ProjNO nvarchar(50)
DECLARE @ContractNO nvarchar(50)
SET @ProgNO = null
SET @ProjNO = null
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 * FROM Tab_ccsnetcn '

If @ProgNO IS NOT NULL
BEGIN
set @sql1 = 'WHERE ProgNO = ' + @ProgNO
END

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

If @ContractNO IS NOT NULL
BEGIN
If @sql1 IS NOT NULL
BEGIN
set @sql1 = @sql1 + ' and ContractNO =' + @ContractNO
END
ELSE
BEGIN
set @sql1 = ' WHERE ContractNO =' + @ContractNO
END
END

EXEC(@SQL+@Sql1)
GO
**********************
Thank you very much.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2004-11-23 : 17:22:16

Duplicate:

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

Tara

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources