Err: Must declare the scalar variable “@temp”.

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

Transact-SQL (2008)

Err: Must declare the scalar variable “@temp”.

Author

Topic

micnie_2020
Posting Yak Master

232 Posts

Posted - 2013-07-08 : 21:22:14

Hi All,

I have

Alter Procedure [dbo].[DisplayCurrentYearLeaveAllocation]
(
/*
exec [DisplayCurrentYearAllocation] '0','01/Apr/2013','TBL_mic'
*/
@isHead as nvarchar(15),
@fystartdt DateTime,
@temp as nvarchar(50)

)
As

SET NOCOUNT ON;

DECLARE @SQLString nvarchar(4000);

IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].'+@temp) AND type in (N'U'))
BEGIN
SET @SQLString = 'DROP TABLE [dbo].'+@temp;
EXEC (@SQLString);
END

DECLARE @QUERY NVARCHAR(MAX), @Soucecolumn VARCHAR(MAX)
SET @Soucecolumn = STUFF((SELECT distinct ',[' + [Soucecolumn Name] + ']' FROM @temp FOR XML PATH('')),1,1,'')
SET @QUERY = 'SELECT key1, ' + @Soucecolumn + ' FROM '+@temp+' PIVOT (MAX(ColumnValue) FOR [Soucecolumn Name] IN (' + @Soucecolumn + ')) AS pvt'
exec sp_executesql @QUERY

Please advise.

Thank you.

Regards,
Micheale

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-07-09 : 01:18:28

whats the issue you're facing? I dont see a question here.

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

DonAtWork
Master Smack Fu Yak Hacker

2167 Posts

Posted - 2013-07-09 : 06:32:04

looks like they are trying to build a dynamic string, then execute it. It is not going to like @Sourcecolumn = blahbla FROM @temp. That will also need to be dynamic sql.

How to ask: http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-07-09 : 07:00:44

ah...yes you're right
just noticed that.

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

DonAtWork
Master Smack Fu Yak Hacker

2167 Posts

Posted - 2013-07-11 : 06:44:30

quote:
Originally posted by visakh16

ah...yes you're right
just noticed that.

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

I FINALLY noticed something before you! and it only took 48766 posts to do it!

How to ask: http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources