SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2008 Forums
 Transact-SQL (2008)
 Err: Must declare the scalar variable “@temp”.
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

micnie_2020
Posting Yak Master

Malaysia
223 Posts

Posted - 07/08/2013 :  21:22:14  Show Profile  Reply with Quote
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

India
52249 Posts

Posted - 07/09/2013 :  01:18:28  Show Profile  Reply with Quote
whats the issue you're facing? I dont see a question here.

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page

DonAtWork
Flowing Fount of Yak Knowledge

2143 Posts

Posted - 07/09/2013 :  06:32:04  Show Profile  Reply with Quote
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

Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52249 Posts

Posted - 07/09/2013 :  07:00:44  Show Profile  Reply with Quote
ah...yes you're right
just noticed that.

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page

DonAtWork
Flowing Fount of Yak Knowledge

2143 Posts

Posted - 07/11/2013 :  06:44:30  Show Profile  Reply with Quote
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

Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 1.61 seconds. Powered By: Snitz Forums 2000