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)
 creating unknown number of variables at run time.
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

under2811
Constraint Violating Yak Guru

India
356 Posts

Posted - 09/15/2012 :  08:18:25  Show Profile  Reply with Quote
Hi All,

I need help regarding creating unknown number of variables at run time. Scenario is like i am facing problem with sp_executesql which is restricting me to take only 4000 characters so i need to split my string into number of variable like
set @query = @query1 + @query2 + @query3
exec sp_executesql @query
like that.
and @query1,@query2,@query3 not sure depending upon my records in table.

Another problem is like in @query1 statement i am writing pivoting syntax so i have used query like select * into #Temp....for first @query1 it works fine but for @query2 it will give us error like #Temp table already exists..

Can anybody let me know how can i do this...
please help me out.


T.I.A

Edited by - under2811 on 09/15/2012 08:45:26

robvolk
Most Valuable Yak

USA
15665 Posts

Posted - 09/15/2012 :  09:11:06  Show Profile  Visit robvolk's Homepage  Reply with Quote
Why not use varchar/nvarchar(max) to construct your dynamic SQL and use EXEC()? That will avoid the limit and likely the need for multiple variables.
Go to Top of Page

sunitabeck
Flowing Fount of Yak Knowledge

5155 Posts

Posted - 09/15/2012 :  16:14:29  Show Profile  Reply with Quote
Regarding your question about "select..into tablename" giving a syntax error - I am guessing that the problem is this:
SELECT TOP 1 * INTO #tmp FROM table1;
IF OBJECT_ID('tempdb..#tmp') IS NULL
BEGIN 
	SELECT TOP 1 * INTO #tmp FROM table2;
END 

Msg 2714, Level 16, State 1, Line 4
There is already an object named '#tmp' in the database.
I usually change the code to this to get around that problem. You may have to tweak the logic based on whether table1 exists, table2 exists etc.
SELECT TOP 0 * INTO #tmp FROM table1;
INSERT INTO #tmp SELECT * FROM table1;
INSERT INTO #tmp SELECT * FROM table2;
Go to Top of Page

sunitabeck
Flowing Fount of Yak Knowledge

5155 Posts

Posted - 09/15/2012 :  16:22:01  Show Profile  Reply with Quote
quote:
Originally posted by robvolk

Why not use varchar/nvarchar(max) to construct your dynamic SQL and use EXEC()? That will avoid the limit and likely the need for multiple variables.

Is there really a limitation of 4000 on the sp_executsql parameter? This seems to say it is nvarchar(max). Also, my test code below works beyond 4000.
CREATE TABLE #tmp
(
	col1 NVARCHAR(MAX)
);

DECLARE @sql NVARCHAR(MAX);
SET @sql = CAST('select ''' AS NVARCHAR(MAX))
    + REPLICATE(CAST('a' AS NVARCHAR(MAX)), 10000) 
    + CAST('''' AS NVARCHAR(MAX));
SELECT LEN(@sql);

INSERT INTO #tmp
EXEC sp_executesql @sql;

SELECT LEN(col1)
FROM   #tmp;

DROP TABLE #tmp;
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52317 Posts

Posted - 09/15/2012 :  21:58:49  Show Profile  Reply with Quote
quote:
Originally posted by robvolk

Why not use varchar/nvarchar(max) to construct your dynamic SQL and use EXEC()? That will avoid the limit and likely the need for multiple variables.


I hope you were referring to query variables @query1 etc

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Go to Top of Page

under2811
Constraint Violating Yak Guru

India
356 Posts

Posted - 09/16/2012 :  04:45:25  Show Profile  Reply with Quote
Hi

Thank you for reply. I am able to proceed with NVARCHAR(MAX) but now the real problem is i am getting error like

Msg 1702, Level 16, State 1, Line 1
CREATE TABLE failed because column 'Column_1016' in table '#Temp' exceeds the maximum of 1024 columns.

Actually in pivoting i am getting almost more than 2700 columns. If I refer Sparce table/Wide table which will allow us to create more than 20000 columns approx 30000 columns. But as i am creating table #Temp dynamically by pivoting it. So i dont have table structure firm to add Sparce column in it.

T.I.A

Edited by - under2811 on 09/16/2012 06:20:33
Go to Top of Page

sunitabeck
Flowing Fount of Yak Knowledge

5155 Posts

Posted - 09/16/2012 :  10:50:57  Show Profile  Reply with Quote
In most cases, when you find yourself in a situation where you have to create tables with more than 1024 columns, there would be room for redesign and/or improvement in the database design and schema and avoid such wide tables.

Usually people pivot data so they can display it to a client in a friendly manner. But, when you have 1024 (or more pivoted columns) that seems to defeat that goal, if indeed that is the goal.

If you can post a simplified version of the query that you are trying to write along with some sample data and expected output, many people on the forum would be able to offer suggestions on how to accomplish the goal in the most efficient way.
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52317 Posts

Posted - 09/16/2012 :  12:22:54  Show Profile  Reply with Quote
quote:
Originally posted by under2811

Hi

Thank you for reply. I am able to proceed with NVARCHAR(MAX) but now the real problem is i am getting error like

Msg 1702, Level 16, State 1, Line 1
CREATE TABLE failed because column 'Column_1016' in table '#Temp' exceeds the maximum of 1024 columns.

Actually in pivoting i am getting almost more than 2700 columns. If I refer Sparce table/Wide table which will allow us to create more than 20000 columns approx 30000 columns. But as i am creating table #Temp dynamically by pivoting it. So i dont have table structure firm to add Sparce column in it.

T.I.A


why do you need to pivot so many columns?

yep...sparse column would be a good option

else try EAV approach and pivot it on the fily based on data availability ( i dont think you'll have data always in all of these 3000 columns)

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Go to Top of Page

under2811
Constraint Violating Yak Guru

India
356 Posts

Posted - 09/17/2012 :  02:49:26  Show Profile  Reply with Quote
Hi ,

This is my code

in my Input_Date column I have managed to insert rows distinct COLUMN_1,COLUMN_2,...COLUMN_2700 based on distinct Issue Message like below
Some client have mutiple issues separeted by "-" and these all needed as single row so i need to pivot them again..

Input_Date-----Issue_Message---Column_Position
COLUMN_1-------Issue1----------12361
COLUMN_2-------Issue2----------12362
COLUMN_3-------Issue3----------12363
.
.
.
COLUMN_2700----Issue3----------15114

Now need these COLUMN_1, COLUMN_2, ....COLUMN_2770 as pivoting respective to their Client ID..this is the requirement.

declare @Columns NVARCHAR(MAX)
declare @query_Columns_Pivot NVARCHAR(MAX)

select @Columns = STUFF(( select ',' + ltrim(rtrim((Input_Date)))
from #Temp_Session
where Input_Date LIKE 'COLUMN_%'
order by Column_Position
for XML path('')
), 1, 1, '')


set @query_Columns_Pivot =
'select * into #Temp
from
(
select * from
(
select Input_Date,Status_Code,ID,IDType,Client_Name,ClientID,Issue_Message,Input_Region,
Output_Region,Addresses,Client_Position_ID
from #Temp_Session
)t
PIVOT (max(Issue_Message) for Input_Date
in ('+@Columns+')) as pvt) s'

exec sp_executesql @query_Columns_Pivot
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52317 Posts

Posted - 09/17/2012 :  11:13:50  Show Profile  Reply with Quote
is this for reporting need? if yes, it can be very easily build in SSRS using matrix container by just giving Input_date field as column group

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Go to Top of Page

under2811
Constraint Violating Yak Guru

India
356 Posts

Posted - 09/18/2012 :  01:12:44  Show Profile  Reply with Quote
Hi,

I am using it for SSIS and fill the excel.

T.I.A
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52317 Posts

Posted - 09/18/2012 :  01:49:16  Show Profile  Reply with Quote
quote:
Originally posted by under2811

Hi,

I am using it for SSIS and fill the excel.

T.I.A


you mean pivot columns into excel?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Go to Top of Page

under2811
Constraint Violating Yak Guru

India
356 Posts

Posted - 09/25/2012 :  04:37:21  Show Profile  Reply with Quote
Yes..pivot columns into excel..

In above query i am getting error like

Msg 511, Level 16, State 1, Line 2
Cannot create a row of size 8066 which is greater than the allowable maximum row size of 8060.
The statement has been terminated.


So i tried to get print of my query

set @query_Columns_Pivot =
'select * into #Temp
from
(
select * from
(
select Input_Date,Status_Code,ID,IDType,Client_Name,ClientID,Issue_Message,Input_Region,
Output_Region,Addresses,Client_Position_ID
from #Temp_Session
)t
PIVOT (max(Issue_Message) for Input_Date
in ('+@Columns+')) as pvt) s'

print @query_Columns_Pivot


select * into #Temp
from
(
select * from
(
select Input_Date,Status_Code,ID,IDType,Client_Name,ClientID,Issue_Message,Input_Region,
Output_Region,Addresses,Client_Position_ID
from #Temp_Session
)t
PIVOT (max(Issue_Message) for Input_Date
in (COLUMN_1,COLUMN_2,...,COLUMN_307,COLUMN_308)) as pvt) s

Actually i am getting upto COLUMN_393 and each column has string. When i tried to reduce it to COLUMN_307 then i am able to proceed but when i added COLUMN_308 i am getting above error.

Can anybody help me to set something here which check and if its value less than "8060" then only proceed else comeout from here. And how i can get exact row of size?

T.I.A
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 0.09 seconds. Powered By: Snitz Forums 2000