Datatype Issues... 8000 characters is too small.
-
Hello, I have a stored procedure that I wrote that dynamically creates unmatched table queries on the fly. The procedure works really well generally... I am using datatypes of varchar(8000) for my query strings. However, I have two tables at the moment that are very huge. One table has 100+ fields and the other has like 50 or so... my problem is when it creates the queries for these two tables, the query doesn't get created properly because the query ends up being too big for the varchar(8000) datatype.
Is there a way around this? I tried declaring the variables as text, but it said I couldn't do that on local variables. Is there a way to make my variables global to use this text datatype? If you would like to see my code, I can provide it.
Thanks!
-
Hey,
Don't think so; you could try splitting the queries into multiple values and concatenating them at the end like:
exec @SQL1 + @SQL2
But I don't think that will work.
Brian
-
Actually, that does work. That is how I'm doing it now... I have the query broken up into query strings and then I concatenate them together with the exec command.
Its a lot of code but here is the procedure:
CREATE PROCEDURE spQuery (@QueryName nvarchar(255), @TempQueryNum varchar(10)) AS
declare @SQL varchar(500)
declare @SQL2 varchar(500)
declare @QueryName2 varchar(255)
--First 4 variables are for production vs. master
declare @ProdQuery varchar(8000)
declare @ProdQuery2 varchar(8000)
declare @ProdQuery3 varchar(8000)
declare @ProdQuery4 varchar(8000)
--Next 4 variables are for master vs. production
declare @ProdQuery5 varchar(8000)
declare @ProdQuery6 varchar(8000)
declare @ProdQuery7 varchar(8000)
declare @ProdQuery8 varchar(8000)
declare @FieldName varchar(255)
declare @Length int
--Start Production vs. Master Code
Select @QueryName2 = 'P_' + @QueryName
SELECT @SQL = 'SELECT syscol.name INTO TempFields from RTM..syscolumns syscol inner join
RTM..sysobjects sysob on syscol.id = sysob.id where sysob.name = ''' + @QueryName2 + ''' order by syscol.name'
exec (@SQL)
DECLARE table_cursor SCROLL CURSOR FOR
Select * from TempFields
--Exception Handling (Remove this after 4.4 goes live).
If @QueryName = 'CREDIT_LIMIT_POLICY'
BEGIN
DELETE FROM TempFields WHERE [name] = 'CRD_LMT_PERCENT'
END
--
Select @ProdQuery = 'SELECT [Table] = ''' + @QueryName + ''', Type = ''PRODUCTION'''
Select @ProdQuery3 = ''
--Exception Handling (Do Not Remove).
IF @QueryName IN ('CONT_CPNI_PREF_VALUE', 'CONT_CPNI_PREF_CODES', 'SERVICE_PROVIDER')
BEGIN
Select @ProdQuery = 'SELECT [Table] = ''' + @QueryName + ''', Type2 = ''PRODUCTION'''
END
--
OPEN table_cursor
FETCH FIRST FROM table_cursor INTO
@FieldName
Select @ProdQuery4 = 'WHERE (((M.' + @FieldName + ') Is Null)) UNION ALL '
WHILE @@Fetch_Status = 0
BEGIN
Select @ProdQuery = @ProdQuery + ', P.' + @FieldName
Select @ProdQuery3 = @ProdQuery3 + '(ISNULL(P.' + @FieldName + ',0) = ISNULL(M.' + @FieldName + ',0)) AND '
FETCH NEXT FROM table_cursor INTO
@FieldName
END
Select @ProdQuery = @ProdQuery + ' INTO TempQuery' + @TempQueryNum
Select @ProdQuery2 = ' FROM P_' + @QueryName + ' P LEFT JOIN U_' + @QueryName + ' M ON '
Select @Length = len(@ProdQuery3)
Select @ProdQuery3 = substring(@ProdQuery3,1, @Length - 4)
--End Production vs. Master code
--Start Master vs. Production code
Select @QueryName2 = 'U_' + @QueryName
-- SELECT @SQL2 = 'SELECT syscol.name INTO TempFields2 from RTM..syscolumns syscol inner join
-- RTM..sysobjects sysob on syscol.id = sysob.id where sysob.name = ''' + @QueryName2 + ''' order by syscol.name'
--exec (@SQL2)
DECLARE table_cursor2 SCROLL CURSOR FOR
Select * from TempFields
Open Table_cursor2
Select @ProdQuery5 = 'SELECT [Table] = ''' + @QueryName + ''', Type = ''MASTER'''
Select @ProdQuery7 = ''
--Exception Handling (Do Not Remove).
IF @QueryName IN ('CONT_CPNI_PREF_VALUE', 'CONT_CPNI_PREF_CODES', 'SERVICE_PROVIDER')
BEGIN
Select @ProdQuery5 = 'SELECT [Table] = ''' + @QueryName + ''', Type2 = ''MASTER'''
END
--
FETCH FIRST FROM table_cursor2 INTO
@FieldName
Select @ProdQuery8 = 'WHERE (((P.' + @FieldName + ') Is Null))'
WHILE @@Fetch_Status = 0
BEGIN
Select @ProdQuery5 = @ProdQuery5 + ', M.' + @FieldName
Select @ProdQuery7 = @ProdQuery7 + '(ISNULL(P.' + @FieldName + ',0) = ISNULL(M.' + @FieldName + ',0)) AND '
FETCH NEXT FROM table_cursor2 INTO
@FieldName
END
Select @ProdQuery6 = ' FROM U_' + @QueryName + ' M LEFT JOIN P_' + @QueryName + ' P ON '
Select @Length = len(@ProdQuery7)
Select @ProdQuery7 = substring(@ProdQuery7,1, @Length - 4)
--End Master vs. Production code
print @ProdQuery
print @ProdQuery2
print @ProdQuery3
print @ProdQuery4
print @ProdQuery5
print @ProdQuery6
print @ProdQuery7
print @ProdQuery8
--Put the Query together
exec (@ProdQuery + '' + @ProdQuery2 + '' + @ProdQuery3 + '' + @ProdQuery4 + '' + @ProdQuery5 + '' + @ProdQuery6 + '' + @ProdQuery7 + '' + @ProdQuery8)
DROP TABLE TempFields
DEALLOCATE table_cursor
DEALLOCATE table_cursor2
GO
-
I've had to deal with this same issue a number of times myself. I finally decided to stop doing it the hard way and wrote these three stored procedures. The code that is generating really long dynamic SQL is probably already complicated enough as it is without having to dirty it up with variable spanning issues. This set of three SPs allows you to write dynamic SQL of any length by appending any number of times with strings of 7900 or fewer characters at a time. It creates a table of 7900 character long strings. The table currently uses tinyint row numbering giving a maximum length of 2,014,500 characters. You use 1 SP to build the string (appending) and another SP to execute it and optionally clear the string when done. The third SP is used to clear the string and normally is not explicitely used.
Example:
EXEC sp_MSforeachtable @command1='exec UTBigSQLConcat ''Test'', ''print "?"
'' '
exec UTBigSQLExec 'Test'
Another Example:
EXEC sp_MSforeachtable @command1='exec UTBigSQLConcat ''Test'', ''print "?"
'' '
exec UTBigSQLExec 'Test', 0
SELECT * FROM ##BigSQL WHERE ControllerCode = 'Test' AND SPID = @@SPID ORDER BY Num
exec UTBigSQLFree 'Test'
UTBigSQLConcat
This creates a global temporary table called ##BigSQL if it doesn't already exist. It then uses it to build up a table of 7900 length strings that will compose the final SQL. It is keyed off of the SPID (for connection uniqueness), ControllerCode (so that a given process can simultaneously build multiple big strings), and an index number for ordering the 7900 length component strings.
UTBigSQLExec
This executes the built dynamic SQL using dynamic SQL. If the AutoFree optional parameter omitted or sent as a 1 then the UTBigSQLFree procedure is called for the given ControllerCode.
UTBigSQLFree
This deletes the rows within ##BigSQL for the current SPID and the given ControllerCode. If that then leaves the table empty it is DROPed.
The Code:
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS OFF
GO
CREATE PROCEDURE dbo.UTBigSQLConcat
@ControllerCode varchar(50)
, @AppendString varchar(7900) /* reserve space for other table columns */
AS
set nocount on
declare @CurrentNum tinyint
declare @CurrentString varchar(8000)
declare @RemainderCur smallint
declare @RemainderApp smallint
declare @AppendLen smallint
declare @CurrentLen smallint
if @AppendString IS NULL set @AppendString = ''
if @ControllerCode IS NULL set @ControllerCode = ''
if OBJECT_ID( 'tempdb..##BigSQL' ) IS NULL
begin
CREATE TABLE ##BigSQL (
SPID smallint
, ControllerCode varchar(50)
, Num tinyint
, String varchar(7900) /* reserve space for other table columns */
)
INSERT INTO ##BigSQL ( SPID, ControllerCode, Num, String ) VALUES ( @@SPID, @ControllerCode, 1, @AppendString )
return
end
SELECT @CurrentNum = MAX( Num ) FROM ##BigSQL WHERE ControllerCode = @ControllerCode AND SPID = @@SPID
if @CurrentNum IS NULL
begin
--The table existed for another controller, but had no content yet for this controller.
INSERT INTO ##BigSQL ( SPID, ControllerCode, Num, String ) VALUES ( @@SPID, @ControllerCode, 1, @AppendString )
return
end
SELECT @CurrentString = String FROM ##BigSQL WHERE ControllerCode = @ControllerCode AND Num = @CurrentNum AND SPID = @@SPID
-- Note that LEN trims trailing white space and we need to retain it. Therefore, we append an 'X' to the LEN and then subtract it back out.
set @AppendLen = ( LEN( @AppendString + 'X') - 1 )
set @CurrentLen = ( LEN( @CurrentString + 'X' ) - 1 )
set @RemainderCur = ( 7900 - @CurrentLen ) /* Space available on the current line. */
set @RemainderApp = @AppendLen - ( 7900 - @CurrentLen ) /* Positive if chars need to go on the next line. Number = chars on next line. */
--Concatenate what we can (perhaps all) to the current row.
UPDATE ##BigSQL
SET String = @CurrentString + LEFT( @AppendString, @RemainderCur )
WHERE ControllerCode = @ControllerCode AND Num = @CurrentNum AND SPID = @@SPID
if @RemainderApp > 0
begin
--We need to add another row and drop the remainder into it.
INSERT INTO ##BigSQL ( SPID, ControllerCode, Num, String ) VALUES ( @@SPID, @ControllerCode, @CurrentNum + 1, RIGHT( @AppendString, @RemainderApp ) )
end
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS OFF
GO
CREATE PROCEDURE dbo.UTBigSQLExec
@ControllerCode varchar(50)
, @AutoFree bit = 1
AS
set nocount on
declare @CurrentNum tinyint
declare @StringDeclares varchar(8000)
declare @StringSELECTs varchar(8000)
declare @EXECLine varchar(8000)
declare @CRLF varchar(3)
if @ControllerCode IS NULL set @ControllerCode = ''
if @AutoFree IS NULL set @AutoFree = 0
if OBJECT_ID( 'tempdb..##BigSQL' ) IS NULL
begin
--Perhaps it would be best to let it error or to raise a custom error, but here I'm just doing nothing instead.
return
end
SELECT @CurrentNum = MAX( Num ) FROM ##BigSQL WHERE ControllerCode = @ControllerCode AND SPID = @@SPID
if @CurrentNum IS NULL
begin
--Perhaps it would be best to let it error or to raise a custom error, but here I'm just doing nothing instead.
return
end
--We are going to use dynamic SQL to EXEC the dynamic SQL. Glorious efficiency.
SELECT @StringDeclares = '', @StringSELECTs = '', @EXECLine = '', @CRLF = '
'
while @CurrentNum > 0
begin
SELECT @StringDeclares = 'declare @AString' + CONVERT(varchar, @CurrentNum ) + ' varchar(8000)' + @CRLF + @StringDeclares
, @StringSELECTs = 'SELECT @AString' + CONVERT(varchar, @CurrentNum ) + ' = String FROM ##BigSQL WHERE ControllerCode = ''' + @ControllerCode + ''' AND Num = ' + CONVERT(varchar, @CurrentNum ) + ' AND SPID = ' +CONVERT( varchar, @@SPID ) + @CRLF + @StringSELECTs
, @EXECLine = '@AString' + CONVERT(varchar, @CurrentNum ) + case when LEN( @EXECLine ) > 0 then ' + ' else '' end + @EXECLine
set @CurrentNum = @CurrentNum - 1
end
set @EXECLine = 'EXEC( ' + @EXECLine + ' )'
EXEC ( @StringDeclares + @StringSELECTs + @EXECLine )
if @AutoFree = 1
begin
exec UTbigSQLFree @ControllerCode = @ControllerCode
end
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS OFF
GO
CREATE PROCEDURE dbo.UTBigSQLFree
@ControllerCode varchar(50)
AS
set nocount on
if @ControllerCode IS NULL set @ControllerCode = ''
if OBJECT_ID( 'tempdb..##BigSQL' ) IS NULL
begin
--Perhaps it would be best to let it error or to raise a custom error, but here I'm just doing nothing instead.
return
end
DELETE FROM ##BigSQL WHERE ControllerCode = @ControllerCode AND SPID = @@SPID
if NOT EXISTS ( SELECT TOP 1 Num FROM ##BigSQL )
begin
DROP TABLE ##BigSQL
end
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
-
Aaron,
I'm confused as to how this executes all the strings in the temp table. It appears to execute 3 string variables, so the max length would be 24,000. Can you clear this up? I guess I could just test your code out, but I'm really curious as to how it works.
cl
Signature is NULL
-
Hi Roxanne,
Have you tried writing VB code that uses ADO to execute your built queries?
You would need to return the SQL queries in a resultset. So, you might add the following code at the stored proc
declare @sql_stmts table (
cols varchar(8000),
from varchar(8000),
where varchar(8000)
)
-- repeat insert below for each query
insert into @sql_stmts values(
<comma delimited list of columns in SQL query would go here>,
<SQL query FROM clause (i.e. inner joins also) would go here>,
<SQL query WHERE would go here>
select * from @sql_stmts
Then, the VB code might look something like this:
Dim cn as ADODB.Connection, rs as ADODB.Recordset
cn.Open "DB conn str goes here"
Set rs = cn.Execute("spQuery 'SomeQuery', '5' ")
Dim strSqls as String
'building the SQL statements
While (Not rs.EOF)
strSqls = strSqls & "SELECT " & rs(0) & " FROM " & rs(1) & " WHERE " & rs(2) & ";"
rs.MoveNext
Wend
'now, execute all the SQL queries in one execute call
cn.Execute(strSqls)
Hope this helps,
JP
-
Calvin, the 3 strings you noticed are 1) a list of X variable declares of 8000 length varchars 2) a list of assignment selects to assign values to each of the X variables and 3) a single EXEC statement of the concatentation of the list of X variables. Notice that we are building dynamic SQL to EXEC our dynamic SQL (nested). Keeping in mind that only 7,900 of the available 8000 is used for each variable, if you have 50,000 characters of SQL to execute it will build 1) a list of 7 declares 2) 7 different SELECTs to populate each of the variables 3) an "EXEC( @AString1 + @AString2 + @AString3 + @AString4 + @AString5 + @AString6 + @AString7 )" line. It just occurred to me that the @StringSELECTs variable will blow out the 8000 length after generating 76 SELECT statements. So, unless you split that apart into multiple strings (which wouldn't be hard) the effective limit of the BigSQL is a mere 600,400 characters as posted above. I'm thinking I'll manage within that limit, myself.
If that doesn't explain it well enough, try changing the "EXEC ( @StringDeclares + @StringSELECTs + @EXECLine )" line into three print statements to report each of those variables. I suspect that it will become clear then. If not then I could try another explanation. Enjoy.
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply