creating unknown number of variables at run time.

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)

creating unknown number of variables at run time.

Author

Topic

under2811
Constraint Violating Yak Guru

366 Posts

Posted - 2012-09-15 : 08:18:25

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

robvolk
Most Valuable Yak

15732 Posts

Posted - 2012-09-15 : 09:11:06

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.

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-09-15 : 16:14:29

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;

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-09-15 : 16:22:01

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;

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-09-15 : 21:58:49

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/

under2811
Constraint Violating Yak Guru

366 Posts

Posted - 2012-09-16 : 04:45:25

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

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-09-16 : 10:50:57

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.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-09-16 : 12:22:54

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/

under2811
Constraint Violating Yak Guru

366 Posts

Posted - 2012-09-17 : 02:49:26

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

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-09-17 : 11:13:50

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/

under2811
Constraint Violating Yak Guru

366 Posts

Posted - 2012-09-18 : 01:12:44

Hi,

I am using it for SSIS and fill the excel.

T.I.A

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-09-18 : 01:49:16

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/

under2811
Constraint Violating Yak Guru

366 Posts

Posted - 2012-09-25 : 04:37:21

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

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources