Variable declaration - SQL Server Forums

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 2005 Forums

Transact-SQL (2005)

Variable declaration

Author

Topic

winsonlee
Starting Member

4 Posts

Posted - 2008-09-25 : 20:00:11

[code]

USE [Sales]
GO
/****** Object: StoredProcedure [dbo].[selectStratemValue2] Script Date: 09/26/2008 09:02:16 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[selectStratemValue2]
@State varchar(200),
@ProductCode varchar(200),
@WeekNo int
AS
SET NOCOUNT ON
DECLARE @strSQL varchar(1000),@strPcode varchar(1000), @strState varchar(1000)

IF @State = ' '
SET @strState =''
ELSE
SET @strState = 'State in(' + @State +') AND '

if @ProductCode = ' '
SET @strPcode = ''
ELSE
SET @strPcode = 'ProductCode in(' + @ProductCode +') AND '

SET @strSQL = 'SELECT Stratem, sum(salesValue) as Salesx, sum(SOHValue), sum(forecastValue), sum(OrderValue) FROM SOHPricing WHERE '+ @strPcode + @strState + ' WeekNo =@WeekNo GROUP BY Stratem ORDER BY salesx desc';
EXECUTE(@strSQL)
SET NOCOUNT OFF

[/code]

[code]
USE [Sales]
GO

DECLARE @return_value int

EXEC @return_value = [dbo].[selectStratemValue2]
@State = N'VIC',
@ProductCode = N'FL200, MAL616',
@WeekNo = 200838

SELECT 'Return Value' = @return_value

GO

[/code]

Error

Msg 137, Level 15, State 2, Line 1
Must declare the scalar variable "@WeekNo".

(1 row(s) affected)

I have declared @WeekNo but somehow it is not taking it. Any idea what went wrong ?

sodeep
Master Smack Fu Yak Hacker

7174 Posts

Posted - 2008-09-25 : 21:02:20

Go statement is terminating the scope of that variable.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-09-26 : 01:29:32

And there is no need for the below code inside stored procedure
USE [Sales]
GO

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-09-26 : 01:57:43

And since you are using dynamic sql to build your string, you need to enclose every item in your commadelimited string with single quotes...

E 12°55'05.63"
N 56°04'39.26"

swatinagpal
Starting Member

2 Posts

Posted - 2008-09-26 : 02:27:14

+ ' WeekNo =@WeekNo GROUP BY Stratem ORDER BY salesx desc'

@WeekNo should be out of single quote like you have done for other parameters. Please confirm if this worked.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-09-26 : 02:55:28

You are you using dynamic sql at all?

ALTER PROCEDURE dbo.selectStratemValue2
(
	@State varchar(200), 
	@ProductCode varchar(200),
	@WeekNo int
)
AS

SET NOCOUNT ON
    
SELECT		q.Stratem,
		sum(q.salesValue) as Salesx,
		sum(q.SOHValue),
		sum(q.forecastValue),
		sum(q.OrderValue)
FROM		SOHPricing AS q
INNER JOIN	dbo.fnParseList(',', @state) AS p1 on p1.data = q.state
			or @state = ''
INNER JOIN	dbo.fnParseList(',', @productcode) AS p2 on p2.data = q.productcode
			or @productcode = ''
WHERE		q.WeekNo = @WeekNo
GROUP BY	q.Stratem
ORDER BY	q.salesx desc

E 12°55'05.63"
N 56°04'39.26"

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2008-09-26 : 03:01:01

where fnParseList is available at Script Library Forum

Madhivanan

Failing to plan is Planning to fail

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-09-26 : 03:02:03

quote:
Originally posted by madhivanan

where fnParseList is available at Script Library Forum

Madhivanan

Failing to plan is Planning to fail

this is link

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

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-09-26 : 03:03:14

And now all three of us have bumped our post count!

E 12°55'05.63"
N 56°04'39.26"

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2008-09-26 : 03:20:24

quote:
Originally posted by Peso

And now all three of us have bumped our post count!

E 12°55'05.63"
N 56°04'39.26"

Well. It is better you post the link whenever you refer

Madhivanan

Failing to plan is Planning to fail

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-09-26 : 03:31:13

Too much spoonfeeding?

E 12°55'05.63"
N 56°04'39.26"

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2008-09-26 : 03:47:50

quote:
Originally posted by Peso

Too much spoonfeeding?

E 12°55'05.63"
N 56°04'39.26"

No. Many users may think that as system function and may complaint about Invalid function error

Madhivanan

Failing to plan is Planning to fail

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-09-26 : 03:58:46

quote:
Originally posted by madhivanan

quote:
Originally posted by Peso

Too much spoonfeeding?

E 12°55'05.63"
N 56°04'39.26"

No. Many users may think that as system function and may complaint about Invalid function error

Madhivanan

Failing to plan is Planning to fail