Please help, getting error in my SP when executed

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)

Please help, getting error in my SP when executed

Author

Topic

reddymade
Posting Yak Master

165 Posts

Posted - 2004-11-23 : 17:20:30

Server: Msg 137, Level 15, State 2, Line 1
Must declare the variable '@ProgNO'.

I use this logic in front end without using SP and it works great. Dynamic where conditions.( first time trying with SP with asp.net form)
I am totally struck on this since last two days. Somebody please help.

When i am trying to test my SP in query analyzer using the following:

DECLARE @ProgNO nvarchar(50)
DECLARE @ProjNO nvarchar(50)
DECLARE @ContractNO nvarchar(50)
SET @ProgNO = 'prog1'
SET @ProjNO = 'proj1'
SET @ContractNO = 'cnt3'
execute USP_Searchrecords2 @ProgNO,@ProjNO,@ContractNO

*****My 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
select @sql1 = 'WHERE ProgNO = @ProgNO'
END

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

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

EXEC(@SQL+@Sql1)
GO
***************************************************

tkizer
Almighty SQL Goddess

38200 Posts

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

Duplicate:

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

Tara

reddymade
Posting Yak Master

165 Posts

Posted - 2004-11-23 : 17:40:37

Hello tara,
This is what is happen
When i execute my SP i am getting the following in the whole string

which is wrong: can you please help me to correct this:

SELECT * FROM Tab_ccsnetcn WHERE ProgNO = @ProgNO and ProjNO = @ProjNO and ContractNO = @ContractNO

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2004-11-23 : 17:42:45

You've checked @ProjNO for NULLs twice in your code. Why?

Tara

robvolk
Most Valuable Yak

15732 Posts

Posted - 2004-11-23 : 17:44:09

Please do not cross post. Continue the discussion in the original thread.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources